Static functions

From: Joseph S <jks(at)selectacast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Static functions
Date: 2008-10-03 18:48:56
Message-ID: gc5pen$1seu$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joseph Shraibman 2008-10-03 18:54:25 Re: Static functions
Previous Message Rob Richardson 2008-10-03 16:40:32 Re: How do I save data and then raise an exception? -- solved