Tom Lane wrote:
> Bruce Momjian <bruce@momjian.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
itwill 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@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +
