I've got 2 nearly identical SRFs to retrieve data from the DB(pg 8.2.4)
which goes something like this
================= Function 1 ======================
CREATE OR REPLACE FUNCTION hot(fromdate timestamp without time zone, todate timestamp without time zone, code text[])
RETURNS SETOF trh_hot AS
$BODY$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT
foo.a,
bar.b
FROM d
INNER JOIN ts
ON ts.id = D.id
inner join trh
ON ts.id = trh.id
AND ts.ttype = trh.ttype
AND ts.run_date = trh.run_date
WHERE d.record_update_date_time BETWEEN fromdate AND todate
AND trh.run_date BETWEEN fromdate AND todate
AND trh.ttype IN (select ttype from lookup_ttype where tsequence = 'hot')
AND d.code = any (code)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
===================================
========== Function 2 ===================
CREATE OR REPLACE FUNCTION hot(fromdate timestamp without time zone, todate timestamp without time zone, code text[], sn text[])
RETURNS SETOF trh_hot AS
$BODY$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT
foo.a,
bar.b
FROM d
INNER JOIN ts
ON ts.id = D.id
inner join trh
ON ts.id = trh.id
AND ts.ttype = trh.ttype
AND ts.run_date = trh.run_date
WHERE d.record_update_date_time BETWEEN fromdate AND todate
AND trh.run_date BETWEEN fromdate AND todate
AND trh.ttype IN (select ttype from lookup_ttype where tsequence = 'hot')
--> AND (CASE WHEN code IS NULL THEN true else d.code = any (code) END)
--> AND (CASE WHEN sn IS NULL THEN TRUE else D.id = any(SN) END)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
===================== >8 =============================
The main difference being these 2 lines
AND (CASE WHEN code IS NULL THEN true else d.code = any (code) END)
AND (CASE WHEN sn IS NULL THEN TRUE else D.id = any(SN) END)
which the aim is to check for NULL input and determine whether or not a
filter is necessary for those items.
The query is called by these respectively
Func1: select * from HOT('8/1/2007','9/30/2007','{HUA71}')
Func2: select * from HOT2('8/1/2007','9/30/2007','{HUA71}',NULL)
If I put in all the SN in replacement of the NULL, it's slightly faster
at 37 secs to return the data.
Vmstat 5 on Function1:7 secs 8400 rows
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 268 32296 2908 1096780 1 1 92 75 1083 1021 10 6 82 3
4 1 268 33708 2864 1094856 0 0 5771 21 1134 3518 23 19 51 7
6 0 268 39196 2876 1091396 0 0 2074 27 1230 3477 33 51 0 16
very little on cpu wait.
Vmstat 5 on Function2: 50secs 8400 rows
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 0 268 33648 4980 1087552 0 0 1606 103 1216 2943 21 32 0 47
2 1 268 33888 4484 1089576 0 0 2352 14 1209 2811 19 32 0 49
3 1 268 34380 4100 1090564 0 0 1270 13 1187 2890 13 21 0 66
2 1 268 34184 4004 1091408 0 0 1107 98 1206 2949 12 19 0 69
3 2 268 33004 3984 1093312 0 0 1357 34 1203 2931 11 20 0 69
4 0 268 33008 3860 1093556 0 0 1302 8 1193 2897 13 21 0 65
2 1 268 33608 3800 1093140 0 0 1286 6 1195 2954 12 18 0 70
1 1 268 33552 3300 1093776 0 0 1282 109 1213 2896 12 20 0 67
3 1 268 32404 3296 1095368 0 0 1202 1 1188 2908 12 19 0 69
1 1 268 33976 2680 1094476 0 0 1184 0 1180 2905 11 19 0 70
3 1 268 33336 2440 1095924 0 0 1291 8 1196 2944 11 19 0 70
2 1 268 34112 2272 1095412 0 0 1245 118 1221 2932 13 19 0 68
2 0 268 34844 2756 1095364 0 0 698 4 1145 3040 19 49 0 32
Just look at the CPU wait. What Gives? Does checkin on NULL causes a penalty? I
tried running the same query as is on pgadmin3 and it looked to be OK
(the null checking). I do notice that the system (my laptop) was doing
quite a bit of IO.
how can I debug or diagnose where the issues lies? Explain analyse
doesn't do much since this is a Function Scan anyway.
AS of right now, I'm trying to see if I can do Dynamic SQL instead.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
