Message-ID: <4DE598A4.1080100@postnewspapers.com.au>
Date: Wed, 01 Jun 2011 09:40:52 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: pgsql-performance@postgresql.org,
	Jarrod Chesney <jarrod.chesney@gmail.com>
Subject: Re: Delete performance
References: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
	<op.vwdb1pv8eorkce@apollo13>
In-Reply-To: <op.vwdb1pv8eorkce@apollo13>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 1/06/2011 7:11 AM, Pierre C wrote:
>> If i run 30,000 prepared "DELETE FROM xxx WHERE "ID" = ?" commands it
>> takes close to 10 minutes.
>
> Do you run those in a single transaction or do you use one transaction
> per DELETE ?
>
> In the latter case, postgres will ensure each transaction is commited to
> disk, at each commit. Since this involves waiting for the physical I/O
> to happen, it is slow. If you do it 30.000 times, it will be 30.000
> times slow.

Not only that, but if you're doing it via some application the app has 
to wait for Pg to respond before it can send the next query. This adds 
even more delay, as do all the processor switches between Pg and your 
application.

If you really must issue individual DELETE commands one-by-one, I 
*think* you can use synchronous_commit=off or

  SET LOCAL synchronous_commit TO OFF;

See:

http://www.postgresql.org/docs/current/static/runtime-config-wal.html


-- 
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/