From: Klint Gore <kg@kgb.une.edu.au>
To: David Mitchell <david.mitchell@telogis.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How can I speed up this function?
Date: Tue, 28 Jun 2005 18:36:35 +1000
Message-ID: <42C10C1338.6F82KG@129.180.47.120> (raw)
In-Reply-To: <42C0B7EE.4040608@telogis.com>
References: <42C0B7EE.4040608@telogis.com>
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 : :
+---------------------------------------+-----------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kg@kgb.une.edu.au, david.mitchell@telogis.com
Subject: Re: How can I speed up this function?
In-Reply-To: <42C10C1338.6F82KG@129.180.47.120>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox