Datastoresindexsequential scanselectivityplannerpages

An Index Scan Is Not Automatically Faster

The planner chooses a sequential scan over an index for good reasons: selectivity, table size, cache residency and the cost of random page access. Forcing the index because "indexes are fast" is the most confidently made wrong optimization in database work.

Follow the diagnosis

Frame the diagnosis

Performance work starts from a symptom and a signal — never from a resource dashboard.

Diagnostic question
The plan shows a sequential scan — is that the problem, or is it the correct choice?
Symptom
A query is slow and the plan says `Seq Scan`, which looks like an obvious smoking gun to anyone who has read that indexes make queries fast.
Signal
Selectivity — the fraction of the table the predicate actually matches — read together with table size and cache residency. Rows returned alone is the misleading signal: returning 200 rows can be selective on a billion-row table and completely unselective on a 300-row table.
Symptom→Signal→Measurement→Hypothesis→Evidence→Root Cause→Change→Validation→Regression Check

The planner is doing arithmetic you are not

An index scan is not a cheaper way to read rows. It is a *different* way, with a different cost curve. Reading through an index means descending the tree for each match, then usually jumping to the heap to fetch the row — random access, one page at a time, each potentially a separate storage read. A sequential scan reads pages in physical order, which storage and the operating system both prefetch well, and skips the tree entirely.

The crossover is selectivity. Below a few percent of the table, the index wins easily because it touches few pages. Above roughly a fifth of the table, the sequential scan usually wins, because the index path would touch nearly every heap page anyway plus the index pages, in random order, doing strictly more work. Between those, it depends on correlation between index order and physical row order, on whether the index covers the query, and on what is already in memory.

This is why the same query deserves different plans in different environments. On a laptop with a 500-row table, everything is in memory and a sequential scan is unbeatable. On production with 80 million rows and a cold cache, the index is decisive. A plan captured in the wrong environment is not evidence — it is a different question, answered correctly.

When each access path wins. Percentages are the shape of the trade-off, not thresholds to memorize.
SituationSequential scanIndex scanWhy
Predicate matches ~0.1% of rowsReads every pageWins clearlyTouches a handful of index and heap pages
Predicate matches ~30% of rowsUsually winsRandom-fetches most pages anywayIndex adds tree descents on top of nearly the same heap reads
Small table, fully cachedWinsOverhead without benefitNo I/O to save; sequential access has near-zero cost
Index covers all selected columnsReads the heapWins even at low selectivityIndex-only scan never touches the heap
Rows physically clustered by index orderCompetitiveWinsIndex fetches become near-sequential heap reads
Rows randomly distributed, cold cachePredictableCan be far worseEvery match is a separate random page read
Query also sorts by the indexed columnNeeds a sort stepOften winsIndex returns rows already ordered, no sort

The misdiagnosis, and the measurement that prevents it

Engine-specific · PostgreSQL 16 syntax. Every engine has an equivalent of "the predicate cannot use this index"; the specific rules about casts, functions and collations differ.

The failure mode is mechanical: see Seq Scan, add an index, ship it, and the query does not get faster — or gets faster in staging and not in production, which is worse because now there is a permanent write cost and a false belief. The planner had already considered that index and rejected it, and the rejection was probably correct.

The measurement that settles it takes one minute: count what the predicate actually matches, divide by the table size, and compare against what the planner estimated. If the planner's estimate is right and selectivity is high, the sequential scan is correct and the query needs a different shape — fewer rows requested, a narrower predicate, pagination, or precomputation. If the estimate is wrong, you are in The Slow Query Workflow's statistics case and the index is still not the fix.

There is a third case worth naming because it is common and quietly expensive: the predicate is selective, an index exists, and the planner still refuses it because the index cannot be used — a function applied to the column, a type mismatch forcing a cast, or a leading wildcard in a LIKE. The plan says sequential scan, the index exists, and everyone concludes the planner is broken. It is not; the predicate is simply not expressible through that index.

