Skip site navigation (1) Skip section navigation (2)

Re: How can I speed up this function?

From: Keith Worthington <KeithW(at)NarrowPathInc(dot)com>
To: David Mitchell <david(dot)mitchell(at)telogis(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How can I speed up this function?
Date: 2005-06-28 03:21:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
David Mitchell wrote:
> 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$
>         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 = 
>         FOR statement IN SELECT, s.transaction_id, s.table_name, 
> s.op,
>                 FROM dbmirror.pending_statement AS s
>                 WHERE s.transaction_id =
>                 ORDER BY 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;


I'm still a newbie and it may not affect performance but why are you 
aliasing the tables?  Can you not simply use

FOR transaction IN SELECT trans_id
                      FROM pending_trans
                     WHERE fetched = false
                     ORDER BY trans_id
                     LIMIT 50


FOR statement IN SELECT id,
                    FROM dbmirror.pending_statement
                   WHERE pending_statement.transaction_id =
                   ORDER BY

I am pretty sure that the ORDER BY is slowing down both of these 
queries.  Since you are going to go through the whole table eventually 
do you really need to sort the data at this point?

Kind Regards,

In response to


pgsql-performance by date

Next:From: David MitchellDate: 2005-06-28 03:31:48
Subject: Re: How can I speed up this function?
Previous:From: Christopher Kings-LynneDate: 2005-06-28 03:20:36
Subject: Re: How can I speed up this function?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group