Re: Constant propagation and similar issues - Mailing list pgsql-hackers

On Mon, Sep 11, 2000 at 11:15:58AM -0400, Tom Lane wrote:
> 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';

Well, I shall speak quietly and timidly, for I'm not offering to do
the work, and I respect that other tasks are both more interesting and
more important. However, it does seem to me that PostgreSQL /should/
be able to make these transformations (at least, it should IMO
recognise that given an expression of the form a + b - c + d < e - f
+ g where exactly one of a..g is a column name, and the rest are
constant, that is a candidate for using the index).

> 
> 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?)

Yes.  I came across CURRENT in some examples somewhere, got very
confused, decided I didn't like it, and used now() instead ;-)  I now
understand the problem.  Personally, I'm thinking drop CURRENT, but
only because I've never used it myself...

> 
> 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.

Thanks very much.  I shall try that.

Jules