X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7E99ED1CA6C for ; Wed, 10 Mar 2004 00:07:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04893-08 for ; Tue, 9 Mar 2004 20:07:17 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 8CDBFD1C9C4 for ; Tue, 9 Mar 2004 20:07:14 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2A07Hhc000248; Tue, 9 Mar 2004 19:07:17 -0500 (EST) To: Chris Kratz Cc: pgsql-performance@postgresql.org Subject: Re: Delete performance on delete from table with inherited tables In-reply-to: <200403031649.44937.chris.kratz@vistashare.com> References: <200403031649.44937.chris.kratz@vistashare.com> Comments: In-reply-to Chris Kratz message dated "Wed, 03 Mar 2004 16:49:44 -0500" Date: Tue, 09 Mar 2004 19:07:17 -0500 Message-ID: <247.1078877237@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/120 X-Sequence-Number: 5976 Chris Kratz 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