Message-ID: <42C0C518.4020109@telogis.com>
Date: Tue, 28 Jun 2005 15:33:44 +1200
From: David Mitchell <david.mitchell@telogis.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: KeithW@NarrowPathInc.com
Cc: pgsql-performance@postgresql.org
Subject: Re: How can I speed up this function?
References: <42C0B7EE.4040608@telogis.com>
 <42C0C226.8070201@NarrowPathInc.com>
In-Reply-To: <42C0C226.8070201@NarrowPathInc.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Keith,

Unfortunately, we must have those sorts. The statements within a 
transaction must be executed on the slave in the same order as they were 
on the master, and similarly, transactions must also go in the same 
order. As for aliasing the tables, that is just a remnant from previous 
versions of the code.

Thanks

David

Keith Worthington wrote:
> 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
> 
> and
> 
> FOR statement IN SELECT id,
>                         transaction_id,
>                         table_name,
>                         op,
>                         data
>                    FROM dbmirror.pending_statement
>                   WHERE pending_statement.transaction_id =
>                         transaction.trans_id
>                   ORDER BY pending_statement.id
> 
> 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?
> 


-- 
David Mitchell
Software Engineer
Telogis