TRT
Sign in
mediumExpertconcept#query-plans#cost-model

When is a sequential scan better than an index scan?

Compare selective index access against ordered full-table reads, and use estimated versus actual rows to confirm which plan the data supports.

TRT practice prompt — not a verified question from a named employer.

The Runtime Theory Team2 min read
Learn it firstFenwick treesPro — members onlyThe lowest set bit decides how many elements each cell owns, which buys prefix sums and point updates in O(log n) with one array of n plus 1 cells and a loop short enough to hold in your head.

A strong answer

A sequential scan reads table pages in order while an index scan seeks through the index and then fetches scattered table rows, so the winner depends on selectivity — the fraction of rows the predicate keeps. When a query keeps 0.1% of a 10-million-row table, a few thousand index and heap page visits beat reading terabytes of pages. When it keeps 30 to 40%, chasing 3 million scattered heap pointers with random I/O loses to one ordered pass, and the planner correctly picks the scan even though an index exists. The crossover usually sits in the 5 to 10% range, shifting with row width, cache state, and storage.

An ordinary index scan also pays per-row extras that plans reveal: a heap fetch for every row whose columns are not in the index, plus a visibility check against concurrent versions. An index-only scan skips the heap fetch when all columns live in the index and the visibility map confirms the page is stable, which is why narrow projections over fresh statistics favor the index. Verify with EXPLAIN (ANALYZE, BUFFERS): compare estimated and actual rows at the scan node, read shared hits versus disk reads, and confirm whether the index path still visits the heap. Stale statistics are the common false signal — after a bulk load that doubles the table, estimates can be off by 10x until ANALYZE refreshes them.

Adding an index to force the index path is therefore a trade, not a fix: each new index adds a write on every insert, update, and delete plus permanent storage. When the workload genuinely needs a large fraction of rows, the scan is the efficient plan and the right answer is to accept it, batch the work, or narrow the query rather than index around it.

What the interviewer is listening for

  • Selectivity driving the choice, with the 5 to 10% crossover stated as a range, not a law.
  • Random heap fetches and visibility checks as the per-row tax on index scans.
  • Reading estimated versus actual rows and buffer counts to confirm the plan.
  • Stale statistics and non-indexable predicates as reasons a scan appears despite an index.

Common mistakes

  • Calling every sequential scan a missing-index problem.
  • Quoting plan cost units as milliseconds.
  • Recommending an index without checking heap fetches or write-path cost.
  • Running EXPLAIN ANALYZE without noting that it executes the statement.

This answer walks

Practice follow-ups

  1. 01Why can stale statistics produce a poor plan?
  2. 02What extra work can an ordinary index scan perform after locating a key?
  3. 03How do you compare estimated and actual row counts?

More interviews in this topic

easy · databasesWhy do databases use B+ Trees instead of Binary Search Trees or B-Trees (where internal nodes also store row payloads)? Explain the exact hardware and WAL.medium · databasesWhy should you use the Database Buffer Pool instead of relying solely on the Linux OS Page Cache (`mmap`), how does Clock-Sweep / LRU-K prevent Sequential.easy · databasesWhy is a Columnar Vectorized engine (DuckDB / ClickHouse / Snowflake over Parquet) 100x–1,000x faster than a Row-Oriented OLTP engine (PostgreSQL / MySQL) for.

Not started

Sign in to save your learning progress.

Sign in to save