Re: DELETE running at snail-speed

On Thu, Dec 18, 2008 at 05:29:52AM -0800, gerhard wrote:
> I'm running a delete command on a postgresql-8.1.9 server. 25000
> tuples are delete, time is ~ 400 secs
> detailid | integer | not null default nextval ('downtime_detail_detailid_seq'::regclass)
> downtimeid | integer |
> detail | smallint |
> Indexes:
> "downtime_detail_pkey" PRIMARY KEY, btree (detailid)
> Foreign-key constraints:
> "statusid_ok" FOREIGN KEY (downtimeid) REFERENCES downtime (downtimeid) ON UPDATE CASCADE ON DELETE CASCADE
>
> I suspect the foreign key constraint of downtime_detail to slow down
> the delete process.

Try adding an index on "downtime_detail.downtimeid". If you have a lot
of entries in this table PG will spend a lot of time finding the entries
to delete them.

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

It's not really a bug (although there probably is code that could be
written to make this case go faster) you can see where PG is actually
spending time by doing an EXPLAIN ANALYSE on the DELETE. It should
display the time spent executing the triggers, but it's been a while
since I've used 8.1 so I'm not sure.

Sam