Ron Mayer <ron@intervideo.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