Re: Delete Cascade FK speed issue

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