Jules Bean <jules@jellybean.co.uk> writes:
> I have a query of the form:
> SELECT * FROM .... WHERE (now()-date1) > 'interval 1 day';
> ..i.e. all rows 'older' than 1 day. This could be efficiently
> processed using the index on date1, but sadly pg doesn't know this ;-(
No, and I don't think it should. Should we implement a general
algebraic equation solver, and fire it up for every single query,
in order to see if the user has written an indexable condition in
a peculiar form? I don't think we want to expend either the development
effort or the runtime on that. If you are concerned about performance
of this sort of query, you'll need to transform it to
SELECT * FROM .... WHERE date1 < now() - interval '1 day';
Of course that still leaves you with problem (b),
> SELECT * FROM ....
> WHERE date1 > '2000-09-11 00:00:00'::datetime - '1 hour'::interval;
> ...so it doesn't realise that constant-constant is constant,
> notwithstanding the more complex issues that now() is pseudo-constant.
Most of the datetime operations are not considered constant-foldable.
The reason is that type timestamp has a special value CURRENT that
is a symbolic representation of current time (this is NOT what now()
produces, but might be thought of as a data-driven way of invoking
now()). This value will get reduced to a simple constant when it is
fed into an arithmetic operation. Hence, premature evaluation changes
the results and would not be a correct optimization.
AFAIK hardly anyone actually uses CURRENT, and I've been thinking of
proposing that we eliminate it to make the world safe for constant-
folding timestamp operations. (Thomas, any comments here?)
In the meantime, there is a workaround that's been discussed on the
mailing lists before --- create a function that hides the
"unsafe-to-fold" operations and mark it iscachable:
create function ago(interval) returns timestamp as'select now() - $1' language 'sql' with (iscachable);
Then something like
SELECT * FROM .... WHERE date1 < ago('1 day');
will be considered indexable. You can shoot yourself in the foot with
this --- don't try to write ago(constant) in a rule or function
definition --- but in interactive queries it'll get the job done.
regards, tom lane