When there are multiple search arguments on the same table, the optimizer uses statistics to combine the selectivity of the search arguments.
This query specifies search arguments for two columns in the table:
select title_id from titles where type = "news" and price < $20
With an index on type, price, the selectivity estimates vary, depending on whether statistics have been created for price:
With only statistics for type, the optimizer uses the frequency cell weight for type and a default selectivity for price. The selectivity for type is #.106600, and the default selectivity for an open-ended range query is 33%. The number of rows to be returned for the query is estimated using .106600 * .33, or .035178. With 5000 rows in the table, the estimate is 171 rows.
See Table 2-2 for the default values used when statistics are not available.
With statistics added for price, the histogram is used to estimate that .133334 rows match the search argument on price. Multiplied by the selectivity of type, the result is .014213, and the row estimate is 71 rows.
The actual number of rows returned is 53 rows for this query, so the additional statistics improved the accuracy. For this simple single-table query, the more accurate selectivity did not change the access method, the index on type, price. For some single-table queries, however, the additional statistics can help the optimizer make a better choice between using a table scan or using other indexes. In join queries, having more accurate statistics on each table can result in more efficient join orders.
