Обсуждение: Finding foreign keys that are missing indexes

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

I wasn't able to find anything searching Google or the pg archives.

Attached is a first attempt. Just run the missing-fk-indexes.sql through psql. e.g.,

  psql -q mydb -f missing-fk-indexes.sql

I know the output can be improved, but is this headed toward the right direction and/or is there already a simpler way to accomplish this?

Briefly, it finds all the unique tables/columns referenced by foreign keys. Then it examines all the indexes, looking for any that are a prefix of the fk columns. It writes out any tables/columns where no indexes are found, followed by a list of the fk's that reference those tables/columns.

Also attached is a trivial test schema to run it against.

---

Life on your PC is safer, easier, and more enjoyable with Windows Vista®. See how

plu tard <plutard12@hotmail.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
eithertable (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

> plu tard <plutard12@hotmail.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. Get your account now.

plu 12 <plutard12@hotmail.com> writes:
> 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)? 

I guess you could, but it seems overly complicated for something that's
just meant to locate trouble spots.

            regards, tom lane