[HACKERS] Constant propagation and similar issues
Jules Bean
them just in case, and to see if you have plans to sort them out:
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 ;-(
This transforms an operation which should be O(1) to O(rows)....
More worryingly, when I investigated the above, I find it doesn't even
use the index for
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.
This could be fixed by constant folding, I guess; any plans for that?
Jules
Ross J. Reedstrom
>
> 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?)
>
I checked the ansi SQL'99 docs, and CURRENT as a date special constant
is not a part of the standard (although CURRENT is a keyword: it is
used in the context of cursors)
Ross
--
Ross J. Reedstrom, Ph.D., <reed...@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Ross J. Reedstrom
> On Mon, Sep 11, 2000 at 11:15:58AM -0400, Tom Lane wrote:
> >
> > 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?)
> >
>
> I checked the ansi SQL'99 docs, and CURRENT as a date special constant
> is not a part of the standard (although CURRENT is a keyword: it is
> used in the context of cursors)
>
Following up to myself:
Ah, I had forgotten that CURRENT is a magic value, like 'infinity'.
The standard does specify in section 6.19:
CURRENT_DATE, CURRENT_TIME, LOCALTIME, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
as <datetime value function>
Which are currently implemented as generating the special value 'CURRENT',
which then get's stored in the column. This strikes me as _not_ standards
compliant. What do other DB's do with these? I think that they should
be equivalent to now(), returning a static date that is stored.
I do find the timestamp special values 'infinity' and '-infinity' very
useful, but have never found a use for 'current'.
Jules Bean
> Jules Bean <ju...@jellybean.co.uk> writes:
> > 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).
>
> Mumble. I think that'd be a very difficult thing to do without losing
> the datatype extensibility of the system. Right now, the only reason
> that "a < b" is considered indexable is that the optimizer has a table
> that tells it "<" is an indexable operator for btree indexes with
> certain datatypes (opclasses). Neither the optimizer nor the btree code
> has any real understanding of the relationships between "<" and "-", say.
> There is no part of the system anywhere with understanding of algebraic
> identities like "a - b < c can be transformed to a < b + c", and no way
> I can see to add such knowledge without making it *substantially* harder
> to add new datatypes and operators.
Yes, actually something like this occurred to me after I sent the
above email. I had forgotten about the (rather pretty) extensible
type system; I can see that makes spotting optimisations such as the
above much more difficult. Seems like it might make a nice subject for
a paper, actually.
>
> Between that and the runtime that would be wasted during typical queries
> (IMHO searching for rearrangeable clauses would usually be fruitless),
> I really doubt that this is a good goal to pursue in Postgres.
I'm afraid I can't buy that second argument ;-) The time it takes to
optimise a query is asymptotically irrelevant, after all...
Jules