Обсуждение: 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] non-volatile (immutable) and non-SRF functions are cached per (top) transaction. Regards. [1] http://plscheme.projects.postgresql.org/
On Sat, Oct 4, 2008 at 1:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
Joseph S <jks@selectacast.net> writes:There's no promise of that.
> Aren't static functions supposed to executed only once per transaction?
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:
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:Not if said effort would cost more than is saved, which would be by far
> Shouldn't PG make all efforts to not execute something when the result is
> already known?
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;
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:
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
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:No, this is equally untrue for immutable.
> 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.
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/ > 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';