(View and SQL) VS plpgsql - Mailing list pgsql-hackers

This has been posted to performance but i didin't had any answer i could
look forward...

If anyone got some time for explanation,examples..


Abstract:

The function that can be found at the end of the e-mail emulate two
thing.

First it will fill a record set of result with needed column from a
table and two "empty result column" a min and a max.

Those two column are then filled by a second query on the same table
that will do a min and a max

on an index idx_utctime.

The function loop for the first recordset and return a setof record that
is casted by caller to the function.


The goald of this is to enabled the application that will receive the
result set to minimise its

work by having to group internaly two matching rowset. We use to handle
two resultset but i am looking

toward improving performances and at first glance it seem to speed up
the process.


Questions:

1. How could this be done in a single combinasion of SQL and view?

2. In a case like that is plpgsql really givig significant overhead?

3. Performance difference [I would need a working pure-SQL version to
compare PLANNER and Explain results ]

STUFF:

--TABLE && INDEX


CREATE TABLE archive_event
( inst int4 NOT NULL, cid int8 NOT NULL, src int8 NOT NULL, dst int8 NOT NULL, bid int8 NOT NULL, tid int4 NOT NULL,
utctimeint4 NOT NULL, CONSTRAINT ids_archives_event_pkey PRIMARY KEY (inst, cid), CONSTRAINT
ids_archives_event_cid_indexUNIQUE (cid) 
)

--index

CREATE INDEX idx_archive_utctime ON archive_event USING btree (utctime);

CREATE INDEX idx_archive_src ON archive_event USING btree (src);

CREATE INDEX idx_archive_bid_tid ON archive_event USING btree (tid, bid);




--FUNCTION
CREATE OR REPLACE FUNCTION console_get_source_rule_level_1() RETURNS SETOF RECORD AS
'
DECLARE

one_record record;
r_record record;

BEGIN
FOR r_record IN SELECT count(cid) AS hits,src, bid,
tid,NULL::int8 as min_time,NULL::int8 as max_time FROM archive_event
WHERE inst=\'3\' AND (utctime BETWEEN \'1114920000\' AND \'1131512399\')
GROUP BY src, bid, tid LOOP
SELECT INTO one_record MIN(utctime) as timestart,MAX(utctime) as
timestop from archive_event  where src =r_record.src AND bid
=r_record.bid  AND tid = r_record.tid AND inst =\'3\' AND (utctime
BETWEEN \'1114920000\' AND \'1131512399\');
r_record.min_time := one_record.timestart;r_record.max_time := one_record.timestop;    RETURN NEXT r_record;

END LOOP;
   RETURN;

END;
' LANGUAGE 'plpgsql' VOLATILE;
GRANT EXECUTE ON FUNCTION console_get_source_rule_level_1() TO console
WITH GRANT OPTION;


--FUNCTION CALLER
SELECT * from get_source_rule_level_1() AS (hits int8,src int8,bid
int8,tid int4,min_time int8,max_time int8)




-Eric Lauzon