Quick answer
A query planner estimates how many rows each operation will emit so it can compare scans, join orders, join algorithms, sorts, and aggregates before execution. PostgreSQL derives selectivity from approximate statistics collected by ANALYZE, including null fraction, distinct-value estimates, most-common values, histograms, physical correlation, and optional extended statistics across columns or expressions. Estimates are inputs to planning, not exact counts.
Cardinality errors matter because they compound. If a filter is estimated at 100 rows but emits 100,000, a nested loop can repeat its inner work far more often than expected, a hash or sort can exceed memory, or an index path can perform many heap visits. Diagnose the earliest meaningful estimate divergence, then ask why the statistics model differs from the production distribution.
Fixes include refreshing statistics after material data changes, increasing a targeted statistics level, creating justified multivariate or expression statistics, rewriting non-estimable predicates, or changing the schema or query. Validate across representative value classes. Never run ANALYZE or raise targets everywhere simply to make one plan change: collection, storage, analysis, and planning have costs, and a new estimate must still produce correct, stable workload behavior.
Shared order-history incident stage
The order-history release filters on tenant_bucket, status, and created_at. After a flash sale, tenant 7 has many recent paid orders; after a backfill, older cancelled orders dominate other tenants. These columns are not independent. The existing single-column statistics describe each distribution separately, but multiplying independent selectivities understates the popular combination.
The query plan therefore estimates a small number of rows and chooses a path suitable for a rare tenant/status pair. Actual execution emits far more rows. The join to items repeats, the sort receives a larger input, and buffer and temporary work increase. The mismatch is visible in PostgreSQL EXPLAIN and Query Plans as estimated versus actual rows and loops.
Statistics freshness is only one hypothesis. The team must distinguish stale data from insufficient representation. A fresh sample can still miss a narrow skew; a higher per-column target cannot encode correlation between columns; an expression applied to created_at may need expression statistics or a query rewrite; a prepared generic plan can intentionally avoid specializing for one value. The incident uses each possibility as a separate testable hypothesis.
Long transactions, bloat, locks, partition pruning, and connection-pool wait remain downstream contributors. Correcting a row estimate may change the plan but does not prove those clocks recovered. The runbook measures both cause and user outcome.
Core mechanism and evidence boundary
Selectivity is the estimated fraction of input rows that satisfies a predicate. Cardinality is the estimated number of rows after applying it. PostgreSQL combines relation-size estimates with column statistics stored internally and exposed in safer form through pg_stats. null_frac, n_distinct, most-common values and frequencies, histogram bounds, and correlation each answer a limited question about a sampled distribution.
pg_stats is a readable, access-filtered view over information stored in lower-level catalogs such as pg_statistic; extended-statistics definitions and data use their own catalogs. Prefer documented views and functions for routine diagnosis, and do not treat raw catalog layout or stored values as a stable application interface.
Most-common-value lists represent frequent values directly. Histograms divide the remaining distribution into approximate groups. n_distinct may be a positive count or a negative multiplier related to table size. Correlation describes relationship between physical row order and logical column order; it is not multicolumn statistical correlation. Do not infer causal relationships from the field name.
Single-column estimates commonly assume predicates are independent. PostgreSQL extended statistics can capture functional dependencies, multivariate distinct counts, and multivariate most-common combinations for columns or expressions. In PostgreSQL 18, these objects are PostgreSQL-specific, not a SQL standard. The official documentation also notes limits, including that extended statistics are not currently used for selectivity estimates made for table joins. State the applicable version instead of promising a universal planner capability.
ANALYZE takes a statistical sample, and repeated runs can produce slightly different estimates. Statistics can lag a rapid load, be absent on a new partition, or have insufficient resolution for skew. Cumulative monitoring can also lag active work. Record collection time, relation changes, statistics target, PostgreSQL version, relevant configuration, plan cache behavior, and the literal class used in the experiment.
Expressions create another boundary. Statistics on the base created_at column do not automatically describe every transformation such as date_trunc, a cast, or a business-calendar function. PostgreSQL can collect statistics on an expression through CREATE STATISTICS, and expression indexes can provide both an access path and statistics, but those choices have different write, storage, and maintenance costs. Often the safest repair is a sargable half-open range on the base column, provided it preserves timezone and business semantics.
Partitioned tables require explicit observation. PostgreSQL can hold statistics for individual partitions and, depending on how analysis is invoked, information representing the inheritance hierarchy. A newly attached or rapidly loaded partition may not resemble older partitions. Confirm which relation statistics the plan uses and whether pruning removes unrelated partitions before attributing every error to the parent table.
The planner also estimates groups and joins, not only filters. Distinct-count errors can make an aggregate or hash structure larger than expected. Correlated join keys can cause output divergence that current multivariate table statistics do not repair. That is why the workflow follows estimates through the entire tree rather than declaring victory when one scan estimate improves.
Use plan_rows, actual_rows, and plan_loops together. The teaching ratio actual rows / max(estimated rows, 1) highlights divergence, but no single ratio threshold proves a defect. A one-row estimate that returns ten rows may be harmless; a tenfold error at the outer side of a deeply repeated join can be severe. These fields are not a stable OpenTelemetry semantic convention. Never place raw predicates, query identifiers, tenant IDs, or plan text in metric labels.
Minimal reproducible PostgreSQL 18 example
This focused statistics example reuses the synthetic order-history model introduced in Database Query Performance for Backend Systems. Create correlated synthetic data in a disposable PostgreSQL 18 database:
CREATE TABLE planner_orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_bucket integer NOT NULL,
status text NOT NULL,
created_at date NOT NULL
);
INSERT INTO planner_orders (tenant_bucket, status, created_at)
SELECT 7, 'PAID', DATE '2026-08-01' + (g % 3)
FROM generate_series(1, 90000) AS g;
INSERT INTO planner_orders (tenant_bucket, status, created_at)
SELECT 1 + (g % 20), 'CANCELLED', DATE '2025-01-01' + (g % 300)
FROM generate_series(1, 10000) AS g;
ANALYZE planner_orders;Inspect the initial estimate in the disposable fixture:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id
FROM planner_orders
WHERE tenant_bucket = 7
AND status = 'PAID';Review the safe statistics view without exporting production values:
SELECT attname, null_frac, n_distinct,
most_common_vals, most_common_freqs,
histogram_bounds, correlation
FROM pg_stats
WHERE schemaname = 'public'
AND tablename = 'planner_orders';Create a justified multivariate object, recollect, and compare:
CREATE STATISTICS planner_orders_tenant_status_stats
(dependencies, mcv, ndistinct)
ON tenant_bucket, status
FROM planner_orders;
ANALYZE planner_orders;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id
FROM planner_orders
WHERE tenant_bucket = 7
AND status = 'PAID';Do not expect every node or time to become identical across runs. Compare the relevant row estimate, chosen work, actual rows, buffers, and correctness for dominant, typical, and rare combinations. If an application uses prepared statements, separately test the execution mode actually used by its driver and pool. Do not force custom planning in production without measuring planning overhead and workload-wide behavior.
Failure modes and dangerous misconceptions
Calling every estimate error stale statistics. A current sample can lack correlated, expression, or sufficiently detailed information. Test freshness, representation, and query shape separately.
Running ANALYZE until the desired plan appears. Sampling variation can make this look successful by chance. Define expected distributions and repeat the experiment. A plan change is not the acceptance criterion.
Increasing the global statistics target. Broad increases enlarge statistics, analysis time, and planning work. Prefer evidence-backed per-column or extended statistics and verify their value.
Reading correlation as column-to-column correlation. In pg_stats, it concerns physical row order versus the column’s logical order. Use extended statistics for supported multicolumn relationships.
Assuming extended statistics fix joins. PostgreSQL 18 documents limits for join selectivity. Validate the exact predicate and plan rather than inferring support from the existence of a statistics object.
Treating estimates as capacity counters. They are planner inputs for a particular statement and data model. Use actual workload metrics and database activity for capacity denominators.
Ignoring plan caching. A generic plan may trade specialization for lower repeated planning cost. A plan observed with a literal in a console may differ from the application’s prepared execution path.
Embedding sensitive literals in evidence. Statistics arrays and SQL predicates can disclose tenant or personal distributions. Restrict and sanitize diagnostics.
Using a target plan as the test oracle. A particular node sequence is an implementation choice under a data and cost model. Assert correct results, bounded work, and representative latency. Allow the planner to choose a different healthy plan as the distribution grows.
Refreshing statistics during peak load without a budget. Although routine analysis is normal maintenance, a broad manual run still reads samples, updates catalogs, and competes for resources. Scope the target relations, observe progress and load, and define a stop condition.
Security, privacy, capacity, and cost implications
Statistics and plans may reveal PII or personal data distributions even when rows are not exported. Most-common values can expose frequent tenant identifiers, statuses, regions, or other sensitive categories. Limit access to catalog and monitoring views, redact artifacts, avoid screenshots of raw arrays, and apply audited retention.
Do not use query text, tenant IDs, user IDs, order IDs, literal classes, queryid, or trace identifiers as metric labels. Bounded dimensions such as route, operation, environment, and database role support aggregation without revealing individual workload identity. Store detailed fingerprints in a restricted diagnostic system.
Higher statistics targets use more sample work, catalog space, and planner processing. Extended statistics add maintenance and schema ownership. Manual ANALYZE competes for CPU and I/O, and a large post-load analysis can overlap peak traffic. Schedule and bound it according to the actual change, then observe replicas and other maintenance.
Accurate estimates can select a plan that consumes more memory or parallel workers because that is cheaper for one query. Validate whole-system concurrency. Local latency gains do not authorize unbounded per-session memory or planner settings.
Testing and production validation
Create distributions that represent common, dominant, rare, new, and historical value combinations. Test after bulk load, after incremental writes, and after statistics refresh. Record the seed or fixture recipe so results are reproducible without copying production data.
For each class, compare estimated rows, actual rows, loops, node selection, buffers, temporary I/O, duration, and returned rows. Check that improved estimates occur at the node where correlation matters. Measure planning time as well as execution time when adding targets or statistics objects.
Exercise application-prepared statements using the real driver configuration. Compare the console experiment with the application path without logging parameters. Validate connection-pool behavior and concurrency because planning gains can be offset by execution saturation.
Roll out statistics changes as schema/operations changes with ownership, stop conditions, and rollback. After deployment, confirm the target query family, user SLI, tail latency, pool wait, database duration, buffers, temp I/O, and write workload. Repeat after data growth to ensure the model remains useful rather than merely matching one incident snapshot.
Use a shadow comparison when possible: collect a sanitized fixture, evaluate the old and proposed statistics definitions, and compare multiple query classes without changing production. In staging load tests, vary concurrency as well as literals because a plan that consumes more memory per query can become worse at production parallelism even when its single-session duration drops.
Validate removal too. If an extended statistics object no longer affects representative estimates or duplicates another mechanism, test dropping it in a safe environment and measure planning behavior. Unowned statistics objects accumulate operational debt just like unused indexes.
Operations checklist
- Identify the earliest plan node with a material estimate-to-actual divergence.
- Preserve estimated rows, actual rows, loops, output rows, and surrounding node relationships.
- Record PostgreSQL version, statistics age, relation changes, targets, settings, and prepared execution mode.
- Inspect
pg_statsthrough a restricted role and avoid exporting sensitive values. - Distinguish stale statistics from skew, correlated columns, expressions, partitions, and plan caching.
- Run manual
ANALYZEonly when a material change justifies its resource cost. - Prefer targeted statistics changes over global target increases.
- Validate extended-statistics support for the exact predicate; do not assume join support.
- Test dominant, typical, rare, new, and historical value classes.
- Measure planning time, execution work, memory, buffers, temporary I/O, writes, and concurrency.
- Keep high-cardinality identifiers out of metrics and sanitize plan artifacts.
- Close only after user impact and workload-wide resource evidence recover.
Official sources
- PostgreSQL 18: Planner Statistics — statistics types, targets, and extended statistics; accessed 2026-08-04.
- PostgreSQL 18: How the Planner Uses Statistics — row-estimation and multivariate examples; accessed 2026-08-04.
- PostgreSQL 18: CREATE STATISTICS — supported objects and documented limits; accessed 2026-08-04.
- PostgreSQL 18: ANALYZE — collection behavior and options; accessed 2026-08-04.
- PostgreSQL 18: pg_stats — readable statistics fields and access behavior; accessed 2026-08-04.
Continue the learning path
Read the estimate in context with PostgreSQL EXPLAIN and Query Plans, then design access paths in Composite, Covering, and Partial Indexes. Database Indexes provides the B-tree and write-cost foundation. The broader evidence model is in Database Query Performance for Backend Systems. Continue through System Design or Topics.