Query Planner — Complete Guide
Query Planner — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of PostgreSQL Tutorial on Toolliyo Academy.
On this page
PostgreSQL Tutorial · Lesson 28 of 100
Query Planner
SQL → Advanced
SQL · 1 — Queries · ~6 min · PostgreSQL — Indexing & Performance
What is this?
The planner estimates costs using statistics (pg_stats), indexes, and GUC settings to pick join methods, scan types, and order. It optimizes for estimated cost, not your intuition.
Why should you care?
PostgresVerse report suddenly uses Nested Loop on 1M rows because stats outdated — planner thought only 10 rows matched.
See it live — copy this example
Run in pgAdmin or psql.
SET enable_hashjoin = on;
EXPLAIN
SELECT *
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.customer_id = 77;What happened?
- EXPLAIN without ANALYZE shows planned node types — Hash Join, Merge Join, or Nested Loop.
- enable_hashjoin is a session knob to test alternate plans in dev.
Practice next
- Run EXPLAIN on a two-table JOIN.
- Note join type and relative costs.
- Run ANALYZE orders; ANALYZE order_items; re-EXPLAIN.
- Increase default_statistics_target on filtered column and ANALYZE.
- Compare Merge Join vs Hash Join on sorted vs unsorted inputs.
Remember
Planner uses stats and cost constants. Stale stats cause wrong join and scan choices. Session GUCs help experiment, not fix production blindly.
PostgresVerse stats incident
Weekly ANALYZE job failed; planner picked nested loop until ops reran analyze.
Outcome: Alert on last_analyze age prevents repeat.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
