Re: Finding foreign keys that are missing indexes

From: plu 12 <plutard12(at)hotmail(dot)com>
To: <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Finding foreign keys that are missing indexes
Date: 2008-12-29 00:32:29
Message-ID: BLU115-W25ECA52218B78B91BAEAD5A6E60@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


> plu tard <plutard12(at)hotmail(dot)com> writes:
> > I'm aware that if you create a foreign key constraint, no indexes are automatically created.
> > I would like to find a way to programatically inspect all my foreign keys and identify possibly missing indexes on either table (either the table defining the constraint or the table being referenced).

[snip]

Tom Lane writes:
> The above only works for single-column fkeys and indexes, though, and
> extending it to multicolumn is a bit of a PITA. You can't just compare
> conkey as a whole to indkey because (for historical reasons) indkey has
> zero-based array indexes instead of 1-based. Even aside from that, we'd
> really want the code to recognize that an index on (f1,f2) is usable for
> a constraint on (f2,f1). So it seems that you need something like this:
>
> db=# create function sortarray(int2[]) returns int2[] as
> db-# 'select array(select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) i order by 1)' language sql;
> CREATE FUNCTION
> db=# create table m (f1 int, f2 int, primary key(f1,f2));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "m_pkey" for table "m"
> CREATE TABLE
> db=# create table s (r1 int, r2 int, foreign key (r2,r1) references m);
> CREATE TABLE
> db=# select conrelid::regclass,conname from pg_constraint where contype = 'f'
> and not exists (select 1 from pg_index where indrelid=conrelid and sortarray(conkey) = sortarray(indkey));
> conrelid | conname
> ----------+-----------
> s | s_r2_fkey
> (1 row)

Wonderful. Thank you, Tom.

Technically, would we also need to worry about constraints that are a prefix of an index? e.g., that an index on (f1, f2) would be usable for a constraint on (f1)?

_________________________________________________________________
It’s the same Hotmail®. If by “same” you mean up to 70% faster.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_broad1_122008

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-12-29 00:42:36 Re: Finding foreign keys that are missing indexes
Previous Message Greg Smith 2008-12-28 22:04:47 Re: Bgwriter and pg_stat_bgwriter.buffers_clean aspects