What is this issue?
When you create a foreign key constraint, PostgreSQL automatically creates an index on the
referenced (parent) table's primary key, but NOT on the referencing (child) table's
foreign key columns.
Without an index on the child table's FK columns:
- DELETEs on the parent table scan the entire child table
- UPDATEs to the parent's primary key scan the entire child table
- JOINs between the tables may be slow
This becomes increasingly problematic as tables grow.
Why it matters
Slow DELETEs
Deleting a parent row requires scanning the entire child table to check for references
Lock Contention
Long-running scans hold locks that block other operations
Cascade Performance
ON DELETE CASCADE operations can take minutes or hours on large tables
Join Performance
Queries joining on FK columns don't benefit from index lookups
How PG Pilot detects it
```sql
SELECT
c.conrelid::regclass AS child_table,
c.conname AS constraint_name,
a.attname AS fk_column,
c.confrelid::regclass AS parent_table
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey)
AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND a.attnum = ANY(i.indkey)
);
```
How to fix it
Identify the foreign key columns
List all foreign keys and their columns:
SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS referenced_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';Create the missing index
Add an index on the foreign key column(s):
CREATE INDEX CONCURRENTLY idx_child_table_fk_column
ON child_table (fk_column);For composite foreign keys, include all columns in the index.
Verify the fix
Test that DELETE operations on the parent table are now fast:
EXPLAIN ANALYZE
DELETE FROM parent_table WHERE id = 12345;You should see an Index Scan on the child table, not a Seq Scan.
Prevention
- Always create indexes on foreign key columns
- Use database migration tools that auto-create FK indexes
- Review new foreign key constraints before deploying
- Run PG Pilot checks as part of CI/CD