Reading PostgreSQL Query Plans

Read the plan from the inside out

EXPLAIN (ANALYZE, BUFFERS) prints a tree. Execution starts at the deepest node and bubbles upward, so read it bottom-up even though it prints top-down.

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.PostId, p.Title
FROM BlogPost p
JOIN PostTag pt ON pt.PostId = p.PostId
WHERE pt.TagId = 3 AND p.Published = TRUE
ORDER BY p.CreatedOn DESC
LIMIT 10;

The numbers that matter

• actual time - real milliseconds. cost is a unitless guess; ignore it.
• rows vs actual rows - a large gap means the statistics are stale.
• loops - the per-row cost is actual time x loops, which is easy to misread.
• Buffers: shared read - pages fetched from disk rather than cache.

Three shapes that mean trouble

1. Seq Scan on a large table with a selective filter. A missing index, or a predicate written so the index cannot be used.
2. Nested Loop with thousands of loops. The planner expected a handful of rows and got thousands; run ANALYZE.
3. Sort spilling to disk. Visible as Sort Method: external merge. Either raise work_mem for that session or index the sort order.

A plan is a hypothesis about your data. When the plan is wrong, the fix is usually better statistics, not a bigger machine.

Making it a habit

Add a LogSlowQuery hook in development and print the plan for anything over 200 ms. You will find the problems long before a user does.