Choose a plan using an estimate, then check it

After finding legal query plans, the engine needs to choose one. It might follow an index for a few matching rows or scan a large part of the table. It must usually choose before it knows exactly how many rows will match.

A planner uses statistics and a cost model to estimate that work. Statistics summarize stored data; a cost model translates estimated rows, pages, and other operations into comparable costs. Those costs need not be milliseconds.

Count rows under an assumption

Our shop has 10,000 customers. Of them, 1,000 live in region R and 1,000 use currency C. A query requires both conditions.

If the two properties were independent, the estimate would be:

$10{,}000 \times 0.1 \times 0.1 = 100.$

Independent means that knowing one property does not change the probability of the other. Here that is a modeling assumption, not a fact established by the two separate counts.

Independent model
Region R: 1,000 customers
Expected fraction also using C: 10%
Estimated intersection: 100

Actual correlated data
All 1,000 customers in R use C
Actual intersection: 1,000
Estimate is ten times too small

The arithmetic is correct for the model. The model is wrong for this population.

The number of rows is cardinality. The fraction surviving a condition is selectivity. An underestimated cardinality can make repeated indexed lookups seem cheaper than a scan, or make an in-memory join appear to fit when it will need temporary storage.

Explain the mistaken choice

Imagine an illustrative cost model: scanning costs 500 units; fetching each qualifying row through an index costs one unit, plus 20 units of setup. At 100 expected matches, the index costs 120, so it wins. At 1,000 actual matches, that estimate becomes 1,020, exceeding the scan cost.

These numbers are invented to expose the decision boundary. A real model must account for caching, row placement, output width, and operator behavior. It should not assume that every row fetch has a constant physical cost.

Statistics can become stale or fail to capture relationships between columns. Better joint statistics may improve this example, but no summary predicts every future query perfectly.

Locate the break-even point

In the same illustrative model, how many qualifying rows make the index cost equal to the scan’s 500 units?

Include the setup cost

480 rows: 20 + 480 = 500. Below that point the index has lower estimated cost; above it the scan does. This compares only the stated model, not measured database timings.

When diagnosing a plan, compare estimated and observed rows at the first place they diverge. That is often more informative than blaming the final expensive operator alone.

PostgreSQL’s statistics documentation describes how estimates are supported, including statistics for related columns. EXPLAIN exposes estimates and, when requested, actual execution measurements.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter