Foreign key columns missing a covering index
does postgres automatically index foreign keys No, Postgres never creates an index on a foreign key column automatically, only on the primary key it references. An unindexed foreign key forces a sequential scan of the child table on every parent delete or update. Find them with a catalog query joining pg_constraint against pg_index, then add the missing index with CREATE INDEX CONCURRENTLY shipped as a migration.
Seen in 14 of 450 scanned projects (3%). Based on 450 Deep Scan runs across production projects.
How to tell you have it
- Deleting a row from the parent table takes seconds instead of milliseconds
- EXPLAIN ANALYZE on a join through a foreign key shows a sequential scan on the child table
- Row-level lock contention or timeouts appear specifically during parent-table deletes or updates
- A vector or trigram index exists in the schema but the query planner never chooses it
- The child table has grown large enough that the missing index only started hurting recently
Why it matters
Postgres automatically builds an index for a primary key and for any column with a unique constraint, but it does nothing for the foreign key column on the other side of that relationship. If you never explicitly create one, the child table has no index to support lookups by that foreign key value, and Postgres falls back to a full sequential scan whenever it needs to find matching child rows.
This becomes expensive specifically on delete or update of the parent row, because Postgres has to check the child table for rows that reference the parent, either to cascade the change or to verify the constraint would not be violated. On a small child table this is invisible. On a child table with millions of rows it turns a routine delete into a multi-second operation that holds a lock the whole time, which can cascade into timeouts elsewhere in the app.
The same pattern shows up with joins in normal application queries. Any query that joins on the foreign key column, which is extremely common, pays for the missing index every single time it runs, and the cost grows linearly with child table size. Teams often do not notice until the table crosses some size threshold and query latency jumps all at once.
A related but distinct issue is an index that exists on paper but the planner never uses, commonly a vector or trigram index created with the wrong operator class or against a query pattern that does not match how the index was built. EXPLAIN ANALYZE reveals this clearly: the index is present in the schema, costs storage and write overhead on every insert, and delivers zero benefit because every query still falls through to a sequential scan.
How Heygents detects it
Deep Scan connects to the project's own DATABASE_URL read from its .env with a read-only psql session and runs a catalog query joining pg_constraint, pg_class, and pg_index to list foreign key columns with no matching index, then optionally runs EXPLAIN ANALYZE on a representative join to confirm a sequential scan.
How to fix it
- List every foreign key with no covering index Run this catalog query directly against the project database. It joins the constraint catalog against the index catalog and returns only foreign keys that have no matching index on their columns.
- Verify the cost with EXPLAIN ANALYZE before fixing Confirm the sequential scan on the child table for a query pattern that actually happens in production, so you can compare before and after once the index is added.
- Create the index without locking the table Use CREATE INDEX CONCURRENTLY so the index build does not take an exclusive lock on the table, which matters on any table that is actively written to in production. It cannot run inside a transaction block.
- Re-run EXPLAIN ANALYZE to confirm the planner switched to an index scan Compare the plan and timing against the earlier run. You should see an index scan or bitmap index scan replacing the sequential scan, with execution time dropping accordingly.
- Ship the index as a migration, not a manual psql session Add the CREATE INDEX CONCURRENTLY statement to your migration tool so it is applied consistently across environments and tracked in version control, rather than run once by hand and forgotten. Most migration tools require running this outside their normal transaction wrapper since CONCURRENTLY is incompatible with transactions.
List every foreign key with no covering index
SELECT
conrelid::regclass AS child_table,
a.attname AS fk_column,
confrelid::regclass AS parent_table
FROM pg_constraint c
JOIN unnest(c.conkey) AS k(attnum) ON true
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.attnum
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND a.attnum = ANY(i.indkey)
);Verify the cost with EXPLAIN ANALYZE before fixing
EXPLAIN ANALYZE
SELECT * FROM <child_table> WHERE <fk_column> = 12345;Create the index without locking the table
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_<child_table>_<fk_column>
ON <child_table> (<fk_column>);Re-run EXPLAIN ANALYZE to confirm the planner switched to an index scan
EXPLAIN ANALYZE
SELECT * FROM <child_table> WHERE <fk_column> = 12345;Find this in your own projects, automatically
Heygents runs a read-only Deep Scan across every project you own, finds issues like this one, and hands you a ready-to-run fix an AI agent can execute and verify. A solo developer gets the audit, the backlog and the fix loop in one place.
Open Heygents →Frequently asked questions
Why doesn't Postgres index foreign keys automatically?
Postgres treats the index and the foreign key constraint as separate concerns by design, since not every foreign key benefits from an index, for example on a very small or rarely joined child table. The tradeoff is that developers have to add the index deliberately, and many simply forget.
Does adding an index slow down writes to the child table?
Yes, every insert or update on an indexed column has to update the index too, so there is a small write cost. In almost all cases the read benefit on joins and parent deletes far outweighs this, unless the table is extremely write-heavy with rare reads by that column.
Why is CREATE INDEX CONCURRENTLY important on a live table?
A plain CREATE INDEX takes a lock that blocks writes to the table for the duration of the build, which on a large table can mean minutes of blocked inserts and updates in production. CONCURRENTLY builds the index without that exclusive lock, at the cost of a slightly longer and more fragile build process.
My vector index exists but EXPLAIN shows a sequential scan anyway, why?
Usually the operator class does not match the query's distance operator, or the table is small enough that the planner correctly judges a sequential scan as cheaper. Check the index definition's operator class against the operator used in your ORDER BY or WHERE clause, and confirm the table has enough rows for the index to matter.