Re: How can I speed up this function? - Mailing list pgsql-performance

But in the function you are exiting the loop when the count hits 100. If you
do not want to limit the statements then remove the limit clause from the
query I've written.

with regards,
S.Gnanavel


> -----Original Message-----
> From: david.mitchell@telogis.com
> Sent: Tue, 28 Jun 2005 16:29:32 +1200
> To: s.gnanavel@inbox.com
> Subject: Re: [PERFORM] How can I speed up this function?
>
> Hi Gnanavel,
>
> Thanks, but that will only return at most 100 statements. If there is a
> transaction with 110 statements then this will not return all the
> statements for that transaction. We need to make sure that the function
> returns all the statements for a transaction.
>
> Cheers
>
> David
>
> Gnanavel Shanmugam wrote:
> > Merge the two select statements like this and try,
> >
> > SELECT t.trans_id as ID,s.id, s.transaction_id, s.table_name, s.op,
> s.data
> >    FROM pending_trans AS t join dbmirror.pending_statement AS s
> >    on (s.transaction_id=t.id)
> > WHERE t.fetched = false order by t.trans_id,s.id limit 100;
> >
> >  If the above query works in the way you want, then you can also do the
> > update
> > using the same.
> >
> > with regards,
> > S.Gnanavel
> >
> >
> >
> >>-----Original Message-----
> >>From: david.mitchell@telogis.com
> >>Sent: Tue, 28 Jun 2005 14:37:34 +1200
> >>To: pgsql-performance@postgresql.org
> >>Subject: [PERFORM] How can I speed up this function?
> >>
> >>We have the following function in our home grown mirroring package, but
> >>it isn't running as fast as we would like. We need to select statements
> >>from the pending_statement table, and we want to select all the
> >>statements for a single transaction (pending_trans) in one go (that is,
> >>we either select all the statements for a transaction, or none of
> them).
> >>We select as many blocks of statements as it takes to top the 100
> >>statement limit (so if the last transaction we pull has enough
> >>statements to put our count at 110, we'll still take it, but then we're
> >>done).
> >>
> >>Here is our function:
> >>
> >>CREATE OR REPLACE FUNCTION dbmirror.get_pending()
> >>   RETURNS SETOF dbmirror.pending_statement AS
> >>$BODY$
> >>
> >>DECLARE
> >>         count INT4;
> >>         transaction RECORD;
> >>         statement dbmirror.pending_statement;
> >>     BEGIN
> >>         count := 0;
> >>
> >>         FOR transaction IN SELECT t.trans_id as ID
> >>         FROM pending_trans AS t WHERE fetched = false
> >>         ORDER BY trans_id LIMIT 50
> >>     LOOP
> >>             update pending_trans set fetched =  true where trans_id =
> >>transaction.id;
> >>
> >>        FOR statement IN SELECT s.id, s.transaction_id, s.table_name,
> s.op,
> >>s.data
> >>                 FROM dbmirror.pending_statement AS s
> >>                 WHERE s.transaction_id = transaction.id
> >>                 ORDER BY s.id ASC
> >>             LOOP
> >>                 count := count + 1;
> >>
> >>                 RETURN NEXT statement;
> >>             END LOOP;
> >>
> >>             IF count > 100 THEN
> >>                 EXIT;
> >>             END IF;
> >>         END LOOP;
> >>
> >>         RETURN;
> >>     END;$BODY$
> >>   LANGUAGE 'plpgsql' VOLATILE;
> >>
> >>Table Schemas:
> >>
> >>CREATE TABLE dbmirror.pending_trans
> >>(
> >>   trans_id oid NOT NULL,
> >>   fetched bool DEFAULT false,
> >>   CONSTRAINT pending_trans_pkey PRIMARY KEY (trans_id)
> >>)
> >>WITHOUT OIDS;
> >>
> >>CREATE TABLE dbmirror.pending_statement
> >>(
> >>   id oid NOT NULL DEFAULT nextval('dbmirror.statement_id_seq'::text),
> >>   transaction_id oid NOT NULL,
> >>   table_name text NOT NULL,
> >>   op char NOT NULL,
> >>   data text NOT NULL,
> >>   CONSTRAINT pending_statement_pkey PRIMARY KEY (id)
> >>)
> >>WITHOUT OIDS;
> >>
> >>CREATE UNIQUE INDEX idx_stmt_tran_id_id
> >>   ON dbmirror.pending_statement
> >>   USING btree
> >>   (transaction_id, id);
> >>
> >>Postgres 8.0.1 on Linux.
> >>
> >>Any Help would be greatly appreciated.
> >>
> >>Regards
> >>
> >>--
> >>David Mitchell
> >>Software Engineer
> >>Telogis
> >>
> >>---------------------------(end of
> broadcast)---------------------------
> >>TIP 8: explain analyze is your friend
>
>
> --
> David Mitchell
> Software Engineer
> Telogis