Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: thilo <thilo.tanner@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Slow Bulk Delete
References: <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
Date: Mon, 17 May 2010 12:10:31 +0200
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vcug7tj0eorkce@immo.peufeu.com>
In-Reply-To: 
 <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
User-Agent: Opera Mail/10.10 (Linux)

> DELETE FROM table1 WHERE table2_id = ?

For bulk deletes, try :

DELETE FROM table1 WHERE table2_id IN (list of a few thousands ids)

- or use a JOIN delete with a virtual VALUES table
- or fill a temp table with ids and use a JOIN DELETE

This will save cliet/server roundtrips.

Now, something that can make a DELETE very slow is a non-indexed ON DELETE  
CASCADE foreign key : when you DELETE FROM table1 and it cascades to a  
DELETE on table2, and you forget the index on table2. Also check the time  
spent in triggers. Do you have a GIN index ?