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
Date: Tue, 3 Jul 2007 05:33:42 -0600
Message-ID: <20070703113342.GA41193@winnie.fuhr.org> (raw)
In-Reply-To: <4689E727.5020408@p-dw.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, lists@p-dw.com
Subject: Re: Delete Cascade FK speed issue
In-Reply-To: <20070703113342.GA41193@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox