How the Postgres Query Planner Picks a Plan

The Postgres query planner is a cost-based optimizer. Every statement that reaches the server is parsed, rewritten, then handed to the planner, which weighs alternative execution strategies and picks the one it estimates is cheapest. It does not run the query to find out which is fastest; it predicts, using stored statistics and a cost model. Knowing how that prediction works is the difference between guessing at indexes and reading the plan the optimizer already hands you.

Statistics: what the planner thinks your data looks like

The planner never inspects your rows directly. It reasons from a sample gathered by ANALYZE (and autovacuum's analyze pass) and stored in pg_statistic, surfaced through the pg_stats view. The key inputs are the row and page estimates in pg_class (reltuples, relpages), each column's null fraction, its number of distinct values, a most-common-values list with frequencies, and a histogram covering the rest of the distribution. From these the planner estimates selectivity, the fraction of rows a predicate will keep. When those statistics are stale, or the sample missed a skewed column, the downstream estimates are wrong and the plan that looked cheapest on paper behaves badly at runtime.

The cost model and the seq-scan decision

Cost is an abstract unit, not milliseconds. The planner sums estimated page reads and CPU work using tunable constants: seq_page_cost (default 1.0), random_page_cost (default 4.0), cpu_tuple_cost, and a few others. That 4:1 I/O ratio is precisely why the choice between a sequential scan and an index scan hinges on selectivity. A seq scan reads every page once, cheaply and in physical order. An index scan pays for the index descent plus random heap I/O per matching row, so it only wins when the predicate is selective enough to touch far fewer rows. Ask for 2% of a table and an index scan is obvious; ask for 40% and the seq scan is genuinely faster. When matches are few but scattered, a bitmap index scan splits the difference, collecting tuple locations from the index, sorting them, then reading the heap in order.

Join methods and reading EXPLAIN

For joins the planner chooses among a nested loop (best when the outer side is tiny and the inner side is indexed), a hash join (build a hash on the smaller input, ideal for large unindexed equality joins), and a merge join (both inputs sorted on the key). It costs each option, estimates the result cardinality, and assembles the cheapest tree. EXPLAIN prints that tree; EXPLAIN (ANALYZE, BUFFERS) also executes it and shows actual rows and timing beside the estimates. The most useful habit is comparing estimated rows to actual rows on every node, because a large gap points straight at a stale statistic or a column correlation the planner could not see. Read it inside-out: leaf nodes feed their parents, and the topmost node's cost is the whole query.

Once you can read the plan, the fix is usually one of a handful: refresh statistics, raise the statistics target on a skewed column, rewrite a predicate so an existing index becomes usable, add the right index type, or accept that a seq scan was correct all along. A tool such as Querk can turn a pasted plan into a diagnosis and index DDL, and the same read is available from an editor through its MCP server. For chronic offenders, pair plan reading with a pg_stat_statements triage workflow to decide which queries earn the attention, and when a plan shows a Gather node, how parallel execution shifts the cost math is worth understanding. The planner is not adversarial; it is doing arithmetic on the numbers it has, so give it better numbers and the plan improves on its own.