From: Chris Kratz <chris.kratz@vistashare.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete performance on delete from table with inherited tables
Date: Wed, 31 Mar 2004 10:42:48 -0500
Message-ID: <200403311042.48630.chris.kratz@vistashare.com> (raw)
In-Reply-To: <20040309155908.Y9964@megazone.bigpanda.com>
References: <200403031649.44937.chris.kratz@vistashare.com>
<20040309155908.Y9964@megazone.bigpanda.com>
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: chris.kratz@vistashare.com, sszabo@megazone.bigpanda.com
Subject: Re: Delete performance on delete from table with inherited tables
In-Reply-To: <200403311042.48630.chris.kratz@vistashare.com>
References: <200403031649.44937.chris.kratz@vistashare.com> <20040309155908.Y9964@megazone.bigpanda.com> <200403311042.48630.chris.kratz@vistashare.com>
* 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