From: Mark Lewis <mark.lewis@mir3.com>
To: Patric de Waha <lists@p-dw.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete Cascade FK speed issue
Date: Tue, 03 Jul 2007 09:01:05 -0700
Message-ID: <1183478465.387.143.camel@archimedes> (raw)
In-Reply-To: <4689E727.5020408@p-dw.com>
References: <4689E727.5020408@p-dw.com>
On Tue, 2007-07-03 at 08:05 +0200, Patric de Waha wrote:
> Hi,
> I've dbase with about 80 relations.
> On deleting a user, this cascades through all the tables.
> This is very slow, for 20 users it takes 4 hours, with exclusive
> access to the dbase.
> No other users connected to the dbase.
>
> Ok I know there will be somewhere a relation with a FK without
> index, which
> is being scanned sequentially. But how can I find out what postgres
> is doing
> while it is handling the transaction?
>
> Is there a way I can find out what postgres does, and where it hangs
> around, so I know
> where the FK might not be indexed. (The dbase is to big to analyze
> it by hand).
>
> The way I do it now is to check the pg_locks relation, but this is
> not very representative.
>
> Is there profiling method for triggers/constraints, or a method
> which gives me a hint
> why it is taking so long?
In 8.1 and later, an EXPLAIN ANALYZE of the delete will show you the
amount of time spent in each trigger. Remember that it will still
perform the delete, so if you want to be able to re-run the DELETE over
and over as you add missing indexes, run it in a transaction and
rollback each time. That will tell you which foreign key constraint
checks are taking up time. The output will not be nearly as useful if
you don't name your foreign key constraints, but is still better than
nothing.
Alternatively, you can just dump the schema to a text file and spend 30
minutes and some text searching to reconstruct your foreign key
dependency graph rooted at the table in question and check each column
for proper indexes. We recently did this for a 150 relation database,
it's not as painful as you seem to think it is. An 80 relation database
is by no means "too big to analyze" :)
-- Mark Lewis
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: mark.lewis@mir3.com, lists@p-dw.com
Subject: Re: Delete Cascade FK speed issue
In-Reply-To: <1183478465.387.143.camel@archimedes>
* 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