pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jarrod Chesney <jarrod.chesney@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Delete performance
Date: Thu, 2 Jun 2011 11:46:38 +1000
Message-ID: <331CAE09-C7F5-4539-BCDD-5BF216CF9F86@gmail.com> (raw)
In-Reply-To: <154D1F3C-0605-434C-BA1D-246CF257DB32@gmail.com>
References: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
	<op.vwdb1pv8eorkce@apollo13>
	<4DE598A4.1080100@postnewspapers.com.au>
	<154D1F3C-0605-434C-BA1D-246CF257DB32@gmail.com>


On 01/06/2011, at 11:45 AM, Jarrod Chesney wrote:

> I'm executing 30,000 single delete statements in one transaction.
> 
> At this point i'm looking into combining the multiple deletes into one statement and breaking my big transaction into smaller ones of about 100 deletes or so.
> 
> On 01/06/2011, at 11:40 AM, Craig Ringer wrote:
> 
>> 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/
> 

Apologies for top posting, Sorry.


view thread (7+ messages)

Message-ID: <331CAE09-C7F5-4539-BCDD-5BF216CF9F86@gmail.com>
Permalink:  ../331CAE09-C7F5-4539-BCDD-5BF216CF9F86@gmail.com/
Also on:    postgresql.org/message-id/331CAE09-C7F5-4539-BCDD-5BF216CF9F86@gmail.com

 · 

reply
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: jarrod.chesney@gmail.com
  Subject: Re: Delete performance
  In-Reply-To: <331CAE09-C7F5-4539-BCDD-5BF216CF9F86@gmail.com>

* 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