Postgres in depth · Chapter 50 of 65
How the planner thinks
By the end of this chapter you can read a query plan from the inside out, say why Postgres chose each scan and join, check its guesses with EXPLAIN (ANALYZE, BUFFERS), and write conditions an index can use. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Inside Postgres
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Sana's error report takes a second, the same report for Arjun takes a millisecond, and the only difference is how the date is written. SQL says what you want, never how to get it: a part of Postgres called the planner decides that, for every query, by estimating the cost of many possible plans and running the cheapest. Chapters 47 to 49 built indexes; this chapter shows how the planner decides whether to use them.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
sargable.sql: let the index work
Sana needs the error events of 1 May 2026, India time. Her first try (in the starter file) is correct, but it reads all 200,000 rows. The Check builds lesson_events as the lesson does, with an index on happened_at.
Rewrite it so it returns the same rows and Postgres can use that index. Return three columns: id, student_id and happened_at. Any order.
Careful: an index on happened_at cannot help a condition on an expression of happened_at; compare the column itself.
- Task 2
latest-events.sql: stop early
Aarav Shah's profile shows his 5 latest lesson events: when each happened, its event_type, and the title of its lesson. Write it as one query, finding Aarav by name.
Return three columns: happened_at, event_type and title, newest first (5 rows).
Afterwards the plan of your query must not read all of lesson_events.
- Task 3
merge-join.sql: the third join method
Arjun's lesson report counts events per lesson title (the query is in the starter file); Postgres picks a hash join for it. Priya wants the class to see a merge join on the same query.
For this session only, switch off the two other join methods, then end the file with EXPLAIN of the report. Afterwards the plan must join the two tables with a Merge Join.
Careful: these planner settings are for experiments; SET changes them for your session only.
- Task 4 · interview question
Interview: seq-cost.sql, cost a Seq Scan by hand
"How does Postgres put a number on a sequential scan?" Work out the total cost of the plain Seq Scan (no parallel workers) of the query in the starter file, which has two comparisons in its WHERE, the way the planner does.
Return one row with three columns: relpages and reltuples (lesson_events's size as pg_class records it) and total_cost: one seq_page_cost per page, plus, per row, one cpu_tuple_cost and one cpu_operator_cost for each of the two comparisons.
Careful: read every cost setting from the server; never type the numbers.
What you will learn
- EXPLAIN: the plan tree, costs, estimated rows and width.
- The cost model: page and CPU costs, and how a Seq Scan is priced.
- EXPLAIN (ANALYZE, BUFFERS): actual rows, loops, time and pages.
- Scan types: Seq, Index, Index Only and Bitmap scans, and when each wins.
- Join types: nested loop, hash join, merge join.
More in Inside Postgres
Build "How the planner thinks" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.