PostgreSQL Deep Dive
by Sergey Okinchuk
Building Indexes SafelyReading an EXPLAIN Plan

Chapter Nine · The Planner and EXPLAIN

The Planner and EXPLAIN

Postgres does not follow rules when it runs a query. It prices every plan it can think of and executes the cheapest one, and the price is computed from statistics that describe the table as it was the last time anyone looked. Six topics on reading that arithmetic — and on the first Saturday of the month, when the arithmetic is wrong by five orders of magnitude.

6 topics

Chapter 8 built Cartwheel's indexes. This chapter is about the component that decides whether to use them, and it works differently from what most engineers assume. There is no rule saying "an equality predicate on an indexed column uses the index". There is an arithmetic: for every way of getting the rows, Postgres estimates how many rows come out and multiplies that by a set of per-page and per-tuple constants, then runs whichever plan came out cheapest. Change the estimate and the plan changes. That is the whole mechanism, and once it is in your head, "the planner ignored my index" stops being a complaint and becomes a question with a findable answer.

Which matters here because the estimate is the part that breaks. Statistics are a 30,000-row sample of a 40-million-row table, refreshed when autovacuum decides enough has changed. A histogram describes the data that existed when it was built, and says almost nothing about rows inserted since. On the first Saturday of each month, a query that has run in 40 ms for four weeks runs in 6 seconds, for about twenty minutes, and then recovers without anyone touching it. Nothing was deployed. No host is short of CPU. Restarting the API makes it look fixed, which is why it survived three months of investigation. The plan is the evidence, and by the end of this chapter you will be able to read it off the page.

The six topics build in one direction. First the shape of a plan and the four numbers on every node, then EXPLAIN (ANALYZE) and the estimated-versus-actual comparison that turns reading into diagnosing. Then the five ways Postgres gets rows off disk and the three ways it joins them, because those are the choices the estimate is buying. Then the statistics themselves — what ANALYZE collects, how a range predicate is priced from a histogram, and what happens to a value that lies past the last bucket. The final topic is the diagnostic procedure, and it ends by naming Cartwheel's Saturday, showing the two plans side by side, and fixing it.

There are no rules, only prices — and the price starts from a sample
Statisticsthe table as it was when someone last looked
→
Row estimatehow many rows come out
→
Costrows × per-page and per-tuple constants
→
The cheapest plan runs40 ms · or 6 seconds on a Saturday

Topics in This Chapter

Topic 44
Reading an EXPLAIN Plan
A plan is a tree read from the inside out, and every node carries startup cost, total cost, an estimated row count and an estimated row width. What those four numbers mean, why a LIMIT can select an entirely different plan, and the difference between an Index Cond and a Filter.
Plan Reading
Topic 45
EXPLAIN (ANALYZE, BUFFERS)
Running the query and putting reality next to the prediction, with buffer counts included by default since 18. The loops multiplication trap, what a disk spill looks like, and why ANALYZE on a DELETE deletes.
Plan Diagnostics
Topic 46
Scan Nodes
Five ways to get rows out of a table, and the estimated row count that decides between them. Sequential, index, index-only, the bitmap pair that reads each page once, and the parallel forms with a Gather on top.
Access Paths
Topic 47
Join Strategies
Three join algorithms, each optimal in a different regime, plus the Memoize cache that changed when a nested loop is reasonable. Why a nested loop is the one that turns a small estimating error into a six-second query.
Join Methods
Topic 48
Statistics and Row Estimates
Where the row counts come from: a random sample, a most-common-values list, a hundred-bucket histogram and a physical correlation figure, all readable in pg_stats. Independence assumptions, extended statistics, and the ascending-key problem.
Cost Model
Topic 49
When the Planner Gets It Wrong
The diagnostic procedure — find the lowest node where estimate and reality diverge, and fix that node's statistics rather than the plan above it. Ends with the two Cartwheel plans, the mechanism behind the Saturday slowdown, and the fix.
Troubleshooting