Scan and filter selectivity values

The index selection block includes, a scan selectivity value and a filter selectivity value. In the example above, these values are the same (0.000936).

For queries that specify search arguments on multiple columns, these values are different when the search arguments include the leading key, and some other index key that is not part of a prefix subset.

That is, if the index is on columns A, B, C, D, a query specifying search arguments on A, B, and D will have different scan and filter selectivities. The two selectivities are used for estimating costs at different levels:

Scan Selectivity

Filter Selectivity

Used to estimate:

Number of index rows and leaf-level pages to be read

Number of data pages to be accessed

Determined by:

Search arguments on leading columns in the index

All search arguments on the index under consideration. even if they are not part of the prefix subset for the index