Re: function(contants) evaluated for every row

Someone offlist reported query slowness because we don't convert
function calls with all-constant parameters to be a constants before we
start a sequential scan:

EXPLAIN SELECT * FROM test WHERE
x >= to_date('2001-01-01', 'YYYY-MM-DD') AND
x <= to_date('2001-01-01', 'YYYY-MM-DD');

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------

Seq Scan on test (cost=0.00..58.00 rows=12 width=4)
Filter: ((x >= to_date('2001-01-01'::text, 'YYYY-MM-DD'::text)) AND
(x <= to_date('2001-01-01'::text, 'YYYY-MM-DD'::text)))
(2 rows)

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?

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

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

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.

regards, tom lane

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

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> 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?

Other than the significant number of cycles that would be wasted (in
most cases) checking for the possibility, probably not. I'm dubious
that it would average out to a win though.

regards, tom lane

On Wed, Nov 24, 2010 at 21:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.

> No.  This is per expectation.  Only IMMUTABLE functions can be folded to
> constants in advance of the query.

This is something that has bit me in the past.

I realize that STABLE functions cannot be constant-folded at
planning-time. But are there good reasons why it cannot called only
once at execution-time?

As long as *only* STABLE or IMMUTABLE functions are used in a query,
we can assume that settings like timezone won't change in the middle
of the execution of a function, thus STABLE function calls can be
collapsed -- right?

Regards,
Marti

On Nov 24, 2010, at 15:28 , Marti Raudsepp wrote:

> On Wed, Nov 24, 2010 at 21:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
>
>> No. This is per expectation. Only IMMUTABLE functions can be folded to
>> constants in advance of the query.
>
> This is something that has bit me in the past.
>
> I realize that STABLE functions cannot be constant-folded at
> planning-time. But are there good reasons why it cannot called only
> once at execution-time?
>
> As long as *only* STABLE or IMMUTABLE functions are used in a query,
> we can assume that settings like timezone won't change in the middle
> of the execution of a function, thus STABLE function calls can be
> collapsed -- right?

I've seen this as well be a performance issue, in particular with partitioned tables. Out of habit I now write functions that always cache the value of the function in a variable and use the variable in the actual query to avoid this particular "gotcha".

Michael Glaesemann
grzm seespotcode net