Reference · Cheat sheet
Lesson 0006 distilled — the node line, ANALYZE vs plain, and the one check that matters. Built to print.
cost = startup..total, arbitrary units (seq page = 1.0); includes children. For comparing plans, not seconds.
rows = estimated rows emitted (after filter), not scanned.
width = est. bytes/row. Read the tree bottom-up.
Plain EXPLAIN: estimates only, doesn't run.
EXPLAIN ANALYZE: executes the query; shows actual time, actual rows, loops next to estimates.
DML danger: EXPLAIN ANALYZE UPDATE… really updates. Use BEGIN; … ; ROLLBACK;.
Check estimated rows vs actual rows. Big gap → planner flew blind → bad join/scan choices cascade upward.
Usual fix: estimates come from stats gathered by ANALYZE (autovacuum runs it too). Stale stats → ANALYZE thetable;.
hit = from cache (fast). read = from disk (slow — the number that hurts on a cold cache).
Seq Scan — whole heap (fine for a big fraction of rows).
Index Scan — index → heap fetch (two-step, 0003).
Index Only Scan — skips heap if all-visible; watch Heap Fetches:.
Bitmap Heap Scan — index → bitmap → heap in physical order (many scattered matches).
| Aspect | MySQL | Postgres |
|---|---|---|
| Shape | row per table | node tree |
| Cost | rows×filtered | page-fetch units |
| Cache view | — | BUFFERS |
| Top check | rows vs actual | est vs actual rows |