Find Foreign Keys Without Supporting Indexes in PostgreSQL
Lists foreign keys with no index on the referencing side. A common cause of slow deletes, updates and lock contention.
SELECT c.conrelid::regclass AS table_name,
c.conname AS fk_name,
pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND (string_to_array(i.indkey::text, ' ')::int2[])[1:array_length(c.conkey, 1)] @> c.conkey
)
ORDER BY 1, 2;Unindexed foreign keys cause full table scans on child tables during DELETE/UPDATE operations on parent tables.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 10).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other POSTGRESQL Diagnostic Scripts
View All (100)Find Long-Running Queries and Idle-in-Transaction Sessions in PostgreSQL
Lists queries running longer than a minute, their wait events, and idle-in-transaction sessions that block vacuum. The first script to run during a performance incident.
PostgreSQL Lock Chain: Who Is Blocking Whom?
Uses pg_blocking_pids to pair blocked and blocking sessions with their queries and wait times. Shows the root cause of a lock chain in seconds.
PostgreSQL Dead Tuples, Bloat and Autovacuum Health Check
Shows dead tuple ratio and last vacuum/analyze times per table. Helps you find tables where autovacuum settings are not keeping up.
Find Unused Indexes in PostgreSQL
Lists never-scanned indexes that are neither unique nor primary keys, with their sizes. Surfaces cleanup candidates to cut write overhead and disk usage.