Re: Delete performance on delete from table with inherited tables

Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
> There are about 67 inherited tables that inherit the fields from this table,
> hence the 134 constraint triggers.

Why "hence"? Inheritance doesn't create any FK relationships. You must
have done so. What are those FK constraints exactly?

> Some of the documentation implies that inherited tables cause deletes to be
> very slow on the parent table, so I did the following experiment.

No, but foreign keys linked from tables that don't have indexes can be
pretty slow.

> it seems like the "delete from only..." statement should
> ignore the constraint triggers.

Why would you expect that?

It appears to me that this table is the referenced table for a large
number of foreign-key relationships, and thus when you delete a row from
it, many other tables have to be checked to verify that they do not
contain entries matching that row. That's going to be relatively slow,
even with indexes on the other tables. It's not very clear from your
description what the FK relationships actually do in your database
schema, but I would suggest looking at redesigning the schema so that
you do not need them.

regards, tom lane