When PostgreSQL sequential scans matter

A sequential scan reads table pages and evaluates rows without using an index to locate the starting rows. PostgreSQL's planner compares estimated plan costs and can correctly choose a sequential scan when a query needs much of a table, the table is small, or indexed access would cost more.

Sequential scans also support normal tasks such as analytics, exports, maintenance-related reads, and queries without a useful index. The presence of a Seq Scan node is therefore evidence about one plan—not proof of a missing index.

This read-only query ranks user tables by rows fetched through sequential scans and includes the database statistics reset timestamp. The counters describe activity accumulated in the available statistics window.

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.seq_scan,
  s.seq_tup_read,
  s.idx_scan,
  s.idx_tup_fetch,
  s.n_live_tup AS estimated_live_rows,
  CASE
    WHEN s.seq_scan > 0
    THEN round(s.seq_tup_read::numeric / s.seq_scan, 1)
  END AS average_rows_per_sequential_scan,
  d.stats_reset
FROM pg_stat_user_tables AS s
CROSS JOIN (
  SELECT stats_reset
  FROM pg_stat_database
  WHERE datname = current_database()
) AS d
ORDER BY s.seq_tup_read DESC
LIMIT 50;

Use plain EXPLAIN first. It shows the planner's estimated plan without executing the statement. Replace the sample table and predicate only with a query you are authorized to inspect.

EXPLAIN (COSTS, VERBOSE)
SELECT *
FROM public.orders
WHERE customer_id = 42;

• A large relation repeatedly reads many rows to return a small, selective result.
• A high-contribution query fingerprint aligns with the same table and time window.
• Planner row estimates differ substantially from observed rows in a deliberately reviewed execution plan.
• An expected supporting index is absent, invalid, unusable for the predicate, or poorly aligned with column order and operators.
• Statistics or planner assumptions may be stale after substantial table changes.

Even then, an index is only one possible response. Query shape, partition pruning, statistics quality, result selectivity, storage behavior, and write overhead all belong in the review.

• A high seq_scan count does not mean every scan was slow or read the entire table from disk.
• More sequential scans than index scans is not a universal problem threshold.
• Disabling sequential scans is a diagnostic influence on planner choices, not a general production fix.
• Creating an index can increase storage and write cost without improving a broad-read query.
• A cumulative table counter cannot identify the exact statement responsible; correlate it with workload evidence and plans.

The PostgreSQL health check captures bounded relation size, estimated rows, sequential scans, rows fetched sequentially, and index scans. It labels disproportionate sequential-scan activity as an observation and explicitly notes that small tables and analytical workloads can make it appropriate.

Connected telemetry can compare supported relation counters over time. The pg_stat_statements analyzer can separately rank normalized query fingerprints without SQL text, helping narrow the workload window before an authorized plan review.