Misdiagnosis: the plan says Seq Scan, so add an index
1-- Plan showed:  Seq Scan on orders  (actual rows=6100000)
2CREATE INDEX orders_status_idx ON orders (status);
3 
4-- Re-run: planner still chooses Seq Scan. Why?
5SELECT count(*) FILTER (WHERE status = 'active') AS matching,
6       count(*) AS total
7FROM orders;
8 matching | total
9----------+----------
10  6100000 |  8600000     ← the predicate matches 71% of the table
11 
12-- The index was never going to help. It now costs a write
13-- on every insert and status update, permanently.
Correct: measure selectivity first, then pick the layer
1-- Same question, asked before touching the schema:
2--   71% selectivity  ->  a sequential scan is the right access path.
3--   The query is slow because it RETURNS six million rows.
4 
5-- The fix is at the query/API layer, not the index layer:
6SELECT id, customer_id, total_cents, created_at
7FROM orders
8WHERE status = 'active'
9  AND created_at > $1          -- keyset boundary, selective
10ORDER BY created_at, id
11LIMIT 100;                      -- bounded result
12 
13CREATE INDEX orders_active_created_idx
14  ON orders (created_at, id)
15  WHERE status = 'active';      -- partial: indexes only the hot subset

The first version treats the access path as the problem. The second recognizes that returning 71% of a table is the problem, bounds the result, and only then adds an index that serves the bounded query — a partial index over the hot subset, which is smaller and cheaper to maintain than one over every row.

What a scan actually costs, and when that changes

A sequential scan's cost is proportional to table size in pages, and it degrades gracefully: twice the data, roughly twice the time, with excellent prefetching. An index scan's cost is proportional to matching rows, plus a tree descent each, plus a heap fetch each unless the index covers the query. That second curve is far better at low selectivity and far worse at high selectivity, and it is much more sensitive to whether pages are in memory.

Cache residency is the variable that makes production disagree with staging. If the table fits in the buffer pool, "random access" costs a memory lookup and the index wins over a wider range. If it does not, every heap fetch may be a storage read, and the index's advantage collapses exactly when the table is large enough to matter. This is the same working-set argument as Disk and Storage: Latency, Throughput, IOPS and the fsync Tax, seen from the database side, and the depth is in The Buffer Pool.

The practical consequence for diagnosis: when a query flips from fast to slow with no code change and no data-shape change, suspect the working set outgrowing memory before suspecting the planner. The buffer hit ratio and shared read= counts in the plan tell you directly, and the fix is capacity or partitioning rather than anything in the query.

Same query, same plan, two weeks apart — nothing in the code changedILLUSTRATIVE
SignalValueWhat it tells youVerdict
Plan shapeIndex Scan (unchanged)The planner made the same decision both times. Not a plan flip.normal
Rows returned~2,400 (unchanged)Selectivity is stable. Not a data-distribution change.normal
Buffers: shared hit2,390 → 210Pages found in memory collapsed.suspect
Buffers: shared read10 → 2,190Nearly every heap fetch is now a storage read. The random-access cost became real.smoking gun
Table size48 GB → 71 GBThe working set outgrew the buffer pool. This is a capacity change, not a query change.suspect
Query duration p9911 ms → 780 msThe symptom. Identical plan, identical rows, 70× slower.normal

Key points

  • An index scan trades sequential page reads for random ones; it wins at low selectivity and loses at high selectivity.
  • Selectivity is the fraction of the table matched, not the number of rows returned — 200 rows is selective on a billion-row table and not on a small one.
  • A Seq Scan in the plan is frequently the correct choice; the planner already considered your index and rejected it.
  • Covering (index-only) scans and index-ordered clustering both widen the range where the index wins.
  • A query can slow 70× with no plan change when the working set outgrows memory — that is a capacity signal, not a query signal.

Follow the diagnosis

The causal chain, hop by hop — and the readings that invite the wrong conclusion.

  1. 1
    Plan → suspicion: Seq Scan on orders, 6.1 M rows, 2.3 s — reads as an obvious missing index.
  2. 2
    Selectivity → reality: the predicate matches 71% of the table, so an index scan would random-fetch nearly every heap page plus index pages.
  3. 3
    Planner → verdict: the sequential scan is the cheaper path and the planner chose correctly; the index would be rejected even if it existed.
  4. 4
    Query → root cause: the query returns six million rows to the application, so the cost is the result size, not the access path.
  5. 5
    Root cause → layer: the fix is bounding the result (keyset pagination, a narrower predicate), then a partial index sized to the bounded query.
