This seems to have a few false positives. Multi-column indexes that have the first column as the foreign key work as well as a single column index. For more modern postgres, partial indexes with `WHERE column_name IS NOT NULL` on columns that can be null are also valid and more performant.

Here's what we use in CI to check for missing indexes:

-- Unindexed FK -- Missing indexes - For CI

WITH y AS (
SELECT
pg_catalog.format('%I', c1.relname) AS referencing_tbl,
pg_catalog.quote_ident(a1.attname) AS referencing_column,
(SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement
FROM pg_catalog.pg_constraint t
JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid
JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid
JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1]
WHERE t.contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_index i
WHERE i.indrelid = t.conrelid
AND i.indkey[0] = t.conkey[1]
AND indpred IS NULL
)
)
SELECT referencing_tbl || '.' || referencing_column as column
FROM y
WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)'))
ORDER BY 1;

Additionally I have this to specify the index creation commands (CONCURRENTLY is recommended for existing tables in production as it doesn't cause locking):

-- Unindexed FK -- Missing indexes - Show Create Syntax

WITH y AS (
SELECT
pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl,
pg_catalog.quote_ident(a1.attname) AS referencing_column,
(SELECT pg_get_expr(indpred, indrelid) FROM pg_catalog.pg_index WHERE indrelid = t.conrelid AND indkey[0] = t.conkey[1] AND indpred IS NOT NULL LIMIT 1) partial_statement,
t1.typname AS referencing_type,
t.conname AS existing_fk_on_referencing_tbl,
pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl,
pg_catalog.quote_ident(a2.attname) AS referenced_column,
t2.typname AS referenced_type,
pg_relation_size( pg_catalog.format('%I.%I', n1.nspname, c1.relname) ) AS referencing_tbl_bytes,
pg_relation_size( pg_catalog.format('%I.%I', n2.nspname, c2.relname) ) AS referenced_tbl_bytes,
pg_catalog.format($$CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %s%I(%I)%s;$$, c1.relname || '_' || a1.attname || 'x' , CASE WHEN n1.nspname = 'public' THEN '' ELSE n1.nspname || '.' END, c1.relname, a1.attname, CASE WHEN a1.attnotnull THEN '' ELSE ' WHERE ' || a1.attname || ' IS NOT NULL' END) AS suggestion
FROM pg_catalog.pg_constraint t
JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1]
JOIN pg_catalog.pg_type t1 ON a1.atttypid = t1.oid
JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid
JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid
JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1]
JOIN pg_catalog.pg_type t2 ON a2.atttypid = t2.oid
WHERE t.contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_index i
WHERE i.indrelid = t.conrelid
AND i.indkey[0] = t.conkey[1]
AND i.indpred IS NULL
)
)
SELECT referencing_tbl,
referencing_column,
existing_fk_on_referencing_tbl,
referenced_tbl,
referenced_column,
pg_size_pretty(referencing_tbl_bytes) AS referencing_tbl_size,
pg_size_pretty(referenced_tbl_bytes) AS referenced_tbl_size,
suggestion
FROM y
WHERE (partial_statement IS NULL OR partial_statement <> ('(' || referencing_column || ' IS NOT NULL)'))
ORDER BY
referencing_tbl_bytes DESC,
referenced_tbl_bytes DESC,
referencing_tbl,
referenced_tbl,
referencing_column,
referenced_column;