From: Pierre C <lists@peufeu.com>
To: pgsql-performance@postgresql.org, "Jarrod Chesney" <jarrod.chesney@gmail.com>
Subject: Re: Delete performance
Date: Wed, 01 Jun 2011 01:11:39 +0200
Message-ID: <op.vwdb1pv8eorkce@apollo13> (raw)
In-Reply-To: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
References: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
> 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.
Note that you should really do :
DELETE FROM table WHERE id IN (huge list of ids).
or
DELETE FROM table JOIN VALUES (list of ids) ON (...)
Also, check your foreign keys using cascading deletes have indexes in the
referencing tables. Without an index, finding the rows to cascade-delete
will be slow.
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: lists@peufeu.com, jarrod.chesney@gmail.com
Subject: Re: Delete performance
In-Reply-To: <op.vwdb1pv8eorkce@apollo13>
* 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