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

From Klint Gore
Subject Re: How can I speed up this function?
Date
Msg-id 42C10C1338.6F82KG@129.180.47.120
Whole thread Raw
In response to How can I speed up this function?  (David Mitchell <david.mitchell@telogis.com>)
List pgsql-performance
On Tue, 28 Jun 2005 14:37:34 +1200, David Mitchell <david.mitchell@telogis.com> wrote:
>          FOR transaction IN SELECT t.trans_id as ID
>          FROM pending_trans AS t WHERE fetched = false
>          ORDER BY trans_id LIMIT 50

What the the average number of statements per transaction?  if avg > 2
then you could save a small amount of time by lowering the limit.

You might also save some time by using FOR UPDATE on the select since
the next thing you're going to do is update the value.


>         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

Have you explained this to make sure it's using the created index?  You
might need to order by both transaction_id, id.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

pgsql-performance by date:

Previous
From: Gnanavel Shanmugam
Date:
Subject: Re: How can I speed up this function?
Next
From: Tom Lane
Date:
Subject: Re: Poor index choice -- multiple indexes of the same columns