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

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_eventwhere src = ae.srcAND bid =ae.bidAND tid = ae.tidAND inst = '3'AND utctime
BETWEEN'1114920000' AND '1131512399'
 
) as min_time,
(select MAX(utctime)from) as max_timearchive_eventwhere src = ae.srcAND bid =ae.bidAND tid = ae.tidAND inst = '3'AND
utctimeBETWEEN '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 -------