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)
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 |