Cheat sheet — Why the planner ignores your index

Lesson 0008 distilled — it's cost-based; the index only wins when it's cheaper. Built to print.

The reframe

The planner is cost-based: it estimates every candidate (seq / index / bitmap) and runs the cheapest. Your index isn't ignored — it lost a cost comparison. Ask: did it lose fairly?

Selectivity picks the gear

Few rows → Index Scan.

Medium / scattered → Bitmap Heap Scan (collect locations, read heap in order).

Large fraction → Seq Scan (one sweep beats many random jumps). This is correct, not broken.

The cost knobs

seq_page_cost = 1.0; random_page_cost = 4.0 (default = spinning disk). Index heap jumps are random I/O → priced 4×.

SSD / cached: lower random_page_cost → ~1.1, raise effective_cache_size (default 4GB) → indexes win.

The real bugs (fix these)

1 · Stale stats → bad estimate → ANALYZE (the est-vs-actual gap is the tell, 0006).

2 · Wrapped column lower(email) → expression index.

3 · Type mismatch / leading LIKE '%x' → fix predicate / special index.

4 · Cost model ≠ hardware → random_page_cost / effective_cache_size.

Diagnose, don't guess

SET enable_seqscan = off; then re-EXPLAIN ANALYZE to see what the index plan would cost. Diagnostic only — never leave it off in prod.

Loop: est≈actual? no→ANALYZE. Selective? no→seq is right. Still not indexed→tune costs / expression index.

Postgres vs MySQL (0004)