Thread: Delete Cascade FK speed issue

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

On Tue, Jul 03, 2007 at 08:05:27AM +0200, Patric de Waha wrote:
> 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).

You could query the system catalogs to look for foreign key constraints
that don't have an index on the referencing column(s).  Something like
the following should work for single-column foreign keys:

select n1.nspname,
       c1.relname,
       a1.attname,
       t.conname,
       n2.nspname as fnspname,
       c2.relname as frelname,
       a2.attname as fattname
  from pg_constraint t
  join pg_attribute  a1 on a1.attrelid = t.conrelid and a1.attnum = t.conkey[1]
  join pg_class      c1 on c1.oid = t.conrelid
  join pg_namespace  n1 on n1.oid = c1.relnamespace
  join pg_class      c2 on c2.oid = t.confrelid
  join pg_namespace  n2 on n2.oid = c2.relnamespace
  join pg_attribute  a2 on a2.attrelid = t.confrelid and a2.attnum = t.confkey[1]
 where t.contype = 'f'
   and not exists (
         select 1
           from pg_index i
          where i.indrelid = t.conrelid
            and i.indkey[0] = t.conkey[1]
       )
 order by n1.nspname,
          c1.relname,
          a1.attname;

--
Michael Fuhr

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