Re: function(contants) evaluated for every row

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Notice the to_date()'s were not converted to constants in EXPLAIN so
> > they are evaluated for every row. to_date() is marked STABLE.
>
> > Is this something we should improve?
>
> No. This is per expectation. Only IMMUTABLE functions can be folded to
> constants in advance of the query.

Well CREATE FUNCTION says about STABLE:

STABLE indicates that the function cannot modify the
database, and that within a single table scan it will
consistently return the same result for the same
argument values, but that its result could change
across SQL statements. This is the appropriate
selection for functions whose results depend on
database lookups, parameter variables (such as the
current time zone), etc. (It is inappropriate for
AFTER triggers that wish to query rows modified by the
current command.) Also note that the current_timestamp
family of functions qualify as stable, since their
values do not change within a transaction.

I realize they can't be converted to constants before the query starts
but is there a reason we can't convert those functions to constants in
the executor before a table scan?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +