How does the database estimate how many rows a WHERE clause will return?

For an equality filter on a value absent from the common list, the share is one over the number of distinct values. If a status column has \(v=7\) distinct values and the table has \(n=9{,}400{,}000\) rows, then \(s=1/7=0.142857\) and \(m=9{,}400{,}000/7=1{,}342{,}857.14\). The planner rounds this to an integer, but keeps the fraction for later steps. If an earlier filter on country kept 0.031 of the table, the rows entering the status filter are \(9{,}400{,}000 \times 0.031 = 291{,}400\), and the status filter leaves \(291{,}400/7 = 41{,}628.57\), about 41,629 rows.

A range uses the histogram rather than the distinct count. Take buckets with lower and upper bounds and equal row counts. Bucket three runs from 29 to 58 and holds 94,000 rows; bucket four runs from 58 to 140 and holds 94,000 rows. For a range from 30 to 60, the part inside bucket three is \(94{,}000 \times (58-30)/(58-29) = 94{,}000 \times 28/29 = 90{,}758.62\). The part inside bucket four is \(94{,}000 \times (60-58)/(140-58) = 94{,}000 \times 2/82 = 2{,}292.68\). The estimate is their sum, \(93{,}051.30\) rows.

The natural guess is that the database counts matching rows, or uses one row count and a fixed fraction. Neither happens. Counting would mean reading the table to decide how to read the table, which is the cost the plan is supposed to avoid. A single row count cannot distinguish a column with two values from one with two million values, so it cannot produce a useful share. The summary exists precisely to supply the missing per-column numbers without touching the rows.

Several filters on one table are compounded by multiplying their shares. That is exact only if the columns are unrelated: if knowing a row passed one filter tells you nothing about whether it passes the next. With \(0.031\), \(1/7\) and \(0.2\), the combined share is \(0.031 \times (1/7) \times 0.2 = 0.000885714\), and \(9{,}400{,}000 \times 0.000885714 = 8{,}325.71\) rows. The plan is built for about 8,326 rows. Each share came from the summary, so each error is multiplied too: three estimates each wrong by a factor of two in the same direction give a result wrong by \(2^3=8\).

The largest range error comes from interpolation inside a bucket. The top bucket spans \(9{,}800-140=9{,}660\) units and holds 94,000 rows, so even spreading gives \(94{,}000/9{,}660=9.73\) rows per unit. If the real values cluster near 140, an estimate inside that bucket is far too high; if they cluster near 9,800, it is far too low. The histogram cannot see inside the bucket. A second failure is stale or sampled statistics: a sample of 30,000 from 9,400,000 rows is \(30{,}000/9{,}400{,}000=0.003191\), or 0.319 per cent of the table, and the summary may be hours old. A third is correlation: country and region are not independent, so multiplying their shares understates the rows that survive both.

When a condition cannot be analysed at all, such as a comparison against a function result or a parameter whose value is not known yet, the planner substitutes a constant. A common default for an unanalysable equality is \(0.005=1/200\). Two such conditions give \(0.005 \times 0.005 = 0.000025 = 1/40{,}000\), a share with no connection to the data. That is a diagnostic: an estimate that lands exactly on a default share is usually a default being applied, not a measurement.