The Query Planner

The PostgreSQL query planner, sometimes called the optimizer, is the component that decides how a query will actually be executed before a single row is read.

It does not follow a fixed rulebook the way a naive interpreter might; instead it estimates the cost of many candidate execution strategies and picks the one it believes is cheapest, which is why PostgreSQL is described as a cost-based optimizer.

Everything this planner decides, from scan type to join order to join algorithm, ultimately traces back to a single input: how many rows it expects at each step, and that expectation comes from statistics gathered about your data.

Summary

• Core Idea: The planner estimates the cost of candidate execution strategies using row-count statistics and picks the cheapest one, rather than following fixed rules.
• Why It Matters: A planner working from accurate statistics chooses good plans automatically; a planner working from stale or naive statistics chooses bad ones, often silently.
• Key Concepts: selectivity, cardinality estimate, cost model, pg_stats, ANALYZE, planner GUCs.
• When to Use: Reasoning about why a query picked one plan over another, tuning work_mem or cost constants, or diagnosing systematic misestimation across many queries.
• Limitations / Trade-offs: The planner's cost model is an approximation calibrated with configurable constants, and it assumes column independence unless you tell it otherwise via extended statistics.
• Related Topics: EXPLAIN plan reading, index design, autovacuum and autoanalyze, extended statistics.

Foundations

Every query the planner considers starts from a question: how many rows will survive this filter, this join, this aggregate?

That number is called a cardinality estimate, and it is the single most consequential value the planner produces, because every downstream cost calculation depends on it.

Cardinality estimates come from selectivity, the fraction of a table's rows expected to match a condition, which the planner computes using statistics stored in the pg_stats system view.

Those statistics are not derived by scanning the whole table on every query; they are captured periodically by the ANALYZE command, which samples a subset of rows and builds a compact statistical summary.

That summary typically includes a list of the most common values and their frequencies, a histogram of the remaining value distribution, an estimated count of distinct values, and the fraction of rows that are null.

For an equality filter like status = 'shipped', the planner checks whether 'shipped' appears in the most-common-values list and uses its recorded frequency directly if so, or falls back to a generic estimate based on distinct-value count if not.

For a range filter like created_at > now() - interval '7 days', the planner instead consults the histogram to estimate what fraction of rows fall above that bound.

Once the planner has a row estimate for a base table scan, it feeds that number into a cost formula that accounts for page reads and per-row CPU work, and that resulting cost is what you see printed on each node in EXPLAIN output.

The planner is not omniscient about your query's intent; it is only as good as the statistical model it is given, and that model is a deliberately compact approximation, not a full copy of your data.

Mechanics & Interactions

The planner's real complexity emerges once a query combines multiple conditions or multiple tables, because selectivities have to be combined into a single joint estimate.

The default assumption is column independence: if status = 'shipped' matches 5% of rows and country = 'US' matches 20% of rows, the planner assumes their combination matches roughly 1% of rows by simple multiplication.

That independence assumption is often wrong in real schemas, where columns like country and currency, or status and shipped_at IS NULL, are naturally correlated, and multiplying their selectivities under-estimates or over-estimates the true joint selectivity.

Extended statistics, created with CREATE STATISTICS, exist specifically to repair this by recording actual observed correlation, dependency, or joint distinct-value counts between named columns.

For joins, the planner extends the same estimate-then-cost approach across every viable join order and join algorithm it is willing to consider, using dynamic programming to avoid brute-forcing every permutation once the number of tables grows large.

It weighs a nested loop join, which repeats an inner scan once per outer row, against a hash join, which builds an in-memory hash table once, against a merge join, which requires both sides pre-sorted on the join key, and each has a different cost shape depending on the estimated row counts on either side.

This is precisely where a bad cardinality estimate becomes dangerous rather than merely inconvenient: an underestimated row count can lead the planner to choose a nested loop it believes will only iterate a handful of times, when in reality it iterates hundreds of thousands.

Several configuration parameters, collectively called planner GUCs, shape this decision without touching the statistics themselves; random_page_cost tunes how expensive random I/O looks relative to sequential I/O, and work_mem caps how much memory a sort or hash operation can use before spilling to disk.

Autovacuum's autoanalyze worker keeps statistics from going stale automatically, triggering a fresh ANALYZE once enough rows in a table have changed, though bulk loads and bulk deletes can outrun that threshold and leave the planner working from an outdated picture in the interim.

The planner also treats subqueries, common table expressions, and views largely by inlining and re-optimizing them in context, rather than treating them as opaque black boxes, which is why a CTE's plan can look different depending on how it's referenced elsewhere in the query.

-- The joint-selectivity assumption in miniature: without extended stats,
-- the planner multiplies these two selectivities as if independent
-- even though 'US' orders are disproportionately 'shipped'.
CREATE STATISTICS orders_country_status_stats (dependencies)
  ON country, status FROM orders;
ANALYZE orders;

Advanced Considerations & Applications

At scale, planner behavior becomes a systems problem, not just a per-query curiosity, because thousands of queries a second all depend on the same statistical model staying fresh.

Partitioned tables complicate estimation further, since the planner must combine partition-pruning decisions with per-partition statistics, and a partition with unusually skewed data can silently dominate the cost of a query that touches the whole partitioned set.

Multi-column indexes interact with the planner's estimates too, because an index can make a plan cheap enough to select even when the underlying row estimate is only roughly right, as long as the index avoids a full scan of the ruled-out rows.

Prepared statements introduce a distinct planning mode: PostgreSQL may plan generically, without knowledge of the actual parameter values, after enough executions with a custom plan, trading a small amount of per-plan accuracy for the ability to skip replanning on every call.

Extension-driven workloads add their own statistical wrinkles; pgvector's approximate nearest-neighbor indexes, for example, rely on the planner correctly costing an index scan against a sequential scan for similarity search, which depends on accurate row estimates just as much as ordinary B-tree scans do.

Systematic misestimation across an entire schema, rather than one query, is usually a sign of a structural issue: a table where autoanalyze thresholds are too coarse for its write volume, or a workload built entirely on expression predicates that plain column statistics can't describe.

Common Misconceptions

• "The planner scans the table to know how many rows match." - It never does this at plan time; it estimates from a periodically-sampled statistical summary in pg_stats.
• "More indexes always mean better plans." - An index only helps if the planner's cost model, driven by accurate row estimates, judges it cheaper than the alternatives for that specific query.
• "The planner treats every WHERE clause condition independently and correctly." - It assumes independence by default, which understates or overstates selectivity whenever columns are correlated.
• "ANALYZE reads the whole table." - ANALYZE samples a bounded number of rows controlled by default_statistics_target, not the entire table, which keeps it fast even on very large relations.
• "A slow query always means missing statistics." - Sometimes the statistics are accurate and the plan is genuinely the cheapest available one; the real fix is a schema or index change, not ANALYZE.
• "Planner GUCs like random_page_cost fix bad estimates." - GUCs reshape the cost model applied to a given row estimate; they do nothing to correct an estimate that's simply wrong.

FAQs

Related

• Statistics Basics - the pg_stats fields and histogram mechanics referenced here
• ANALYZE & Autoanalyze - how and when statistics actually get refreshed
• Extended Statistics - fixing the column-independence assumption directly
• Planner GUCs - the cost constants that shape plan selection
• How EXPLAIN Works - reading the plan tree this page's estimates feed into

Stack versions: This page was written for PostgreSQL 18.4 (stable line 18, maintenance line 17); the cost-based planner architecture and pg_stats model described here are stable across recent major versions.