How the Planner Chooses

Understand why PostgreSQL picks one plan over another — join strategies, stale statistics, sort spills, and how LIMIT rewrites the whole approach — on a real multi-table stand.

Reading a plan tells you what Postgres did. This course explains why it did that. We build a stand with four tables — users, orders, products, and line_items — and use it to provoke every major planning decision the planner makes. We watch a Hash Join flip to a Nested Loop the moment an index appears. We insert rows without running ANALYZE and watch the estimate diverge from reality by a factor of two hundred thousand, then fix it. We shrink work_mem until a sort spills to disk and time the difference when it stops spilling. We add a LIMIT to a query and see the planner abandon its sort entirely in favour of a backward index scan. Every effect is visible in the plan output; the course teaches you to connect what you read to the decision the planner made.

What you'll build

• Explain why Nested Loop, Hash Join, and Merge Join each suit a different input size
• Show how a new index changes a join strategy without changing the query
• Reproduce stale-statistics misestimation and fix it with ANALYZE
• Demonstrate sort spill with a small work_mem and measure the improvement when memory is sufficient
• Explain why LIMIT can eliminate a Sort node entirely when a matching index exists
• Use enable_hashjoin / enable_nestloop as a diagnostic lens, not a production fix

Contents

1. A database to reason about plans on
2. Wait for it to be ready
3. One command to bring it up
4. Start the server
5. A clean slate when you need one
6. A short way into the database
7. The users table
8. The orders table
9. The products table
10. The line_items table
11. Create the tables
12. Seed the users, repeatably
13. Seed the products
14. Seed a million orders
15. Seed three million line items
16. Load the data
17. Fresh statistics, single-threaded plans
18. Confirm what we are sitting on
19. Nested Loop: a few outer rows, one lookup each
20. Reading a Nested Loop plan
21. Hash Join: one big side, one small side
22. Reading a Hash Join plan
23. Add an index — same query, new plan
24. The plan flips to Nested Loop
25. Merge Join as a diagnostic tool
26. Reading a Merge Join plan
27. What happens when work_mem runs out
28. Sort spill in the plan
29. The same sort with enough memory
30. Quicksort in memory
31. Insert rows the planner does not know about yet
32. A query the planner cannot estimate correctly
33. The estimate is off by two hundred thousand
34. Refresh the statistics
35. The estimate matches reality now
36. Sorting the whole table
37. A full sort plan
38. Ask for only the top ten
39. LIMIT rewrites the cost
40. Index a column we sort by
41. LIMIT + sorted index = no sort node
42. Index Scan Backward — no sort anywhere
43. Connecting the dots