(View and SQL) VS plpgsql

try this, i had no data to check the plan and didn't have time to invent any.
Jim

create index idx_archive_jb_idx on
archive_event(inst,utctime,src,bid,tid);

explain
SELECT count(cid) AS hits,src, bid,
tid,
(select MIN(utctime)
from archive_event
where src = ae.src
AND bid =ae.bid
AND tid = ae.tid
AND inst = '3'
AND utctime BETWEEN '1114920000' AND '1131512399'
) as min_time,
(select MAX(utctime)
from) as max_time
archive_event
where src = ae.src
AND bid =ae.bid
AND tid = ae.tid
AND inst = '3'
AND utctime BETWEEN '1114920000' AND '1131512399
FROM archive_event ae
WHERE inst='3'
AND (utctime BETWEEN '1114920000' AND '1131512399')
GROUP BY src, bid, tid
;

---------- Original Message -----------
From: "Eric Lauzon" <eric.lauzon@abovesecurity.com>
To: <pgsql-hackers@postgresql.org>
Sent: Fri, 11 Nov 2005 19:12:00 -0500
Subject: [HACKERS] (View and SQL) VS plpgsql

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,
utctime int4 NOT NULL,
CONSTRAINT ids_archives_event_pkey PRIMARY KEY (inst, cid),
CONSTRAINT ids_archives_event_cid_index UNIQUE (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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------- End of Original Message -------