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:[EMAIL PROTECTED] ________________________________________________________
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
