Re: Delete performance on delete from table with inherited tables

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