Unindexed Foreign Key Slow Delete
A DELETE on a small parent table, or an UPDATE that changes its primary key, is unexpectedly slow and contended even though it targets one row by primary...
In 10 seconds
Deletes against a parent table have become slow and are holding locks far longer than they should, and nobody can see why -- the parent row count is tiny and the delete touches a single row by primary key. The cause is structural and invisible in the query text: a child table has a foreign key to the parent, but the foreign key column on the child side has no index. PostgreSQL automatically indexes the referenced (parent primary key) side of every foreign key, but it never indexes the referencing (child) side for you. So every time a parent row is deleted -- or its key is updated -- the foreign key's referential-action check has to prove that no child row references it, and with no index that proof is a full sequential scan of the entire child table. The fix is a one-line CREATE INDEX, but the dangerous part is everything you cannot see: every other foreign key in the schema sitting on the same unindexed landmine. This recipe reproduces the full child scan on a 2,000,000-row child table, shows the free per-table check and the premium fleet-wide audit that finds every uncovered foreign key, adds the missing index, and wires the audit in as a standing guard.
Why This Happens
When you declare a foreign key, PostgreSQL guarantees referential integrity by checking, on every change to the parent key, that the relationship still holds. To make the parent side of that check fast it requires (and the system provides) a unique index on the referenced columns -- here parent's primary key, parent_pkey. But it deliberately does not create an index on the referencing columns on the child side, because not every workload needs one and an index is not free to maintain.
The consequence is that the 'is any child still pointing at this parent?' check -- which fires for ON DELETE / ON UPDATE referential actions such as NO ACTION, RESTRICT, CASCADE, and SET NULL -- has no access path on the child and must sequentially scan the entire child table to answer it. With 2,000,000 child rows that is 2,000,000 rows read to delete one parent row. Creating an index on the child's foreign key column (child.parent_id) gives that check a direct path: the referential lookup becomes an index scan instead of a full table scan, and the same index also speeds the ordinary 'fetch all children of this parent' joins your application already runs.
This is one of the few cases where the correct index is fully predictable from the schema alone -- any foreign key column with no covering index is a latent full-scan -- which is exactly why it can be audited and prevented mechanically rather than discovered one slow delete at a time. This is one of the few cases where the correct index is fully predictable from the schema alone -- any foreign key column with no covering index is a latent full-scan -- which is exactly why it can be audited and prevented mechanically rather than discovered one slow delete at a time.
Diagnose
Free Tier -- Basic Approach
The free diagnosis answers one question for the table in front of you: does this foreign key have a covering index on the child side? One catalog query over the child's foreign keys returns the answer, and a value of 'f' tells you exactly what to fix.
SELECT c.conname AS fk_name,
a.attname AS fk_column,
c.confrelid::regclass AS parent_table,
EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND (i.indkey::smallint[])[0:cardinality(c.conkey) - 1] OPERATOR(pg_catalog.@>) c.conkey
) AS has_covering_index
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f' AND c.conrelid = 'child'::regclass;
fk_name | fk_column | parent_table | has_covering_index ----------------------+-----------+--------------+-------------------- child_parent_id_fkey | parent_id | parent | f (1 row)
Query the child table's foreign keys, joining pg_constraint to pg_index, and compute has_covering_index by testing whether any index's leading columns match the foreign key columns. The result is decisive: 'child_parent_id_fkey | parent_id | parent | f' -- the foreign key on child.parent_id (referencing parent) has has_covering_index = f, i.e. no covering index.
That single 'f' is the whole free diagnosis: this foreign key needs an index on its child column. What it does not tell you is how many other foreign keys across the schema are in the same state, which is what the premium audit answers.
Fix
The fix is one line: create the missing index on the child's foreign key column. That gives the referential check a direct access path, so the next parent delete no longer has to scan the whole child table -- and the same index speeds the ordinary 'children of this parent' joins your application already runs.
CREATE INDEX child_parent_id_idx ON child(parent_id); ANALYZE child; SET max_parallel_workers_per_gather = 0;
CREATE INDEX ANALYZE SET
EXPLAIN (COSTS OFF) SELECT 1 FROM child WHERE parent_id = 7;
QUERY PLAN
------------------------------------------------
Bitmap Heap Scan on child
Recheck Cond: (parent_id = 7)
-> Bitmap Index Scan on child_parent_id_idx
Index Cond: (parent_id = 7)
(4 rows)
'CREATE INDEX child_parent_id_idx ON child(parent_id)' followed by ANALYZE. The same foreign-key-check-shaped lookup that was a 'Seq Scan on child' now plans against the new index: 'Bitmap Heap Scan on child' with 'Recheck Cond: (parent_id = 7)' driven by a 'Bitmap Index Scan on child_parent_id_idx' / 'Index Cond: (parent_id = 7)'.
The full child scan is gone -- the planner now reaches the matching rows through child_parent_id_idx. On a busy table use CREATE INDEX CONCURRENTLY so the build does not block writes; the resulting index and plan are the same.
Verify
Healthy-state proof: the foreign-key-check lookup is served by the new index instead of a full scan, and the covering-index audit that returned 'f' now returns 't'. The landmine is cleared, not just the single delete sped up.
SET max_parallel_workers_per_gather = 0;
QUERY PLAN
------------------------------------------------
Bitmap Heap Scan on child
Recheck Cond: (parent_id = 7)
-> Bitmap Index Scan on child_parent_id_idx
Index Cond: (parent_id = 7)
(4 rows)
EXPLAIN (COSTS OFF) SELECT 1 FROM child WHERE parent_id = 7;
QUERY PLAN
------------------------------------------------
Bitmap Heap Scan on child
Recheck Cond: (parent_id = 7)
-> Bitmap Index Scan on child_parent_id_idx
Index Cond: (parent_id = 7)
(4 rows)
SELECT c.conname AS fk_name,
a.attname AS fk_column,
EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND (i.indkey::smallint[])[0:cardinality(c.conkey) - 1] OPERATOR(pg_catalog.@>) c.conkey
) AS has_covering_index
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f' AND c.conrelid = 'child'::regclass;
fk_name | fk_column | has_covering_index ----------------------+-----------+-------------------- child_parent_id_fkey | parent_id | t (1 row)
Re-running the lookup under the index shows it served through child_parent_id_idx -- 'Bitmap Heap Scan on child' with a 'Bitmap Index Scan on child_parent_id_idx' and 'Index Cond: (parent_id = 7)' -- no 'Seq Scan on child' anywhere. Then the same covering-index check from the free step now reports 'child_parent_id_fkey | parent_id | t': has_covering_index has flipped from f to t.
A foreign key whose child column is backed by an index is the durable signal that parent deletes and key updates against it no longer trigger a full child scan.
⏪ Rollback
Every step here is non-destructive and reversible. The fix is a single 'CREATE INDEX child_parent_id_idx ON child(parent_id)', which adds an index and changes no data; to undo it you simply 'DROP INDEX child_parent_id_idx'. On a busy production table prefer 'CREATE INDEX CONCURRENTLY' so the build does not take a lock that blocks writes -- the result is identical, it just takes longer and cannot run inside a transaction block. No rows are modified by any step in this recipe; the only change to the database is the presence of one new index, and the standing-guard query is read-only.
📦 Version Matrix (PG 14--18)
An unindexed foreign key forcing a child-table scan, and the index that fixes it, are core executor behaviour identical across supported releases. On PostgreSQL 14 through 18 the foreign-key-shaped lookup is a full child scan with no index and an index scan once the index exists.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=Seq Scan on child | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=Seq Scan on child | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=Seq Scan on child | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=Seq Scan on child | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=Seq Scan on child | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Doesn't PostgreSQL automatically index foreign keys?
Only the parent side. A foreign key requires a unique index on the referenced columns (the parent's primary key or a unique constraint), and that index exists automatically. PostgreSQL does not create an index on the referencing columns on the child side, and the documentation explicitly recommends you add one yourself when the parent is deleted or updated, precisely because the referential check otherwise has to scan the whole child table.
Why is my DELETE slow when EXPLAIN shows the parent row found by an index scan?
Because the time is not in finding the parent row -- it is in the foreign key's referential check, which runs as a separate trigger. In EXPLAIN (ANALYZE) that cost appears on a line like 'Trigger for constraint child_parent_id_fkey: calls=1', not on the Index Scan line. With no index on the child's foreign key column, that check is a full sequential scan of the child table, and that is where the wall-clock time and lock duration go.
Does this affect updates too, or only deletes?
Both. The referential check fires whenever the parent's referenced key changes -- a DELETE of the parent row, or an UPDATE that changes the referenced column(s). For ON DELETE / ON UPDATE actions like NO ACTION, RESTRICT, CASCADE, and SET NULL, PostgreSQL must find the affected child rows, and without an index on the child foreign key column that means a full child scan each time.
How do I find every unindexed foreign key, not just this one?
That is the premium step. Read pg_constraint for every foreign key (contype = 'f'), and for each one check whether any index on the child table covers the foreign key columns as a leading prefix (via pg_index, comparing indkey against conkey). Foreign keys with no covering index are your prioritized list. Ranking them by child-table size puts the most damaging ones -- the largest tables -- first.
Should I build the index with CREATE INDEX CONCURRENTLY?
On a busy production table, yes. A plain CREATE INDEX takes a SHARE lock that blocks writes to the child table while the index builds, which on a large table can be a significant outage. CREATE INDEX CONCURRENTLY builds without blocking writes; it is slower, takes two table scans, and cannot run inside a transaction block, but it is the safe choice when the table is in use.