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

<snip>
    FOR r_record IN SELECT count(cid) AS hits,src, bid, tid,NULL::int8 as min_time,NULL::int8 as max_time FROM
archive_eventWHERE 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.srcAND bid =r_record.bid  AND tid = r_record.tid AND inst =\'3\' AND (utctime BETWEEN \'1114920000\' AND
\'1131512399\');
</snip>


(it seems to me, that you might combine both queries)

1. have you ever tried to select the min/max within the first stmt ? as i see  you are reducing data in second stmt
usingsame key as in stmt 1. 
2. you are querying data using two keys  (int, utctime). you may create a combined index speeding up your query
3. same for grouping. you are grouping over three fields. composite indexing may helps (8.1 supports index based
grouping)

regards,

marcus





-----Ursprüngliche Nachricht-----
Von: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]Im Auftrag von Eric
Lauzon
Gesendet: Mittwoch, 9. November 2005 21:43
An: pgsql-performance@postgresql.org
Betreff: [PERFORM] (View and SQL) VS plpgsql



Hello all , i post this question here because i wasen't able to find
answer to my question elsewhere , i hope someone can answer.


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_eventWHERE 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.srcAND 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
[Recherche & Développement]
Above Sécurité / Above Security
Tél  : (450) 430-8166
Fax : (450) 430-1858

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org