Re: (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(dot)lauzon(at)abovesecurity(dot)com>
To: <pgsql-hackers(at)postgresql(dot)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 -------