Reading EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows the plan the optimiser chose and its estimates, without running the query. EXPLAIN ANALYZE actually runs it and adds real timings and row counts (careful: it really executes INSERT/UPDATE/DELETE — wrap those in BEGIN … ROLLBACK). Add BUFFERS to see how much data was read.

explain (analyze, buffers)
select * from orders where customer_id = 42 order by ordered_on desc limit 10;

What to look for:

• Scan type — Seq Scan reads the whole table; Index Scan / Index Only Scan / Bitmap Heap Scan use an index. A sequential scan is fine for small tables or when most rows match.
• Estimated vs actual rows — a large mismatch means stale statistics; run ANALYZE table_name.
• Join method — Nested Loop (good when one side is small), Hash Join, Merge Join.
• Expensive nodes — Sort spilling to disk, or a node whose actual time dominates the total.
• cost=start..total — the optimiser's unit-less estimate; compare plans with it rather than reading it as milliseconds.

Plans are read from the innermost (most indented) node outward.