pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Kratz <chris.kratz@vistashare.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete performance on delete from table with inherited tables
Date: Tue, 09 Mar 2004 19:07:17 -0500
Message-ID: <247.1078877237@sss.pgh.pa.us> (raw)
In-Reply-To: <200403031649.44937.chris.kratz@vistashare.com>
References: <200403031649.44937.chris.kratz@vistashare.com>

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



view thread (4+ messages)  latest in thread

Message-ID: <247.1078877237@sss.pgh.pa.us>
Permalink:  ../247.1078877237@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/247.1078877237@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, chris.kratz@vistashare.com
  Subject: Re: Delete performance on delete from table with inherited tables
  In-Reply-To: <247.1078877237@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox