You write what you want — SELECT … WHERE city = 'NYC' AND age > 30 — and the database decides how to get it. That decision is made by the query planner, and it’s the single biggest reason two queries that look identical can differ by a thousandfold in speed. The planner is cost-based: it enumerates the ways it could execute the query — read every row (a sequential scan), jump straight to matching rows through an index scan, or combine two indexes with a bitmap AND — estimates a cost for each using statistics about your data, and runs the cheapest. The counter-intuitive part, the thing that trips up most engineers: an index is not always faster. If a condition matches most of the table, chasing an index means thousands of scattered random reads, and a plain sequential scan wins. The deciding factor is selectivity — what fraction of rows a condition keeps. Change the selectivity of each predicate, toggle the indexes, and watch the planner flip its choice, with the cost of every candidate laid bare.
cost-based · seq scan vs index scan vs bitmap AND · selectivity decides · an index isn’t always a win
sequential scan
Read every row in the table in order. Cheap per row, but you touch all of them.
index scan
Use an index to jump to matching rows. Fast when few rows match; costly random reads when many do.
selectivity
The fraction of rows a condition keeps. Low selectivity (few rows) favours an index; high favours a scan.
bitmap AND
Read two indexes into bitmaps, intersect them, then fetch only the rows in both — great when each index is selective.
planner.js — estimate every plan, run the cheapest
Ready
The planner costs every way to run WHERE city='NYC' AND age>30 on 100,000 rows and picks the cheapest. Change how selective each predicate is, and toggle which indexes exist. Watch the winning plan flip.
—
chosen plan
—
est. cost
—
rows returned
How it works
A planner turns a declarative query into a physical plan by doing something almost mechanical: it enumerates candidate plans and prices each one, then keeps the minimum. The prices come from a cost model over table statistics — chiefly the row count and the selectivity of each predicate (estimated from histograms the database keeps on each column). The key asymmetry the model captures is that a sequential scan reads pages in order (cheap per row, but it reads them all), whereas an index scan does a random heap fetch for each match (expensive per row, but only for matching rows). So index cost grows with the number of matches while scan cost is fixed — and they cross over at some selectivity (often around a fifth to a third of the table). Below the crossover, the index wins; above it, the scan wins, which is why an index on a low-selectivity column is dead weight. When a query has two selective predicates each with an index, a third option appears: read both indexes into bitmaps, AND them, and fetch only the rows in the intersection — cheaper than either index alone. The planner doesn’t know your intent; it just prices these shapes against the current statistics and picks the cheapest, which is why keeping statistics fresh (running ANALYZE) matters so much — stale stats mean mispriced plans and sudden slowdowns.
1
Estimate selectivity
From column statistics, the planner estimates what fraction of rows each predicate keeps — the single most important input. The city match might keep 2% of rows; age over 30 might keep 60%.
2
Price each candidate plan
It costs a sequential scan (read all rows), an index scan per available index (random fetch of matching rows), and — if two selective indexes exist — a bitmap AND. Index cost grows with the number of matches.
3
Pick the minimum
The cheapest candidate wins. A selective predicate with an index gives a cheap index scan; a broad predicate makes its index worthless and a sequential scan wins instead.
✓
Why it flips
Change selectivity and the winner flips: two selective indexes make a bitmap AND cheapest; a non-selective predicate sends the planner back to a full scan. Stale statistics mispriced this — which is why ANALYZE matters.
Decision
minimum estimated cost
Key input
selectivity (row fraction)
Seq scan
fixed cost, reads all
Index scan
cost grows with matches
The code
# Cost-based planning (simplified), 100k-row table
N = 100_000
sel_city, sel_age = 0.02, 0.60 # from column statistics
seq_cost = N # read every row, in order
idx_city = sel_city * N * 4 # random heap fetch per match
idx_age = sel_age * N * 4
bitmap_and = 0.5*(sel_city+sel_age)*N + 2*(sel_city*sel_age)*N
plans = {'Seq Scan': seq_cost}
if has_index('city'): plans['Index Scan (city)'] = idx_city
if has_index('age'): plans['Index Scan (age)'] = idx_age
if has_index('city') and has_index('age'):
plans['Bitmap AND'] = bitmap_and
chosen = min(plans, key=plans.get) # run the cheapest
Quick check
1. A predicate matches 70% of the table. Even with an index on that column, why does the planner usually pick a sequential scan?
Index cost grows with the number of matches (each is a scattered random read), while a sequential scan is a fixed cost that reads pages in order. Past a crossover selectivity (often ~20–33%), the scan is cheaper — so an index on a low-selectivity column is dead weight.
2. What is the single most important input to the planner’s cost estimates?
Selectivity drives everything: it decides how many rows an index scan must fetch and therefore whether the index or a full scan is cheaper. It’s estimated from column statistics (histograms), which is why stale statistics lead to badly mispriced plans.
3. When does a bitmap AND of two indexes become the cheapest plan?
A bitmap AND reads both indexes into bitmaps, intersects them, and fetches only rows matching both conditions. When each predicate is selective, that intersection is tiny — cheaper than a single index scan (which would fetch all rows matching just one condition) and far cheaper than a full scan.
FAQ
What is a SQL query planner?
The part of a database that decides how to execute a SQL query. You write declarative SQL saying what you want; the planner figures out the physical steps — which indexes to use, join order, scan vs seek — by enumerating candidate plans, estimating a cost for each from statistics, and running the cheapest. It’s the main reason two logically identical queries can differ enormously in speed.
Why does the database sometimes ignore my index?
Because the planner estimated using it would be slower. An index scan uses random I/O and its cost grows with the number of matches, so if your condition matches a large fraction of rows (low selectivity), a sequential scan (ordered, one touch per row) wins. It’s priced and rejected, not ignored. Other real causes: stale statistics (run ANALYZE), a function/type mismatch on the indexed column, or a query returning most of the table.
What is selectivity in a query plan?
The fraction of rows a predicate keeps. A highly selective predicate (like a unique-ID equality) keeps very few rows and favours an index; a low-selectivity one (a boolean true for most rows) keeps most rows and favours a sequential scan. The planner estimates it from column statistics (histograms, distinct-value counts) and it’s the dominant factor in scan-vs-index choices. Bad selectivity estimates are the usual root cause of a suddenly slow query.
How do I read a query plan with EXPLAIN?
EXPLAIN shows the chosen plan tree — each node’s operation (Seq Scan, Index Scan, Bitmap Heap Scan, the join types) with estimated cost and rows. EXPLAIN ANALYZE runs it and adds real timing and real row counts. The key habit: compare estimated vs actual rows — a big mismatch means the statistics are wrong, which is usually why the plan is bad. Then fix the cause: ANALYZE to refresh stats, add/adjust an index, or rewrite the predicate so an index can be used.