Re: DELETE running at snail-speed

On Thu, 2008-12-18 at 05:29 -0800, gerhard wrote:
> I suspect the foreign key constraint of downtime_detail to slow down
> the delete process. Is this a bug, probably fixed in latest version
> (8.1.x) or should I drop the constraint and recreate after deletion -
> which I only see as workaround ?

The foreign key is the cause indeed, but you should put an index on
downtime_detail(downtimeid) and it would work fine. What happens is that
for each row you delete from 'downtime' table, the following is done by
the foreign key triggers (cascade the deletion to the child tables):

delete from downtime_detail where downtimeid = $1

You can try to see what kind of plan you get for that by:

prepare test_001(integer) as delete from downtime_detail where
downtimeid = $1;

explain execute test_001(0);

Now multiply whatever you get there by the count of rows deleted from
'downtime' and you'll get the reason why it is slow... then try it again
with the above mentioned index in place.

Cheers,
Csaba.