Lesson 0006 · The Planner
You already read MySQL plans. Postgres shows a tree of plan nodes with costs and row estimates — and one comparison (estimated vs actual rows) tells you more than all the rest.
Closed book. Both show up the moment you read a real plan.
In an Index Only Scan, a non-zero Heap Fetches count means:
Postgres's default isolation level is:
In MySQL you read a plan as a row per table: access type, key, rows, filtered, Extra (that was MySQL lesson 0003). Postgres shows something different: a tree of plan nodes, each annotated with a cost and a row estimate. You read it inside-out / bottom-up — the most-indented nodes run first and feed their parents.
Three things to internalize about that line:
startup..total;
the units are conventionally "one sequential page fetch = 1.0," everything else relative.
They are for comparing plans, not seconds.
— PostgreSQL Docs: "The costs are measured in arbitrary units determined by the planner's cost parameters… seq_page_cost is conventionally set to 1.0."
And critically: "the cost of an upper-level node includes the cost of all its child nodes."rows is an estimate, and it's rows emitted, not
scanned — after the node's own WHERE filtering.
— PostgreSQL Docs: "the rows value… is not the number of rows processed or scanned by the plan node, but rather the number emitted by the node… less than the number scanned, as a result of filtering."width is the estimated average row size in bytes.Plain EXPLAIN only estimates — it does not run the query. Add
ANALYZE and Postgres actually executes it, then shows the real
numbers next to the estimates.
— PostgreSQL Docs: "With this option, EXPLAIN actually executes the query, and then displays the true row counts and true run time accumulated within each plan node, along with the same estimates that a plain EXPLAIN shows."
EXPLAIN ANALYZE UPDATE … performs the UPDATE. The results
are discarded but the side effects are real.
— PostgreSQL Docs: "because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual… If you want to analyze a data-modifying query without changing your tables, you can roll the command back."
So the safe habit for DML is BEGIN; EXPLAIN ANALYZE UPDATE …; ROLLBACK;.
When you read an EXPLAIN ANALYZE, the first thing to check is whether the
estimated rows is close to the actual rows.
— PostgreSQL Docs: "The thing that's usually most important to look for is whether the estimated row counts are reasonably close to reality."
A node that estimates rows=5 but actually emits rows=50000 means
the planner was flying blind — and a wrong row estimate cascades into wrong join
and scan choices above it. Those estimates come from table statistics gathered by
ANALYZE — and remember from lesson 0002
that autovacuum runs ANALYZE too.
— PostgreSQL Docs: autovacuum automates "VACUUM and ANALYZE commands."
So a wildly-off estimate often just means stale statistics → run
ANALYZE thetable; and look again.
Add BUFFERS (implicitly on whenever you use ANALYZE in recent
versions) to see cache vs disk:
— PostgreSQL Docs: "The ANALYZE option implicitly enables the BUFFERS option," which shows "buffers hit, read, dirtied, and written."
Two plans with the same cost can behave very differently at 3 a.m. when the
cache is cold: read= is the number that hurts. This is your window into the
buffer pool — the Postgres cousin of InnoDB's.
Everything from the storage lessons shows up here by name:
Seq Scan — read the whole heap. Not always bad: for a
large fraction of the table it beats an index (next lesson's topic).Index Scan — the two-step from 0003: walk the index, then heap-fetch each match.Index Only Scan — skips the heap if pages are
all-visible; watch its Heap Fetches: line (you just recalled this).Bitmap Heap Scan — build a bitmap of matching tuple
locations from an index, then read the heap in physical order. Postgres's answer for
"many matches, scattered" — a middle gear between index and seq scan.| Question | MySQL / InnoDB | PostgreSQL |
|---|---|---|
| Plan shape | One row per table accessed | A tree of plan nodes (read bottom-up) |
| Cost readout | rows × filtered heuristics | cost=startup..total (page-fetch units) |
| "How many rows" | rows examined estimate | rows = estimated rows emitted |
| Actually run it | EXPLAIN ANALYZE (8.0+) | EXPLAIN ANALYZE (executes; wrap DML) |
| Cache visibility | — | BUFFERS: shared hit vs read |
| Top diagnostic | rows × filtered vs actual | estimated vs actual rows |
From memory. Two items reach back on purpose.
In (cost=0.00..18.50 rows=5 width=36), the rows=5 is:
Reading EXPLAIN ANALYZE, the single most useful check is:
Estimated rows are wildly off from actual. The usual first fix is:
EXPLAIN ANALYZE UPDATE t SET … is dangerous because it:
In Buffers: shared hit=36 read=6, the number that signals slow I/O is: (recall the storage arc)
A lab to run later. It shows estimate-vs-actual and the safe-DML trick:
CREATE TABLE t (id int PRIMARY KEY, k int);
INSERT INTO t SELECT g, g % 50 FROM generate_series(1,100000) g;
EXPLAIN SELECT * FROM t WHERE k = 7; -- estimates only, no run
EXPLAIN (ANALYZE) SELECT * FROM t WHERE k = 7; -- compare rows vs actual rows; note Buffers
ANALYZE t; -- refresh stats
EXPLAIN (ANALYZE) SELECT * FROM t WHERE k = 7; -- estimate should tighten
BEGIN; EXPLAIN ANALYZE UPDATE t SET k = k + 1 WHERE id = 1; ROLLBACK; -- inspect DML safelyBring a plan where estimated and actual diverge and we'll work out why together.
PostgreSQL Docs — 14.1 Using EXPLAIN (the whole node-by-node walkthrough, cost units, ANALYZE, BUFFERS, and the estimate-vs-reality advice). Keep this page open the first few times you read a real plan.