CSE Roadmap

Common tier · about 2 hours · 5 parts

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.