A query planner chooses an execution path using estimated work. It does not run every candidate, time them, and keep the winner. PostgreSQL compares possible scans, joins, sorts, and other operations using statistics and a cost model. The chosen plan is therefore a prediction under current assumptions. Understanding a wrong prediction requires identifying which assumption failed, rather than treating a sequential scan or a nested loop as inherently suspicious.

Costs describe a model

PostgreSQL costs combine estimates of page access, tuple processing, operator work, and related activity. These units are not milliseconds. Startup cost matters when a consumer needs only a few rows, while total cost matters when the full result must be produced. Useful ordering can avoid a later sort, so the cheapest isolated scan is not necessarily the best input to the complete query.

The search also has limits. PostgreSQL considers alternative paths and join orders rather than executing them all. Large join problems can use the genetic query optimizer to control planning effort. Available indexes, legal transformations, configuration, and search choices constrain the candidates. Calling the selected plan mathematically optimal over every imaginable implementation overstates what the optimizer guarantees.

When it breaks

Fresh statistics can still produce a bad estimate. Ordinary statistics describe columns individually, so combining two predicates may assume independence even when the columns always move together. An underestimated intermediate result can make repeated lookups appear cheaper than they become, or distort later join choices. Repeating the same analysis without changing the statistical model cannot teach it a missing relationship.

Extended statistics address particular questions, not every estimation error. Functional dependencies help compatible equality conditions against constants, but do not establish the actual mapping between values. They are not general proofs for range filters or cross-column comparisons. An incompatible pair of constants can remain badly estimated even after a dependency is collected.

Check it quickly

Run this temporary-table experiment with PostgreSQL 18 and psql. Both columns contain the same one hundred distinct values, each repeated one hundred times. The final rollback removes the table and its statistics object.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE dimensions (a integer,b integer);
INSERT INTO dimensions SELECT g%100,g%100 FROM generate_series(1,10000) AS g;
ANALYZE dimensions;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM dimensions WHERE a=7 AND b=7;
CREATE STATISTICS pg_temp.dimensions_dependency (dependencies) ON a,b FROM dimensions;
ANALYZE dimensions;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM dimensions WHERE a=7 AND b=7;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM dimensions WHERE a=7 AND b=8;
DO $$ BEGIN
  IF (SELECT count(*) FROM dimensions WHERE a=7 AND b=7) <> 100 OR
     (SELECT count(*) FROM dimensions WHERE a=7 AND b=8) <> 0 THEN RAISE EXCEPTION 'wrong correlated population'; END IF;
END $$;
ROLLBACK;

Before the extended statistics, PostgreSQL 18.6 estimated one row for a=7 AND b=7, although the query returned one hundred. Each condition individually selects one percent; multiplying those fractions predicts one hundredth of a percent of ten thousand rows. The calculation is reasonable only under an independence assumption that this fixture deliberately violates.

Creating the dependencies object tells the next ANALYZE which relationship to collect. The second plan estimated one hundred rows and returned one hundred. Merely defining the object would not have populated it. The scan remained sequential because this example has no index and still examines the same small table. A better cardinality estimate is demonstrated; a faster application is not.

The final predicate asks for a=7 AND b=8. It returns zero rows, but the dependency estimate remains one hundred. This is the important negative case: knowing that one column determines another does not tell this statistics kind that seven must map specifically to seven. The assertions distinguish correct query results from inaccurate predictions; estimation errors do not change SQL semantics.

Choose the next experiment from the discrepancy

A multivariate common-value list can represent frequent value combinations when that is the missing information. Other workloads may need different statistics, an appropriate index, or a rewritten expression that exposes a usable condition. Add only the object justified by the observed predicates; unnecessary statistics consume analysis and planning resources too.

When comparing alternatives, keep the dataset, parameters, relevant settings, and result requirements consistent. Include rows processed, buffer activity, memory spills, and measured latency. A locally cheaper-looking node is not sufficient evidence if it creates more work upstream. Conversely, accurate row estimates do not rule out waiting, costly I/O, or an unsuitable cost model. Follow the specific difference between predicted and observed work before changing global planner settings.

Sources