Why sequential scans matter

A sequential scan reads every row in a table to find the rows that match a WHERE clause. For a 1,000-row lookup table that is negligible. For a 10-million-row orders table, it means reading 80 MB (assuming 8 KB pages, ~10 rows per page) per query.

At 100 queries per minute, that is 8 GB of I/O per minute — saturating a gp2 EBS volume at ~3,000 IOPS. Latency climbs, autovacuum competes for I/O budget, cache hit rates drop.

Postgres records exactly this in pg_stat_user_tables. You do not need EXPLAIN ANALYZE on individual queries. The aggregate is enough to find the worst offenders.

The detection query

SELECT
  schemaname,
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  CASE
    WHEN seq_scan + idx_scan = 0 THEN NULL
    ELSE ROUND(100.0 * seq_scan / (seq_scan + idx_scan), 1)
  END AS seq_scan_pct,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
  AND seq_scan > 100
ORDER BY seq_scan DESC
LIMIT 20;

What each column tells you:

seq_scan — cumulative sequential scans since the last stats reset (usually since Postgres started or since you ran pg_stat_reset()).
seq_tup_read — total rows read by sequential scans. Divide by seq_scan to get average rows read per scan. If average rows read is close to n_live_tup, every sequential scan is a full table scan.
idx_scan — cumulative index scans. If this is zero on a large table, either no queries filter by indexed columns or the existing indexes are unused.
seq_scan_pct — the ratio you care about. Above 90% on a table larger than 10 MB is a strong signal that an index is missing or existing indexes are not being used.

Interpreting the output

High seq_scan, low seq_scan_pct (e.g., 30%)

The table is accessed via index most of the time, but there are periodic full scans — likely VACUUM, a reporting query, or a query with no WHERE clause. Probably fine. Check the query with the highest cost in pg_stat_statements.

High seq_scan, high seq_scan_pct (e.g., 95%), large table

Nearly all access is sequential. Look at seq_tup_read / seq_scan: - If it equals n_live_tup, every scan is a full table scan. You need an index. - If it is much less, queries are using LIMIT but still doing sequential scans (no index covers the ORDER BY). You need a different index.

Zero seq_scan on a table you know is accessed heavily

Stats have been reset, or all queries are hitting the index perfectly. Run SELECT pg_stat_reset() is destructive — do not do it in production unless you are sure. Instead, compare timestamps: SELECT stats_reset FROM pg_stat_bgwriter;

Finding the right column to index

Once you have a table with a high sequential scan ratio, you need to know which column to add the index on. There are two approaches.

Approach 1: pg_stat_statements

SELECT
  query,
  calls,
  mean_exec_time,
  total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%your_table_name%'
ORDER BY total_exec_time DESC
LIMIT 10;

Look at the WHERE clauses in the most expensive queries against that table. The column in the WHERE clause is your index candidate.

Approach 2: auto_explain

For queries that are hard to catch with pg_stat_statements (infrequent but expensive), enable auto_explain:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1000;
SET auto_explain.log_analyze = true;

Now Postgres logs the actual execution plan for slow queries, including which operations are sequential scans.

The cost of a full sequential scan at scale

A concrete example. Suppose you have an events table: - 5 million rows - Average row size ~200 bytes - Table size ~1 GB

A sequential scan requires reading the entire 1 GB from disk (or from shared_buffers if it fits). On a db.t3.micro (1 GB RAM, shared_buffers ~256 MB), the table does not fit in cache. Every sequential scan hits disk.

At 10 scans per minute: 10 GB/min of I/O.

gp3 EBS baseline throughput is 125 MB/s = 7.5 GB/min. Your sequential scans alone exceed the baseline throughput, and you have not accounted for writes, WAL, and autovacuum.

Adding a single B-tree index on the WHERE column reduces that scan to reading ~10 index pages plus ~1 table page per result row — orders of magnitude less I/O.

insightral PG-R01: sequential scan ratio detection

insightral's rule PG-R01 evaluates every table with more than 10,000 rows and a sequential scan ratio above 80%. The finding includes the table name, the ratio, the estimated table size, and the exact query pattern from pg_stat_statements (if pg_stat_statements is enabled) that is driving the scans.

The collector runs the detection query above on every 5-minute poll. You get a Slack alert with the table name, the ratio, and the recommended index column — without running any queries yourself.

Checking for existing unused indexes

Before adding a new index, verify that a similar index does not already exist but is unused:

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

An index with zero scans since the last stats reset is a candidate for removal. It costs write amplification (every INSERT, UPDATE, DELETE must update the index) without any read benefit.

Summary

1. Query pg_stat_user_tables with the detection query above. Sort by seq_scan DESC and look at tables larger than 10 MB with seq_scan_pct above 80%. 2. For each offending table, find the top queries via pg_stat_statements to identify the WHERE column. 3. Create a B-tree index on that column (use CREATE INDEX CONCURRENTLY to avoid locking the table in production). 4. Check for existing zero-scan indexes before adding new ones — clean up dead weight first. 5. Re-run the detection query after a week to confirm the ratio has dropped.

insightral PG-R01 automates steps 1 and 2 and fires a Slack alert before the sequential scans saturate your I/O budget.