Subject: Re: Delete Cascade FK speed issue
From: Mark Lewis <mark.lewis@mir3.com>
To: Patric de Waha <lists@p-dw.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4689E727.5020408@p-dw.com>
References: <4689E727.5020408@p-dw.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Tue, 03 Jul 2007 09:01:05 -0700
Message-Id: <1183478465.387.143.camel@archimedes>
Mime-Version: 1.0

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