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.


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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to