From: Pierre C <lists@peufeu.com>
To: thilo <thilo.tanner@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow Bulk Delete
Date: Mon, 17 May 2010 12:10:31 +0200
Message-ID: <op.vcug7tj0eorkce@immo.peufeu.com> (raw)
In-Reply-To: <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
References: <f923f5db-a6b1-4029-8ebe-a6d1cc35932a@e2g2000yqn.googlegroups.com>
> 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 ?
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, thilo.tanner@gmail.com
Subject: Re: Slow Bulk Delete
In-Reply-To: <op.vcug7tj0eorkce@immo.peufeu.com>
* 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