Missing Index on Foreign Key

Overview

Every foreign key constraint implies a join. When Postgres executes that join, it needs to find matching rows in the referencing table quickly. Without an index on the FK column, Postgres falls back to a sequential scan of the entire referencing table for every row in the referenced table—a classic nested loop that scales as O(n×m).

The fix is a single B-tree index on the FK column. Postgres will use it automatically for both the join and for ON DELETE CASCADE enforcement. The index can be created online with CONCURRENTLY, meaning no table lock and no downtime.

This rule fires when a foreign key exists on a column that has no single-column or leading-column composite index covering it. It ignores tables below 1,000 rows where a seq-scan is cheaper.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Finds FK columns that have no supporting index on the referencing side
SELECT
  c.conname          AS constraint_name,
  cl.relname         AS table_name,
  a.attname          AS column_name,
  pct.relname        AS referenced_table
FROM pg_constraint c
JOIN pg_class cl ON cl.oid = c.conrelid
JOIN pg_class pct ON pct.oid = c.confrelid
JOIN pg_attribute a ON a.attrelid = c.conrelid
  AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    JOIN pg_attribute ia
      ON ia.attrelid = i.indrelid
      AND ia.attnum = ANY(i.indkey)
    WHERE i.indrelid = c.conrelid
      AND ia.attnum = c.conkey[1]
  )
ORDER BY cl.relname, a.attname;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Replace <table> and <column> with the values from detection output
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_<table>_<column>
  ON <table> (<column>);

Worked example

Common false positives

• Tables under 1,000 rows — seq-scan is cheaper; insightral excludes these automatically.
• The FK column is already the first column of an existing composite index — the composite index satisfies the join; check pg_index.indkey ordering.