Re: Constant propagation and similar issues

On Mon, Sep 11, 2000 at 11:15:58AM -0400, Tom Lane wrote:
> Jules Bean <jules(at)jellybean(dot)co(dot)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