From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ron Mayer <ron(at)intervideo(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Should the optimizer optimize "current_date - interval '1 days'" (fwd) |
Date: | 2002-06-03 23:33:41 |
Message-ID: | 12951.1023147221@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Ron Mayer <ron(at)intervideo(dot)com> writes:
> where dat > (current_date - interval '1 days');
> never uses the index I have on "fact".
I suppose dat is of type date?
> Should it treat my current_dat... expression as a constant and use
> the index? Or is there a good reason it doesn't?
You will never get an indexscan out of that because the expression
seen by the planner is
where timestamp(dat) > timestamp-expression
which is not compatible with an index of datatype date. You should
write something that yields a date, not a timestamp, for example
where dat > (current_date - 1)
This should be indexable (and is, in current development sources)
but in 7.2 and before you have to do additional pushups because
the planner doesn't understand that current_date can be treated
as a constant for the duration of a single indexscan. The standard
workaround is to create a function of a signature like
"days_ago(int) returns date" and mark it isCachable. This is a cheat
but it works fine in interactive queries. See past discussions in
the archives.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-06-04 23:21:05 | Re: Transaction isolation with concurrent increments |
Previous Message | Nico Erichsen | 2002-06-03 19:55:30 | Transaction isolation with concurrent increments |