DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	b=AeToniNFZpFzDntRQ0W/Lti5A8R9AzExhYrAX6S0sb8a1XCunHEdyVQBr/UvqSB3zS
	sg0tH8RbgIQ0rTPzFvu2HN49gauXmXVchje9o6P1rFJ2Yl8yfzSfpENCWlvdY2lJAqNL
	b80LoJ9GQXhRMmWgDaD7edTFilp4srSF0zi6M=
Subject: Re: Delete performance
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
From: Jarrod Chesney <jarrod.chesney@gmail.com>
In-Reply-To: <4DE598A4.1080100@postnewspapers.com.au>
Date: Wed, 1 Jun 2011 11:45:04 +1000
Cc: Pierre C <lists@peufeu.com>,
 pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <154D1F3C-0605-434C-BA1D-246CF257DB32@gmail.com>
References: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
	<op.vwdb1pv8eorkce@apollo13>
	<4DE598A4.1080100@postnewspapers.com.au>
To: Craig Ringer <craig@postnewspapers.com.au>

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" =3D ?" commands =
it
>>> takes close to 10 minutes.
>>=20
>> Do you run those in a single transaction or do you use one =
transaction
>> per DELETE ?
>>=20
>> 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.
>=20
> 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.
>=20
> If you really must issue individual DELETE commands one-by-one, I =
*think* you can use synchronous_commit=3Doff or
>=20
> SET LOCAL synchronous_commit TO OFF;
>=20
> See:
>=20
> http://www.postgresql.org/docs/current/static/runtime-config-wal.html
>=20
>=20
> --=20
> Craig Ringer
>=20
> Tech-related writing at http://soapyfrogs.blogspot.com/