Re: [HACKERS] function(contants) evaluated for every row
Robert Haas wrote: 2010/11/25 pasman pasma?ski pasma...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
On Thu, Mar 10, 2011 at 7:32 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: 2010/11/25 pasman pasma?ski pasma...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
2010/11/25 pasman pasmański pasma...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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', '-MM-DD') AND
x = to_date('2001-01-01', '-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, '-MM-DD'::text)) AND
(x = to_date('2001-01-01'::text, '-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 br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
Tom Lane wrote: Bruce Momjian br...@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 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
On Wed, Nov 24, 2010 at 21:52, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function(contants) evaluated for every row
On Nov 24, 2010, at 15:28 , Marti Raudsepp wrote: On Wed, Nov 24, 2010 at 21:52, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
