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