Lesson 0008 · The Planner

Why the planner "ignores" your index

It never ignores it — the planner is cost-based, so an index is used only when it's estimated cheaper than the alternatives. Learn when a Seq Scan is the right answer, and the handful of times the estimate is genuinely wrong.

~11 minWarm-up recall + quiz + MySQL contrastCheat sheet: here

"Why isn't Postgres using my index?" is the wrong question, and fixing the wrong question wastes hours. Postgres has a cost-based planner: for each query it estimates the cost of every candidate plan — sequential scan, index scan, bitmap scan — and runs the cheapest. Your index is never "ignored." It simply lost a cost comparison. The right question is: did it lose fairly?

Why a Seq Scan can genuinely beat an index

Recall from 0003 that an index scan is a two-step: for each match, jump from the index into the heap. Those heap jumps are random I/O, and Postgres prices random much higher than sequential:

— PostgreSQL Docs: seq_page_cost "The default is 1.0." / random_page_cost "the cost of a non-sequentially-fetched disk page. The default is 4.0."

So if your WHERE matches a large fraction of the table, an index scan means thousands of expensive random jumps, while a Seq Scan reads the whole heap in one cheap sequential pass. The planner correctly picks the sweep. The extreme, from the docs: — PostgreSQL Docs: "on a table that only occupies one disk page, you'll nearly always get a sequential scan plan whether indexes are available or not… there's no value in expending additional page reads to look at an index."

When the estimate is genuinely wrong — the real bugs

Now the cases worth fixing. These are where the planner would choose your index if it saw the truth. Most map straight onto MySQL lesson 0004.

1 · Stale statistics → wrong row estimate

The planner estimates selectivity from table statistics. If those are stale, it can think "most of the table matches" when really 5 rows do — and pick a Seq Scan for a highly-selective query. This is the 0006 skill in action: a big estimated-vs-actual rows gap is the tell. Fix: ANALYZE thetable; (and remember autovacuum runs ANALYZE — a table that outran autovacuum can have bad stats).

2 · A function or expression hides the column

WHERE lower(email) = 'a@x.com' cannot use a plain index on email — the planner only has an index on the raw column, not on lower(email). Same trap as MySQL's "wrapped column." Fix: index the expression: CREATE INDEX ON t (lower(email));.

3 · Type mismatch & leading wildcards

A comparison that forces a type coercion, or a LIKE '%foo' with a leading wildcard, can't use a normal B-tree — identical to MySQL. Fix the type, or use a suitable index type (e.g. trigram) for wildcard search.

4 · The cost model doesn't match your hardware

This one is Postgres-specific and the most common silent cause on modern servers. The default random_page_cost = 4.0 assumes spinning disks where random I/O is ~4× sequential. On SSDs, or a database mostly cached in RAM, random access is nearly free — so 4.0 makes every index scan look artificially expensive and the planner over-prefers Seq Scans. — PostgreSQL Docs: "setting them equal makes sense if the database is entirely cached in RAM, since in that case there is no penalty for touching pages out of sequence." Common fix: lower random_page_cost toward 1.1, and set effective_cache_size to reflect real RAM — a higher value tips the planner toward indexes. — PostgreSQL Docs: effective_cache_size "a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used." (default 4GB)

The diagnostic loop

Postgres vs MySQL — "index ignored"

Check yourself

From memory. Two items reach back on purpose.

CREATE TABLE t (id int PRIMARY KEY, k int);
INSERT INTO t SELECT g, g % 1000 FROM generate_series(1,1000000) g;
CREATE INDEX ON t (k);
ANALYZE t;

EXPLAIN (ANALYZE) SELECT * FROM t WHERE k = 7;      -- selective (~1000 rows): index/bitmap
EXPLAIN (ANALYZE) SELECT * FROM t WHERE k < 900;    -- ~90% of rows: Seq Scan (correct)

SET enable_seqscan = off;                           -- diagnostic only
EXPLAIN (ANALYZE) SELECT * FROM t WHERE k < 900;    -- forced index plan — compare the cost/time
RESET enable_seqscan;
SET random_page_cost = 1.1;                          -- SSD-like; re-run selective queries