Re: Delete very slow after deletion of many rows in

On Sun, 21 Nov 2004, Cornelius Buschka wrote:

> Hi,
>
> we saw the following problem:
>
> We deleted all rows from a table B referencing table A (~500000 records). No
> problem, but the following try to delete all records from table A (~180000) lead
> to a "never ending" statement. We found out, that vacuuming table B after delete
> did the trick.
>
> It seems to us the database has to do scan thru deleted records on B while
> deleting from A. Why did it last so long? An index on B.a_fk did not lead to
> imporvements. The query plan did not help.

An index seems to help for me. It's still kinda slow, but the real time
for the delete on A goes from more minutes than I was willing to wait to
about 19s.

However, if you'd already run the key without the index, refilled the
table, made the index and tried it again, it probably wouldn't have used
the index because it tries to cache the plan on first use in each session
(you'd need to start a new session to try again).