Re: [SQL] indexes using datetime

Michael Richards <miker@scifair.acadiau.ca> writes:
> explain select * from logins where logintime>'now'::datetime-'40
> days'::timespan;
> NOTICE:  QUERY PLAN:
> Seq Scan on logins  (cost=5839.78 rows=44958 width=44)

> Very bad query plan :(

Yah.  It's got nothing to do with datetime though.  Problem is that the
system can only make indexscans work with WHERE clauses of the form
"field op constant" (for "op"s related to the sort ordering of the
index of course).  Your righthand side is not a constant.

Eventually, maybe for 6.6 or 6.7, we will have the smarts in place to
reduce constant expressions to simple constants during preprocessing of
a query.  For now, you are going to have to structure your application to
precompute the value that's going to be compared against index entries.
        regards, tom lane