Re: Finding foreign keys that are missing indexes

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).

You don't have to worry about the referenced table because it must have
a unique index matching the referenced columns. On the referencing
side, the thing to do is try to match up pg_constraint.conkey with
pg_index.indkey. Something like

db=# create table m (f1 int, f2 int, primary key(f1));
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 (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 conkey[1] = indkey[0]);
conrelid | conname
----------+-----------
s | s_r1_fkey
(1 row)

db=# create index si on s(r1);
CREATE INDEX
db=# select conrelid::regclass,conname from pg_constraint where contype = 'f'
and not exists (select 1 from pg_index where indrelid=conrelid and conkey[1] = indkey[0]);
conrelid | conname
----------+---------
(0 rows)

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)

db=# create index si on s(r1,r2);
CREATE INDEX
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
----------+---------
(0 rows)

Ugh :-(

regards, tom lane