index scan cost
:
Lesson 07 · The diagnosis
Why your query is slow: thinking like the planner
You now hold every piece: pages and their thousand-fold cost cliff (L1), B-trees and what they can and can't seek (L2), buffer pools and working sets, MVCC's dead-row tax (L5). The last piece is the decision-maker. The query planner takes your SQL: which says what, never how: enumerates physical strategies, prices each in estimated page reads and CPU, and runs the cheapest estimate. Read that twice: the planner is not an oracle, it's a cost model running on statistics. Nearly every "why is this slow?!" resolves to one of two findings: the planner chose well and the honest cost is just high, or the planner chose badly because its statistics or your SQL misled it. Learn to tell them apart:
The planner's central trade: index vs sequential scan
Selectivity decides everything
Table: orders, 10M rows, 125k pages on disk. Query: WHERE status = ?, index available on status. Drag the slider: what fraction of rows match?: and watch the planner re-price both strategies and flip.
1% of rows match
seq scan cost
:
planner picks
:
:
The counterintuitive lesson the slider teaches: an index is not always faster, and the planner ignoring your index is usually the planner being right. An index scan does a random page read per matching row-group (the B-tree hands back row addresses scattered across the heap); a sequential scan reads every page once, in order, at the fast end of Lesson 1's ladder. Past roughly 5–15% selectivity, scattered random reads cost more than reading everything. This single trade explains the two most common planner "mysteries": "it won't use my index" (your predicate matches too much) and "it used the index and it's still slow" (the predicate matched more than the statistics predicted: stale stats, the autopsy's exhibit C).
The autopsy room: four ways to kill an index
Each query looks innocent. Each defeats a perfectly good index.
The table has an index on every referenced column. Examine each exhibit, then reveal the mechanism and the fix.
:
Reading EXPLAIN like a pathologist
EXPLAIN ANALYZE SELECT ... WHERE customer_id = 42 ORDER BY created_at LIMIT 20;
Index Scan using idx_cust_created on orders
(cost=0.43..91.2 rows=20) ← the ESTIMATE
(actual time=0.1..3.2 rows=20 loops=1) ← the TRUTH
Buffers: shared hit=24 read=2 ← Lesson 1, itemized
Three habits cover 90% of diagnoses. (1) Compare estimated rows to actual rows: a 100× mismatch means the planner planned a different query than the one that ran; fix the statistics (ANALYZE) or the unestimatable predicate before touching anything else. (2) Find the node where actual time concentrates: the slow query is usually one slow node wearing a big plan as camouflage. (3) Read Buffers: hit is RAM, read is the disk ladder; a query that's "sometimes fast, sometimes slow" with identical plans is almost always a cache-residency story (Lesson 1), not a planner story. And the composite-index rule that resolves half of all index questions: an index on (a, b, c) is a phone book sorted by a-then-b-then-c: it serves a, a,b, a,b,c, and a, ORDER BY b, but is nearly useless for b alone (you can't scan a phone book by first name). Equality columns first, then the range or sort column; the index that matches your query's shape exactly can even skip the sort entirely: the LIMIT 20 above never sorted anything, it just walked 20 leaf entries and stopped.
The full picture, end to end: a query's latency = plan quality (this lesson) × structure traversal (L2/L3) × page residency (L1) × version garbage waded through (L5) × locks/aborts encountered (L6): and every write's durability rides on L4. When something is slow, walk the stack in that order: EXPLAIN first (is the plan sane? do estimates match actuals?), then Buffers (RAM or disk?), then table health (bloat? stats age?), then contention. The engine is not mysterious. It's just physics with a cost model on top: which is what this course was for.
Check yourself
1. "The planner refuses to use my index on country for WHERE country='FI': 40% of our users are Finnish." Is the planner broken?
The slider's lesson in the wild. Selectivity, not the existence of an index, decides the plan. The genuinely useful escalations: covering indexes (no heap visits at all → the random-read penalty vanishes), partial indexes (index only the rare, queried slice), or accepting the seq scan: often the honest answer for analytics-shaped predicates on OLTP tables.
2. A query was fast for a year, then gradually became slow. EXPLAIN ANALYZE shows: estimated rows=200, actual rows=1,900,000. No code changed. What happened, and what's the fix?
Exhibit C's disease. "Slow with no code change" almost always means the DATA changed: and the estimate/actual gap is the smoking gun that separates "planner misled" from "cost is honestly high." A 10,000× row misestimate cascades: wrong scan type, wrong join algorithm, wrong memory grant. One ANALYZE often un-slows a system that's been limping for weeks.
3. ORM-generated query: WHERE date_trunc('day', created_at) = '2026-07-19', with an index on created_at. Slow. Why, and what's the portable rewrite?
The single most common index-killer in ORM codebases. A B-tree can only seek on what it sorted; f(column) is a different, unsorted value. The range rewrite expresses the identical question in the index's native language (Lesson 2's leaf-chain range scan). The alternative: a functional index ON date_trunc('day', created_at): works but must anticipate every function; naked-column predicates stay general. Habit: keep columns bare, transform the constants.