plu tard <[email protected]> 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
