Date: Tue, 3 Jul 2007 05:33:42 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Patric de Waha <lists@p-dw.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete Cascade FK speed issue
Message-ID: <20070703113342.GA41193@winnie.fuhr.org>
References: <4689E727.5020408@p-dw.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4689E727.5020408@p-dw.com>

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