1.4 Planner: Which Path to Take

By the time 1.3 ends, the rewriter has let the Query tree through. Views have been expanded, RLS policies have been wedged into WHERE clauses, and missing INSERT columns have been filled with defaults. The starting point of this chapter is one fact: that Query tree now has "what to do" fully decided, but "how to do it" not even by a single character. The stage that fills in that gap is the planner.

The planner's input and output are clear. It takes one Query tree and produces one PlannedStmt. What it takes in is a tree of meaning; what it puts out is a PlannedStmt wrapping a tree of execution. The execution tree is built out of Plan nodes, and PlannedStmt bundles that tree together with the side information execution needs (for each referenced table, what lock mode to take; for each parameter slot the executor will fill at runtime, what type it is; and so on). At the moment of receiving, the tree only carries the meaning "pick the row from the users table where id is 1." At the moment of emitting, it carries a tree the executor can follow step by step, something like "go into the primary key index on users, fetch the one matching row, then output that whole row."

What sets the planner decisively apart from the analyzer and the rewriter is the nature of the decision. When the analyzer resolves "which table at which OID is users," there is one correct answer. When the rewriter expands a view reference into its underlying SELECT, there is one shape the expansion takes. But the question the planner gets, "in what order, using which index, with what join method should this Query be executed," has many equally correct candidates. The way to resolve WHERE id = 1 is a sequential scan only if there is no index. If an index is present, an index scan joins as a candidate, and even then, depending on what the statistics say, a sequential scan can still be cheaper. One candidate has to be picked, and the basis for picking is not correctness but estimated cost. That is why the planner carries a cost model. It scores the candidates with estimates derived from statistics and picks the cheapest path.

To express this cost-based selection, the inside of the planner is split into two layers. Path and Plan. Path is a candidate plane that expresses "this is a possible route." For each chunk of the query, one candidate is generated per possible route, and each carries its own cost into the competition. Plan is the winning route, frozen into a concrete form the executor can actually run. Without the Path stage, there is no place to line up candidates and sort them by cost. Without the Plan stage, the executor has nothing to follow. That is why the two layers exist separately.

1.4 splits into seven sections.

• 1.4.1 Path tree vs Plan tree: what's the difference: how multiple Path candidates get built for the same Query, how one of them gets frozen into a Plan, and why the two data structures exist separately.
• 1.4.2 Cost model: sequential scan formula: how PG estimates the cost of the simplest candidate, SeqScan, and what parameters like seq_page_cost and cpu_tuple_cost are quantifying.
• 1.4.3 Cost model: index scan formula: the cost formula for index scans, a step more complex than SeqScan, and how selectivity enters it.
• 1.4.4 Join strategies: nested loop, hash, merge: the three basic ways to bring two tables together, and the conditions under which each one wins.
• 1.4.5 Join order: dynamic programming vs GEQO: as the number of tables to join grows, the possible orders explode. How PG handles that with dynamic programming and the genetic algorithm (GEQO).
• 1.4.6 Statistics: pg_statistic and selectivity: where the accuracy of cost estimation comes from. The statistics ANALYZE builds, and how those statistics feed selectivity estimation.
• 1.4.7 Planner hook: when it fires, how to use it: the extension point PG has opened at the planner's entry. How external extensions intercept the entire plan or alter parts of the cost estimation.

The planner is not a one-track machine that finds the answer; it is a tool that bets between candidates with cost estimates. The same SQL freezing into a different plan after a single index is added or a single ANALYZE runs, a join order collapsing on one statistical miss, the node tree shown in EXPLAIN output looking the way it does: all of it is the result of this bet.