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

I think the following logic will do want you expect
    FOR statement IN <previous_query> LOOP
          -- update statement goes here --
           if count > 100 and temp <> transaction_id then
                 // reaches here only if the transaction is complete
                 return;
           else
                count:= count+1;
                temp:=transaction_id;
           end if;
    end loop;

with regards,
S.Gnanavel


> -----Original Message-----
> From: david.mitchell@telogis.com
> Sent: Tue, 28 Jun 2005 16:55:00 +1200
> To: s.gnanavel@inbox.com
> Subject: Re: [PERFORM] How can I speed up this function?
>
> The function I have exits the loop when the count hits 100 yes, but the
> inner loop can push the count up as high as necessary to select all the
> statements for a transaction, so by the time it exits, the count could
> be much higher. I do want to limit the statements, but I want to get
> enough for complete transactions.
>
> David
>
> Gnanavel Shanmugam wrote:
> > 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
>
>
> --
> David Mitchell
> Software Engineer
> Telogis