Message-ID: <4689E727.5020408@p-dw.com>
Date: Tue, 03 Jul 2007 08:05:27 +0200
From: Patric de Waha <lists@p-dw.com>
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Delete Cascade FK speed issue
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

thanks in advance