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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend