A Tiny Cost Model: Selectivity

Query planners are cost-based: they estimate the cost of each plan and pick the cheapest. A key input is selectivity, the fraction of rows a condition keeps. A condition that keeps a small fraction favours an index; one that keeps most rows favours a plain scan (reading an index and then the table for most rows is slower than just reading the table).

Using a simple rule, an index lookup is worth it when selectivity is below 0.15. For each status in orders, return:

• status,
• row_count,
• selectivity = row_count ÷ total number of orders, rounded to 3 decimals,
• use_index = 1 if the selectivity is below 0.15, otherwise 0.

Order the result by status.

Constraints

• Columns, in order: status, row_count, selectivity, use_index
• selectivity is rounded to 3 decimals
• use_index is 1 when selectivity < 0.15, else 0
• Order by status

Hints