Thread: (View and SQL) VS plpgsql

(View and SQL) VS plpgsql

From
"Eric Lauzon"
Date:
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


Re: (View and SQL) VS plpgsql

From
"Jim Buttafuoco"
Date:
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 -------