pgconfigurator
pgconfigurator
Talk to sales

Sequential Scan

Appears in EXPLAIN asSeq Scan

Reads every row of a table from start to finish.

What it is

A Sequential Scan reads the table's heap pages in order, from the first block to the last, handing every row up the plan (after applying any Filter).

When the planner picks it

When there's no usable index for the query's conditions, or when the planner expects to return a large fraction of the table — at which point reading sequentially is cheaper than jumping around an index.

Is it good or bad?

Perfectly healthy for small tables and for queries that genuinely need most of the rows. It's a red flag when it scans a large table only to throw away almost everything via a Filter — that usually means a missing or unusable index.

In depth

How it works under the hood

A Sequential Scan asks the storage layer for every block of the table's heap, in physical order, starting at block 0. For each block it walks the line pointers and checks each tuple's visibility against your transaction's snapshot (MVCC), then applies any Filter before passing surviving rows upward.

Because the reads are sequential, the operating system's read-ahead and PostgreSQL's own ring buffer make this very efficient per byte — far more so than the random I/O of chasing an index. The catch is volume: a sequential scan always pays for the whole table, even if you only want three rows.

Reading the numbers

Consider a scan that throws away almost everything:

Seq Scan on big  (cost=0.00..17029.00 rows=18348 width=0)
                 (actual time=62.7..113.4 rows=80000 loops=1)
  Filter: ((cat = 7) AND (val = 999999))
  Rows Removed by Filter: 500000
  Buffers: shared hit=8329

Three things to look at:

  • Rows Removed by Filter dwarfs the rows kept. The scan read 580,000 rows to return 80,000 — work an index could have skipped.
  • rows estimated (18,348) vs actual (80,000) is off by ~4×. Estimates this wrong steer the rest of the plan badly.
  • Buffers: shared hit=8329 — 8,329 8 KB pages (~65 MB) touched. On a cold cache those become read= instead of hit=, and the scan gets much slower.

When it's the right plan

Don't reflexively "fix" a sequential scan. It is the correct choice when:

  • the table is small (a few hundred pages) — an index adds overhead for nothing;
  • the query genuinely needs most of the rows (e.g. a reporting aggregate);
  • you're reading the whole table on purpose (COUNT(*), a full export).

When to act, and how

Act when a large table is scanned to satisfy a selective predicate. Options, roughly in order:

  1. Add an index on the filter/join columns. A partial index (... WHERE status = 'open') is ideal when you always filter the same way.
  2. Make the predicate sargable. WHERE lower(email) = $1 can't use a plain index on email; an expression index on lower(email) (or a rewrite) can.
  3. Refresh statistics with ANALYZE if the row estimate is far from actual — sometimes the planner only picks the scan because it mis-estimates.
  4. Consider parallelism. On a big unavoidable scan, more workers (a Parallel Seq Scan under a Gather) cut wall-clock time.

What the analyzer flags here

  • Seq Scan hot spot — a sequential scan dominating the query's runtime
  • Filter discards most rows — the scan reads far more than it keeps (missing index)

Paste a plan into the analyzer →

Settings that influence it

effective_cache_sizerandom_page_costwork_mem

How we tune these →

FAQ

Is a sequential scan always bad?
No. On a small table, or when a query reads most of the rows anyway, a sequential scan is the right and fastest choice. It's only a problem when a big table is scanned to satisfy a selective condition that an index could serve.
How do I turn a Seq Scan into an Index Scan?
Create an index whose leading columns match the query's WHERE/JOIN conditions, then make sure the statistics are fresh (ANALYZE). If the planner still avoids it, the condition may not be sargable (e.g. a function wrapped around the column).

See also