Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

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