predicate-selectivity · shipping · also filter selectivity, restriction selectivity

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

related
Query cardinality estimation, Covering index

Citations

  1. PostgreSQL 18: Row Estimation Examples (accessed 2026-11-18)
  2. PostgreSQL 18: WHERE, true versus false or null (accessed 2026-11-18)
  3. PostgreSQL 18: Multivariate Statistics Examples (accessed 2026-11-18)
  4. PostgreSQL 18: Using EXPLAIN, output rows and repeated nodes (accessed 2026-11-18)
  5. MySQL 8.4: SHOW INDEX, Cardinality estimates distinct values (accessed 2026-11-18)
  6. Poosala and Ioannidis (VLDB 1997): Selectivity Estimation Without the Attribute Value Independence Assumption (accessed 2026-11-18)

Further reading

  1. Hesela: estimated rows versus read coverage (accessed 2026-11-18)
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.

JSON: /data/terms/predicate-selectivity.json

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

Cite this page
Hesela team. Predicate selectivity. Hesela. 2026-11-18. https://hesela.dev/terms/predicate-selectivity/