What this evidence makes people conclude — wrongly
  • • "Seq Scan means a missing index." It usually means the predicate is not selective enough for an index to pay off.
  • • "The index exists, so the planner should use it." A cast, a function on the column, or a leading wildcard can make the predicate unusable through that index.
  • • "It was fast in staging with the same plan." Staging fits in memory. Random access is cheap until it is not.
  • • "Adding the index is harmless if the planner ignores it." It is not free: every insert and update maintains it forever.
  • • "More rows returned means we need a better index." Returning six million rows is the problem regardless of how they are found.

Measure, fix, validate

An optimization is not finished until the metric that motivated it has moved.

How to measure it
  • • Selectivity directly: `count(*) FILTER (WHERE <predicate>)` over `count(*)`, on production-shaped data.
  • • Estimated versus actual rows at the scan node, to separate "the scan is right" from "the estimate is wrong".
  • • `Buffers: shared hit` versus `shared read` in the plan — the ratio that decides how expensive random access actually is.
  • • Table and index size against available buffer pool memory, tracked over time so the crossover is predicted rather than discovered.
  • • Write latency on the table before and after any index addition, since that cost is permanent.
What actually fixes it
  • • Bound the result first: keyset pagination, narrower predicates, aggregate in the database instead of shipping rows ([[pagination]] covers the contract side).
  • • Where a selective predicate genuinely lacks support, add a composite index ordered to serve filter and sort together — or a partial index over the hot subset, which is smaller and cheaper to maintain.
  • • Make the predicate index-usable: match types to avoid casts, index the expression if a function is unavoidable, avoid leading wildcards.
  • • Consider a covering index when the query selects few columns, converting heap fetches into an index-only scan.
  • • When the working set has outgrown memory, treat it as capacity: more RAM, or partition the hot data ([[partitioning-and-sharding]]).
How you know it worked
  • • Confirm the plan node actually changed — not merely that the timing improved on a warm cache during the test.
  • • Re-run cold: a second execution reading from memory proves nothing about the first execution of the day.
  • • Measure insert and update latency on the table after adding any index, and keep the number next to the read improvement.
  • • Compare endpoint p99 in production over a window matching the baseline, since a faster access path on a non-critical query moves nothing.
What it costs
  • • Every index slows writes and consumes storage and memory that the buffer pool would otherwise use for data.
  • • Partial indexes are cheaper but only serve queries whose predicate matches the index condition — a slightly different query silently falls back to a scan.
  • • Covering indexes duplicate column data, which can make the index large enough to lose the memory it was trying to save.
  • • Rewriting to keyset pagination removes random page access and also removes the ability to jump to page 400 ([[pagination]] states that trade-off in contract terms).
Stop it coming back
  • • Track index size and table size against buffer pool capacity, with an alert before the working set crosses it.
  • • Monitor unused indexes and remove them; each one is a permanent write tax paid for a decision nobody remembers.
  • • Assert plan shape in CI for the handful of statements on the critical path, against a production-shaped dataset.

Accuracy

Performance numbers are conditional. These are the conditions.

What these numbers depend on
  • DATABASE-SPECIFICSelectivity crossover points, clustering behavior and index-only-scan rules differ by engine. PostgreSQL needs a visibility-map check for index-only scans; InnoDB clusters the table by primary key, which changes the arithmetic entirely.
  • ILLUSTRATIVEThe percentages and the two-week comparison are teaching shapes. Real crossover depends on row width, page size, storage characteristics and cache state.
  • ENVIRONMENT-SPECIFICThe same query on the same schema deserves different plans on a laptop and in production, because cache residency and table size differ.

Misconceptions

Claim
“Indexes make queries faster.”
Reality
Indexes make *selective* queries faster and every write slower. At 30% selectivity an index scan does strictly more work than a sequential scan.
Claim
“A sequential scan on a big table is always bad.”
Reality
It is the optimal path when the predicate is unselective, and it degrades linearly with excellent prefetching. Aggregations over most of a table should scan.
Claim
“If the query got faster after adding the index, the index was the fix.”
Reality
The second run reads a warm cache. Compare cold executions and check whether the plan node actually changed.

Where the depth lives

Operating systems
Sequential versus random I/O and readahead

The reason a scan degrades gracefully and an index scan does not is prefetching: the kernel and the storage device both predict sequential access and cannot predict random access.

← The Slow Query WorkflowWhen the Join Strategy Is the Bottleneck →