What the Planner Actually Does
For every SQL statement, PostgreSQL's planner enumerates multiple candidate execution strategies, such as scanning a table sequentially versus via an index, or joining two tables with a hash join versus a nested loop, and assigns each candidate an estimated cost. It then picks the plan with the lowest estimated total cost, not necessarily the plan that is actually fastest, because the choice depends entirely on how accurate the underlying statistics and cost model assumptions are for that specific query and data distribution.
Cricket analogy: A team selector weighs multiple possible playing XIs for a Wankhede pitch, spinner-heavy versus pace-heavy, and picks the combination projected to concede the fewest runs, just as the planner picks the plan with the lowest projected cost.
The Cost Model: seq_page_cost, random_page_cost, and cpu_tuple_cost
The planner's cost formula combines a handful of tunable GUCs: seq_page_cost (default 1.0) models reading one sequential 8KB page, random_page_cost (default 4.0) models reading one page via a non-sequential access pattern such as an index lookup, and cpu_tuple_cost, cpu_index_tuple_cost, and cpu_operator_cost model per-row and per-operation CPU work. On modern SSD-backed systems, random access is nowhere near four times as expensive as sequential access, so lowering random_page_cost toward 1.1-2.0 is a common, well-justified tuning step that makes the planner favor index scans more realistically.
Cricket analogy: Assuming a spinner always costs four times more runs per over than a pacer on any pitch, even a raging turner at Chepauk, is like leaving random_page_cost at its default 4.0 on fast SSD storage where random access is nearly as cheap as sequential.
How Statistics Drive Selectivity Estimates
ANALYZE (run automatically by autovacuum's analyze threshold, or manually) samples table rows to build pg_statistic entries: most-common-value lists with frequencies, histogram boundaries for the rest of the distribution, and n_distinct estimates. The planner uses these to estimate selectivity, the fraction of rows a WHERE clause will match, and multiplies that fraction by table row count to get an estimated row count for each node; when a predicate involves correlated columns the default assumption of independence between columns can produce badly wrong selectivity unless extended statistics (CREATE STATISTICS) are defined.
Cricket analogy: A team's scouting report on a batsman's dismissal patterns against left-arm spin, built from sampled match footage, is like pg_statistic's histograms, and assuming that batsman's shot selection is unrelated to the match situation, when it clearly is, mirrors the planner's flawed independence assumption.
-- Inspect the planner's current cost settings
SHOW seq_page_cost;
SHOW random_page_cost;
SHOW cpu_tuple_cost;
-- A common, well-justified tuning step on SSD-backed systems
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();
-- Refresh statistics for a table after bulk load
ANALYZE VERBOSE orders;
-- Create extended statistics when the planner misjudges correlated columns
CREATE STATISTICS orders_country_region_stats (dependencies)
ON country, region FROM orders;
ANALYZE orders;You can inspect the raw statistics the planner relies on directly: SELECT * FROM pg_stats WHERE tablename = 'orders' AND attname = 'order_date';. This shows most_common_vals, most_common_freqs, and histogram_bounds, the exact inputs feeding selectivity estimation.
When the Planner Gets It Wrong
Even a perfectly accurate cost model can produce a bad real-world plan when default_statistics_target is too low for a skewed column, when a query uses a function on an indexed column without a matching expression index, or when correlated predicates across columns are assumed independent. In these cases the fix is rarely to fight the planner with query hints, which PostgreSQL deliberately does not support; instead you refresh statistics with ANALYZE, raise default_statistics_target for that column, add extended statistics, or restructure the predicate so the planner's model actually applies.
Cricket analogy: A captain who keeps setting an off-side field because that is what the coaching manual says, ignoring that this particular batsman is a strong leg-side player, is like a planner using a stale or too-coarse statistics target for a skewed column.
PostgreSQL intentionally has no query hint syntax like some other databases. Attempts to 'force' a plan (disabling enable_seqscan globally, for example) are session-wide, blunt instruments meant for diagnosis only, never for permanent production tuning; fix the underlying statistics or indexing instead.
- The planner enumerates candidate plans and picks the one with the lowest estimated cost, not necessarily the actual fastest plan.
- seq_page_cost, random_page_cost, and cpu_tuple_cost are the core tunable GUCs behind every cost calculation.
- random_page_cost's default of 4.0 reflects spinning-disk seek penalties and is usually too high for SSD-backed systems.
- Selectivity estimates come from pg_statistic entries built by ANALYZE: most-common-values, histograms, and n_distinct.
- The planner assumes independence between columns by default, which breaks down for correlated predicates unless extended statistics are created.
- default_statistics_target controls histogram/MCV granularity per column and can be raised for skewed columns.
- PostgreSQL has no query hints; fix bad plans via ANALYZE, statistics targets, extended statistics, or indexing, not by forcing plan shapes.
Practice what you learned
1. What does the PostgreSQL planner actually optimize for when choosing among candidate plans?
2. Why is lowering random_page_cost from its default of 4.0 often recommended on SSD-backed databases?
3. What planner assumption commonly leads to bad selectivity estimates on correlated columns like country and region?
4. What is the correct way to fix a bad plan caused by stale statistics on a heavily skewed column?
5. Which pg_statistic-derived structures does the planner use to estimate how many rows a WHERE clause will match?
Was this page helpful?
You May Also Like
Reading EXPLAIN ANALYZE
Learn to interpret PostgreSQL's EXPLAIN ANALYZE output so you can tell what a query planned versus what it actually did, and use that gap to fix slow queries.
Join Algorithms in PostgreSQL
Understand the three physical join strategies PostgreSQL can choose — nested loop, hash join, and merge join — and when each one wins.
Common Table Expressions
Learn how PostgreSQL's WITH clause structures complex queries into named, readable building blocks, including recursive CTEs for hierarchical data.