How to find unindexed foreign keys

Often performance issue is missing index on foreign keys. With few queries is very simple to find foreign keys without index:

1. Create a demo:
   

   create table a(id serial primary key, v text);
   create table b(id serial primary key, id_a integer references a(id), v text);
   

2. Create auxiliary functions:
   

   -- returns a subset of column names specified by their nums in array
   create or replace function attnames(oid, smallint[])
   returns name[] as $$
   select array_agg(attname )
     from (select attname
             from pg_attribute 
            where attrelid = $1 and attnum = any($2)
            order by attnum) s
   $$ language sql;
   
   -- ensure safe quoting of column names for usage in SQL
   create or replace function qnames(name[])
   returns text as $$
   select string_agg(v, ',')
     from (select quote_ident(n) v
             from unnest($1) g(n)) s
   $$ language sql;
   

3. Following query returns foreign key constraint name, table name and related columns:
   

   select conname, conrelid::regclass, attnames(conrelid, conkey) 
     from pg_constraint
    where contype = 'f';
   
   ┌─────────────┬──────────┬──────────┐
   │   conname   │ conrelid │ attnames │
   ╞═════════════╪══════════╪══════════╡
   │ b_id_a_fkey │ b        │ {id_a}   │
   └─────────────┴──────────┴──────────┘
   (1 row)
   

4. We can check if any attnames from this result are covered by some index:
   

   select conname, conrelid::regclass, attnames(conrelid, conkey)
     from pg_constraint
    where contype = 'f'
      and not exists (select *
                        from pg_index
                       where indrelid = conrelid
                         and attnames(conrelid,conkey) = attnames(conrelid, indkey::smallint[]));
   
   -- list of foreign keys without index
   ┌─────────────┬──────────┬──────────┐
   │   conname   │ conrelid │ attnames │
   ╞═════════════╪══════════╪══════════╡
   │ b_id_a_fkey │ b        │ {id_a}   │
   └─────────────┴──────────┴──────────┘
   (1 row)
   

5. Next step can be generating CREATE INDEX commands:
   

   select format('create index on %s(%s)',
                  conrelid::regclass,
                  qnames(attnames(conrelid, conkey)))
     from pg_constraint
    where contype = 'f'
      and not exists (select *
                        from pg_index
                       where indrelid = conrelid
                         and attnames(conrelid,conkey) = attnames(conrelid, indkey::smallint[]));
   
   ┌─────────────────────────┐
   │         format          │
   ╞═════════════════════════╡
   │ create index on b(id_a) │
   └─────────────────────────┘
   (1 row)
   

6. Inside modern psql we can execute last query with \gexec command, that ensures executing result too.
   

Small note about placeholders symbols in format function. We should to use %I for SQL identifiers to be result correctly escaped.

select format('(1):%I, (2):%s', 'BadTableName', 'BadTableName');
┌──────────────────────────────────────┐
│                format                │
╞══════════════════════════════════════╡
│ (1):"BadTableName", (2):BadTableName │
└──────────────────────────────────────┘
(1 row)

This case is exception, because I use casting from Oid to regclass, that enforce correct escaping. Two times escaping can produce wrong result. So %I cannot be used here.

create table "BadName"(a int);

select '"BadName"'::regclass::oid;
┌───────┐
│  oid  │
╞═══════╡
│ 16419 │
└───────┘
(1 row)

select 16419::regclass;
┌───────────┐
│ regclass  │
╞═══════════╡
│ "BadName" │
└───────────┘
(1 row)

postgres=# select format('%I, %s', 16419::regclass, 16419::regclass);
┌──────────────────────────┐
│          format          │
╞══════════════════════════╡
│ """BadName""", "BadName" │
└──────────────────────────┘
(1 row)