Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: pgsql-performance@postgresql.org, "Jarrod Chesney"
	<jarrod.chesney@gmail.com>
Subject: Re: Delete performance
References: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
Date: Wed, 01 Jun 2011 01:11:39 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vwdb1pv8eorkce@apollo13>
In-Reply-To: <6940CBA1-BB1C-4030-B425-2DE9135CEAE3@gmail.com>
User-Agent: Opera Mail/11.10 (Linux)

> 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.