Static functions
Aren't static functions supposed to executed only once per transaction?
I have a query plan that looks like this:
Unique (cost=441872.58..441875.39 rows=562 width=4)
-> Sort (cost=441872.58..441873.98 rows=562 width=4)
Sort Key: elog.uid
-> Nested Loop (cost=89.52..441846.91 rows=562 width=4)
-> Index Scan using j_unique_seqno on j
(cost=0.00..678.70 rows=49 width=4)
Index Cond: (pod = 335)
Filter: ((mtotal > 0) AND (stdate > '2008-01-01
00:00:00-05'::timestamp with time zone) AND (stdate < '2008-10-03
23:59:59-04'::timestamp with time zone))
-> Bitmap Heap Scan on elog (cost=89.52..8975.15
rows=2263 width=8)
Recheck Cond: ((j.id = elog.jid) AND (elog."type"
= ANY ('{1,4}'::integer[])))
Filter: (id >= elog_date_search('2008-01-01
00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on elog_jid_type_type
(cost=0.00..89.43 rows=2263 width=0)
Index Cond: ((j.id = elog.jid) AND
(elog."type" = ANY ('{1,4}'::integer[])))
(12 rows)
That query is taking forever because my function elog_date_search() is
being called each time through the loop, and the pg process ends up
eating %100 of a cpu for a few minutes. If I replace elog_date_search()
in my SELECT with the result the query takes a few seconds.
This is with:
PostgreSQL 8.2.10 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
Joseph S <jks@selectacast.net> writes:
Aren't static functions supposed to executed only once per transaction?
There's no promise of that.
regards, tom lane
On Fri, 03 Oct 2008, Joseph S <jks@selectacast.net> writes:
Aren't static functions supposed to executed only once per transaction?
There is no such promise, that depends on the PL you prefer. For
instance, in PL/scheme[1]http://plscheme.projects.postgresql.org/ non-volatile (immutable) and non-SRF functions
are cached per (top) transaction.
Regards.
On Sat, Oct 4, 2008 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joseph S <jks@selectacast.net> writes:
Aren't static functions supposed to executed only once per transaction?
There's no promise of that.
Can this be changed? Or does it work only in simpler queries, based on some
result-caching criteria?
Shouldn't PG make all efforts to not execute something when the result is
already known?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
Shouldn't PG make all efforts to not execute something when the result is
already known?
Not if said effort would cost more than is saved, which would be by far
the most likely result if we tried to cache all function results.
regards, tom lane
On Sat, Oct 4, 2008 at 8:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
Shouldn't PG make all efforts to not execute something when the result is
already known?Not if said effort would cost more than is saved, which would be by far
the most likely result if we tried to cache all function results.
Sorry Tom, I confused STABLE with IMMUTABLE; my bad.
Joseph, you can cloak the STABLE function inside an IMMUTABLE function, then
this whole thing will be executed only once. Use this advice only after you
understand what you are doing.
Here's an example:
create or replace function f_stable() returns int as $$ begin raise NOTICE
'stable'; return 1; end; $$ stable language plpgsql;
create or replace function f_immutable() returns int as $$ begin raise
NOTICE 'immutable'; perform f_stable(); return 1; end; $$ IMMUTABLE language
plpgsql;
postgres=> select f_stable() from generate_series( 1, 2 );
NOTICE: stable
NOTICE: stable
f_stable
----------
1
1
(2 rows)
postgres=> select f_immutable() from generate_series( 1, 2);
NOTICE: immutable
NOTICE: stable
CONTEXT: SQL statement "SELECT f_stable()"
PL/pgSQL function "f_immutable" line 1 at PERFORM
f_immutable
-------------
1
1
(2 rows)
postgres=>
You can see that if STABLE function is called directly, it is invoked for
each row; but if we hide the STABLE function inside an IMMUTABLE function,
there is going to be just one invocation of both these functions for the
whole command.
HTH.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
On Sat, Oct 4, 2008 at 8:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
Shouldn't PG make all efforts to not execute something when the result is
already known?Not if said effort would cost more than is saved, which would be by far
the most likely result if we tried to cache all function results.Sorry Tom, I confused STABLE with IMMUTABLE; my bad.
No, this is equally untrue for immutable.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
On Sat, Oct 4, 2008 at 6:06 PM, Gregory Stark <stark@enterprisedb.com>wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
On Sat, Oct 4, 2008 at 8:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
Shouldn't PG make all efforts to not execute something when the result
is
already known?
Not if said effort would cost more than is saved, which would be by far
the most likely result if we tried to cache all function results.Sorry Tom, I confused STABLE with IMMUTABLE; my bad.
No, this is equally untrue for immutable.
Yup... I realized that after a bit of more testing after the mail...
Immutable functions are single-call-per-command only of you are passing
constants-only as parameters; if we have an expression involving columns,
then they will be called for every row.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Tom Lane wrote:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
Shouldn't PG make all efforts to not execute something when the result is
already known?Not if said effort would cost more than is saved, which would be by far
the most likely result if we tried to cache all function results.regards, tom lane
In this case, with the function being called over and over again inside
a nested loop, it would be worth the effort. I'm not even talking about
caching the result for the whole transaction block, just replacing the
function call before continuing the query.
Perhaps I can get around this problem by creating a temp table inside
the function that is dropped on transaction commit.
On Sat, Oct 04, 2008 at 08:30:32PM -0400, Joseph S wrote:
In this case, with the function being called over and over again inside
a nested loop, it would be worth the effort. I'm not even talking about
caching the result for the whole transaction block, just replacing the
function call before continuing the query.
I think you're going to have to be more specific, because if the
function is stable or immutable, pg will do exactly as you say. Post
the actual query involved.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout wrote:
On Sat, Oct 04, 2008 at 08:30:32PM -0400, Joseph S wrote:
In this case, with the function being called over and over again inside
a nested loop, it would be worth the effort. I'm not even talking about
caching the result for the whole transaction block, just replacing the
function call before continuing the query.I think you're going to have to be more specific, because if the
function is stable or immutable, pg will do exactly as you say. Post
the actual query involved.
SELECT distinct(uid) FROM elog,j WHERE elog.type IN(1,4) AND j.id =
elog.jid AND j.pod = 3 AND j.mtotal > 0 AND j.stdate > '2008-01-01
00:00:00.0' AND elog.id >= elog_date_search('2008-01-01 00:00:00') AND
j.stdate < '2008-10-06 23:59:59.0';