A table of 100m option quotes has 2,000 underlyings and 40 expiries. The planner estimates WHERE underlying = 'SPX' AND expiry = '2026-06-19' at 100m / 2000 / 40 = 1,250 rows and the true count is…

Question

A table of 100m option quotes has 2,000 underlyings and 40 expiries. The planner estimates WHERE underlying = 'SPX' AND expiry = '2026-06-19' at 100m / 2000 / 40 = 1,250 rows and the true count is 90,000. Reproduce the estimate, name the assumption that failed, and give two fixes.

Accepted Answer

The planner multiplied two selectivities as though the columns were independent, and they are correlated, so the estimate is 72 times too low. Its arithmetic is 100m * (1/2000) * (1/40) = 1,250, which assumes that knowing the underlying tells you nothing about which expiries exist. In an option chain that is false: SPX lists weekly expiries while a thin name lists four a year, so SPX rows concentrate in a few expiries and the conditional selectivity of expiry given SPX is far above 1/40. The independence assumption is the failure, and the damage is downstream, because a 1,250 row estimate makes this branch look like the small side of a join, so the planner picks a nested loop and builds a join order around a table it believes is tiny. Fix one is extended statistics on the pair (underlying, expiry), which stores the joint distinct count and the most common combinations so the planner reads the real conditional selectivity. Fix two is a physical design that makes the estimate matter less, partitioning on expiry and clustering on underlying so the predicate becomes partition pruning plus a range scan whose cost barely depends on the guess. A join hint is a third option and ages worst, because it survives every future change in the data.