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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
