Thread: 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/

"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

"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

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';