Обсуждение: 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-------------------------------------------------------------------------------------------------------------------------------
SeqScan 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@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +
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.
regards, tom lane
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. +
Bruce Momjian <bruce@momjian.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@sss.pgh.pa.us> 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. > 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@sss.pgh.pa.us> 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. > >> 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 functionsthat always cache the value of the function in a variable and use the variable in the actual query to avoid thisparticular "gotcha". Michael Glaesemann grzm seespotcode net
>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". subquery may be used to cache constants: SELECT a_date FROM test WHERE a_date=(SELECT current_date); "Seq Scan on test1 (cost=0.01..10.76 rows=5 width=4)" " Filter: (a_date = $0)" " InitPlan 1 (returns $0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" ------------ pasman
2010/11/25 pasman pasmański <pasman.p@gmail.com>: >>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". > > subquery may be used to cache constants: > > SELECT a_date > FROM test > WHERE a_date=(SELECT current_date); > > > "Seq Scan on test1 (cost=0.01..10.76 rows=5 width=4)" > " Filter: (a_date = $0)" > " InitPlan 1 (returns $0)" > " -> Result (cost=0.00..0.01 rows=1 width=0)" Interesting. So we pull a subquery out and treat it as an initplan, but not a stable function? Hmm... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas wrote: > 2010/11/25 pasman pasma?ski <pasman.p@gmail.com>: > >>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". > > > > subquery may be used to cache constants: > > > > SELECT a_date > > ?FROM test > > ?WHERE a_date=(SELECT current_date); > > > > > > "Seq Scan on test1 ?(cost=0.01..10.76 rows=5 width=4)" > > " ?Filter: (a_date = $0)" > > " ?InitPlan 1 (returns $0)" > > " ? ?-> ?Result ?(cost=0.00..0.01 rows=1 width=0)" > > Interesting. So we pull a subquery out and treat it as an initplan, > but not a stable function? Hmm... I assume this is _not_ a TODO. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Thu, Mar 10, 2011 at 7:32 PM, Bruce Momjian <bruce@momjian.us> wrote: > Robert Haas wrote: >> 2010/11/25 pasman pasma?ski <pasman.p@gmail.com>: >> >>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". >> > >> > subquery may be used to cache constants: >> > >> > SELECT a_date >> > ?FROM test >> > ?WHERE a_date=(SELECT current_date); >> > >> > >> > "Seq Scan on test1 ?(cost=0.01..10.76 rows=5 width=4)" >> > " ?Filter: (a_date = $0)" >> > " ?InitPlan 1 (returns $0)" >> > " ? ?-> ?Result ?(cost=0.00..0.01 rows=1 width=0)" >> >> Interesting. So we pull a subquery out and treat it as an initplan, >> but not a stable function? Hmm... > > I assume this is _not_ a TODO. Dunno, not sure. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company