PostgreSQL Tables with Heavy Sequential Scans
Finds tables that read many rows through sequential scans. A first step in identifying missing-index candidates.
SELECT schemaname, relname, seq_scan, seq_tup_read,
idx_scan, n_live_tup,
round(seq_tup_read::numeric / NULLIF(seq_scan, 0)) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;Sequential scans on small tables are normal. Before adding an index, verify query execution plans using `EXPLAIN (ANALYZE, BUFFERS)`.
- 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: 9.x).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
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.