plu tard <plutard12@xxxxxxxxxxx> 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 (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
