DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=content-type:mime-version:subject:from:in-reply-to:date
	:content-transfer-encoding:message-id:references:to:x-mailer;
	b=PMB1IUEPtHAiYnRNgVYs9CjxejKWV+6o7i3x3d8jaK2OpxbZGXB401MbkhOws02dxU
	tqwTVTQIJl9eb9aIlv/otIn4/Si+MOCCJ60+EsHnRszil34ND1upSpUpyo7exGvoc4BC
	tO2Anex6Qn0LB6VPXhN6nQyQvN2T0pDINvqqY=
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1084)
Subject: Re: Delete performance
From: Jarrod Chesney <jarrod.chesney@gmail.com>
In-Reply-To: <154D1F3C-0605-434C-BA1D-246CF257DB32@gmail.com>
Date: Thu, 2 Jun 2011 11:46:38 +1000
Content-Transfer-Encoding: quoted-printable
Message-Id: <331CAE09-C7F5-4539-BCDD-5BF216CF9F86@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>
To: pgsql-performance@postgresql.org


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

> I'm executing 30,000 single delete statements in one transaction.
>=20
> 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.
>=20
> On 01/06/2011, at 11:40 AM, Craig Ringer wrote:
>=20
>> 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/
>=20

Apologies for top posting, Sorry.=