Predicate selectivity
Predicate selectivity is the fraction of a specified input population for which a predicate evaluates to true.
For a nonempty filter input, observed selectivity is matching rows divided by input rows. A planner instead estimates that fraction from statistics or assumptions. Multiplying it by the estimated input count gives an estimated output count, not a measurement. [1]
In SQL WHERE, false and NULL predicate results are discarded. Rows containing NULL still belong to the input denominator; conditioning on non-NULL rows asks a different question. The empirical ratio is undefined for an empty input, although its output count is zero. [2]
Combining predicates by multiplying their marginal selectivities assumes independence. Identical per-column counts can coexist with different joint counts, so this product can underestimate or overestimate. [7] A small matching fraction alone does not establish that an index is cheaper than a scan. [3] [4]
MySQL SHOW INDEX Cardinality estimates distinct index values, a different quantity. [5]
Relations
Citations
- PostgreSQL 18: Row Estimation Examples
- PostgreSQL 18: WHERE, true versus false or null
- PostgreSQL 18: Multivariate Statistics Examples
- PostgreSQL 18: Using EXPLAIN, output rows and repeated nodes
- MySQL 8.4: SHOW INDEX, Cardinality estimates distinct values
- Poosala and Ioannidis (VLDB 1997): Selectivity Estimation Without the Attribute Value Independence Assumption
Further reading
Sources & caveats
- Primary-source synthesis scoped to PostgreSQL 18 and MySQL 8.4 documentation. The linked counting model uses constructed rows; it is not a PostgreSQL/MySQL planner run or a performance measurement.
- VLDB 1997 Sections 2-7 support the marginal/joint distinction and MHIST/SVD context. The linked four-case model illustrates the principle; it does not reproduce those estimators or their accuracy.