Search Postgresql Archives

SRF called with optional NULL input runs 7x slower

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux