Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> OK, try
>
> select * from titles
> where adate < date('today'::Datetime - '1 month'::timespan);
>
> although there may (still) be problems with Postgres recognizing that
> it could use an index when the "constant" is an expression.
I'm afraid I can already predict the answer: the optimizer only knows
how to use an index to constrain the scan when it finds a WHERE clause
like "var op constant" or "constant op var". What you've got there
isn't a constant.
The right solution, of course, is to put in a rewrite phase that does
constant-expression folding (probably after any rule-generated changes).
We've talked about that before, but it ain't gonna happen for 6.5.
BTW, the original question was why "where adate::date < 'today'::date"
wouldn't work. What the optimizer sees in that case iswhere function(var) < constant
so it doesn't know how to use an index for that either. Now, if you
had a functional index matching the function, it would know what to do.
But it'd be pretty silly to keep a separate functional index just to let
this work, seeing as how adate is already a date.
It might be nice if the parser could drop dummy type conversions
instead of leaving them as functions in the parse tree... although
doing that as part of a general constant-expression folder is probably
a better answer.
regards, tom lane
