Part 3 of 5 · 1 min read
Selectivity estimates
A prediction of how many rows a condition will actually match, which shapes every downstream plan choice.
Think of a store manager predicting discount-code usage from past data. The manager expects about one in every two hundred customers to use it, before deciding how many extra staff to schedule.
In plain words: a selectivity estimate predicts what fraction, or how many rows, of a table a condition is expected to match, using the statistics described above. A highly selective condition, matching very few rows, favors an index lookup or a nested loop join with that side as the small outer table. A condition estimated to match a large fraction of the table favors a full scan or a hash join instead, since the overhead of an index lookup repeated many times can exceed the cost of simply scanning everything.
A worked example. The optimizer estimating WHERE customer_id = 42 matches only a handful of rows out of millions favors an index lookup; estimating WHERE status = 'placed' matches forty percent of all rows might favor a full scan instead.
The key lines. No new SQL; selectivity estimates are computed internally, driving the choices already covered as scan type and join algorithm.
Selectivity estimate
A selectivity estimate is the optimizer's prediction of how many, or what fraction, of a table's rows a condition will match, directly shaping its choice of scan method and join algorithm.
Example: a manager's predicted one-in-two-hundred discount usage rate, used to plan staffing, mirrors a selectivity estimate shaping a plan.