Subject: Re: Optimizing DELETE
From: Csaba Nagy <nagy@ecircle-ag.com>
To: Ivan Voras <ivoras@fer.hr>
Cc: postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: <450FEF1A.4070106@fer.hr>
References: <450FEF1A.4070106@fer.hr>
Content-Type: text/plain
Message-Id: <1158675328.25023.216.camel@coppola.muc.ecircle.de>
Mime-Version: 1.0
Date: Tue, 19 Sep 2006 16:15:29 +0200
Content-Transfer-Encoding: 7bit

> I've just fired off a "DELETE FROM table" command (i.e. unfiltered 
> DELETE) on a trivially small table but with many foreign key references 
> (on similar-sized tables), and I'm waiting for it to finish. It's been 
> 10 minutes now, which seems very excessive for a table of 9000 rows on a 
> 3 GHz desktop machine.

If you have missing indexes on the child tables foreign keys, that might
be a cause of slow delete. The cascading delete must look up the to be
deleted rows in all child tables, which will do sequential scans if you
don't have proper indexes.

Try to do an explain analyze for deleting one row, that should also show
you the time spent in triggers, which might clue you in what's taking so
long.

Cheers,
Csaba.