Missing Index on a Foreign Key: Mesrai's EXPLAIN-Driven Suggestion

A migration adding an `org_id` foreign key. Author thought the FK constraint covered indexing. It does not. Postgres (and MySQL) require an explicit `CREATE INDEX`. Mesrai flagged it.

The vulnerable diff

-- migrations/2026_10_add_org.sql
ALTER TABLE projects
  ADD COLUMN org_id BIGINT REFERENCES orgs(id);

-- (no CREATE INDEX)
-- Every query like:
--   SELECT * FROM projects WHERE org_id = $1
--   SELECT p.* FROM projects p JOIN orgs o ON o.id = p.org_id
-- does a full table scan on projects.

What is wrong

Adding a FK constraint enforces referential integrity but does not create the index that makes lookups efficient. On a 30M-row `projects` table, a `WHERE org_id = $1` query without an index reads every row — hundreds of milliseconds where 1ms would do. Also: cascade deletes on the parent table become catastrophically slow without the child-side index. Postgres documentation has a section explicitly recommending an index on every FK column for this reason.

The attack

EXPLAIN shows the difference:

-- Without index
EXPLAIN ANALYZE SELECT * FROM projects WHERE org_id = 42;
-- Seq Scan on projects  (cost=0..485000)
-- Execution time: 380 ms

-- With index
CREATE INDEX projects_org_id_idx ON projects(org_id);
EXPLAIN ANALYZE SELECT * FROM projects WHERE org_id = 42;
-- Index Scan using projects_org_id_idx  (cost=0..4)
-- Execution time: 0.4 ms

On a 30M-row table the speedup is roughly 1000×.

Mesrai's review comment

mesraipilot · Bot · reviewed 2 min ago

[mesrai] [code-review] [Performance] [Index] [medium]

Adding a FK column does not auto-create the supporting index in
Postgres or MySQL. Every JOIN or WHERE on `org_id` will do a seq
scan on `projects` until you add one.

Add to the migration:

  CREATE INDEX CONCURRENTLY projects_org_id_idx ON projects(org_id);

`CONCURRENTLY` so it does not lock the table during the build —
required on any table large enough to matter. Run after the column
is backfilled.

Same rule applies to every FK in your schema. Audit with:

  SELECT c.conrelid::regclass AS table, a.attname AS column
  FROM pg_constraint c
  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 WHERE i.indrelid=c.conrelid
                  AND a.attnum=ANY(i.indkey));

The fix

-- migrations/2026_10_add_org.sql — fixed
ALTER TABLE projects
  ADD COLUMN org_id BIGINT REFERENCES orgs(id);

-- Backfill happens here (in a separate migration in some shops)

CREATE INDEX CONCURRENTLY projects_org_id_idx ON projects(org_id);

`CONCURRENTLY` does not block writes while building the index — required on any table with active traffic. Run after the column is backfilled so the index build only sees data. The audit query above finds every existing FK column without an index across the schema.

Why human review missed it

FK-without-index is invisible at PR review — the migration looks complete because the FK constraint is there. The performance cost only shows up in query plans, which reviewers rarely look at on a schema-change PR. Mesrai's rule pack flags every `ADD COLUMN ... REFERENCES` without a paired `CREATE INDEX`.

Related rules + further reading

Mesrai rule pack: performance/fk-needs-index — flags FK column additions without a paired index.

Postgres docs: Performance Tips — Indexes on Foreign Key Columns.

MySQL's InnoDB auto-creates indexes for FK columns; Postgres does not.

Takeaway

Every FK column needs an index. Add `CREATE INDEX CONCURRENTLY` in the same migration. Mesrai catches the gap on every schema PR.