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