Thanks Stephan and Tom for your responses. We have been busy, so I haven't had time to do any further research on this till yesterday. I found that the large number of triggers on the parent or master table were foreign key triggers for each table back to the child tables (update and delete on master, insert on child). The triggers have existed through several versions of postgres and as far as we can tell were automatically created using the references keyword at inception.
Yesterday I dropped all the current triggers on parent and children and ran a script that did an alter table add foreign key constraint to each of the 67 child tables with update cascade delete cascade. After this, the delete from the parent where no records existed in the child tables was far more acceptable. Instead of taking hours to do the delete, the process ran for about 5 minutes on my workstation. Removing all constraints entirely reduces this time to a couple of seconds. I am currently evaluating if the foreign key constraints are worth the performance penalty in this particular case. To finish up, it appears that the foreign key implementation has changed since when these first tables were created in our database. Dropping the existing triggers and re-adding the constraints on each table significantly improved performance for us. I do not know enough of the internals to know why this happened. But our experience seems to prove that the newer implementation of foreign keys is more efficient then previous versions. YMMV One other item that was brought up was whether the child tables have the fk column indexed, and the answer was yes. Each had a standard btree index on the foreign key. Explain showed nothing as all the time was being spent in the triggers. Time spent in triggers is not shown in the pg 7.3.4 version of explain (nor would I necessarily expect it to). Thanks for your time, expertise and responses. -Chris On Tuesday 09 March 2004 7:18 pm, Stephan Szabo wrote: > On Wed, 3 Mar 2004, Chris Kratz wrote: > > Which certainly points to the triggers being the culprit. In reading the > > documentation, it seems like the "delete from only..." statement should > > ignore the constraint triggers. But it seems quite obvious from the > > Delete from only merely means that children of the table being deleted > will not have their rows checked against any where conditions and removed > for that reason. It does not affect constraint triggers at all. > > Given I'm guessing it's going to be running about 7000 * 67 queries to > check the validity of the delete for 7000 rows each having 67 foreign > keys, I'm not sure there's much to do other than hack around the issue > right now. > > If you're a superuser, you could temporarily hack reltriggers on the > table's pg_class row to 0, run the delete and then set it back to the > correct number. I'm guessing from your message that there's never any > chance of a concurrent transaction putting in a matching row in a way that > something is marked as deletable when it isn't? -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
