Re: Delete performance on delete from table with inherited tables - Mailing list pgsql-performance

From Tom Lane
Subject Re: Delete performance on delete from table with inherited tables
Date
Msg-id 247.1078877237@sss.pgh.pa.us
Whole thread Raw
In response to Delete performance on delete from table with inherited tables  (Chris Kratz <chris.kratz@vistashare.com>)
List pgsql-performance
Chris Kratz <chris.kratz@vistashare.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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: [ADMIN] syslog slowing the database?
Next
From: "Gavin M. Roy"
Date:
Subject: Re: [ADMIN] syslog slowing the database?