Re: Delete very slow after deletion of many rows in dependent

Hi Stephan,

caching of the execution plan is a good hint. We'll try it in a new connection.

Best Regards
Cornelius

Stephan Szabo wrote:

> 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).
>
>

--
________________________________________________________

Cornelius Buschka

arcus(x) GmbH
Hein-Hoyer-Straße 75 fon: +49 (0)40.333 102 92
D-20359 Hamburg fax: +49 (0)40.333 102 93

http://www.arcusx.com mailto:c(dot)buschka(at)arcusx(dot)com
________________________________________________________