Database field guide · 03
PostgreSQL Query Planning
Estimates, costs, joins, and plan stability
A practical model for understanding why PostgreSQL chooses a plan and how to investigate when that choice is wrong.
Franck Pachot7 chaptersPostgreSQL · cardinality · join planning
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
A plan is a forecast
The planner cannot execute every alternative. It predicts cardinalities and costs from statistics, parameters, and algebraic transformations, then chooses the cheapest forecast. Most surprising plans begin with a wrong estimate rather than a broken scan or join implementation.
- Cost is an internal comparison unit, not elapsed milliseconds.
- Rows flowing between nodes matter more than the final row count.
- The first large estimate error is usually more useful than the top node.
02
Statistics describe distributions
Per-column statistics summarize null fractions, distinct values, common values, and histograms. They cannot automatically describe every correlation between columns or expressions. Extended statistics and expression indexes communicate facts that independent column summaries miss.
- Increase statistics targets selectively, not globally by reflex.
- Analyze after representative data changes.
- Compare estimated and actual rows at every plan boundary.
03
Parameters change what can be known
A literal can reveal selectivity during planning; a parameter may not. Prepared statements can move from custom plans to a generic plan whose compromise is cheaper across executions but poor for skewed values. The right diagnosis distinguishes planning-time ignorance from stale statistics.
- Inspect custom and generic plan behavior separately.
- Parameter skew can justify query variants or controlled replanning.
- Do not disable prepared statements before measuring their trade-off.
04
Join order multiplies uncertainty
For several relations, the planner must choose both join algorithms and order. Underestimating an early input can make a nested loop look cheap; overestimating can hide a useful parameterized index path. Hash and merge joins have different memory, ordering, and startup profiles.
- Nested loops are excellent when the inner lookup stays small.
- Hash joins favor larger equality joins when memory is adequate.
- Merge joins exploit compatible ordering and range-like progression.
05
Cost parameters are a model
Settings such as random_page_cost and effective_cache_size describe the environment; they are not knobs for forcing one query. Calibrate them to broad storage and cache behavior, then fix local modeling problems with statistics, indexes, or query structure.
- Planner enable flags are diagnostic tools, not permanent hints.
- A forced alternative reveals whether a better executable plan exists.
- Global parameter changes require workload-wide evidence.
06
A repeatable investigation
Capture the SQL, parameters, schema, statistics age, plan, runtime rows, buffers, and relevant settings. Find the earliest estimate divergence, formulate one cause, and change one input to the model. This produces knowledge that survives the next query instead of a brittle forced plan.
- Preserve the original plan before experimenting.
- Test realistic parameter values and cache states.
- Treat plan stability as controlled adaptability, not immobility.
07
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Find the first estimation error
EXPLAIN estimates are available without execution; ANALYZE executes the statement. For each node compare estimated rows with actual rows per loop, not only total output. If a nested loop runs 10,000 times, actual rows=2 means 20,000 rows overall. The first large divergence from the leaves upward is usually the cause; later errors are often multiplication effects.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT ...;
ROLLBACK; -- required when diagnosing modifying statements
Inspect what statistics can represent
pg_stats exposes most-common values, frequencies, histogram bounds, null fraction, correlation, and estimated distinct counts. A negative n_distinct is a multiplier of table cardinality. Single-column statistics assume independence, so correlated predicates such as country and postal_code can be badly underestimated. Dependency and multivariate-NDISTINCT statistics give the planner the missing relationship.
CREATE STATISTICS customer_geo_stats
(dependencies, ndistinct, mcv)
ON country, postal_code FROM customer;
ANALYZE customer;
SELECT attname, n_distinct, most_common_vals, correlation
FROM pg_stats WHERE tablename = 'customer';
Separate generic from custom plans
A prepared statement initially receives custom plans using its parameter values. PostgreSQL may later choose a generic plan when its estimated average cost plus planning savings beats continued custom planning. Skew makes that compromise visible: one value needs an index while another needs a scan. plan_cache_mode is a diagnostic control, not a default tuning recommendation.
SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE by_status('RARE');
SET LOCAL plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE by_status('RARE');
Interpret join memory correctly
Hash joins build a hash table from one input and probe it with the other. If the build side exceeds work_mem multiplied by hash_mem_multiplier, batches spill to temporary files. A sort may consume work_mem independently at several plan nodes and parallel workers. Raising work_mem globally can multiply memory consumption dramatically; use node evidence and scoped settings.
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Look for: Batches > 1, temp read/written,
-- Sort Method: external merge, and Disk usage.
SET LOCAL work_mem = '128MB';
08
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
