# Selectivity Analysis and Indexing Decisions

+ **`EXPLAIN ANALYZE`** provides actual rows returned by each operator
+ Calculate the selectivity by using the scanner and its parent operators' **`actRows`** column, for example, the ratio of **`[Selection(actRows)/TableFullScan(actRows)]`**
+ Not recommended to create indexes for selectivity between **`0.1`** and **`1`**
+ Check **`[Selection(Probe)/TableRowIDScan]`** for index scan efficiency 

![`smart`](TuningIndexByExplainAnalyze01_en_001_20230129.png)
![`smart`](TuningIndexByExplainAnalyze02_en_001_20230129.png)

------------------------------------------------------------------------------
To evaluate or tune SQL query performance using the `EXPLAIN ANALYZE` output, consider the following method: When unsure whether to create an index on one or more columns in the `WHERE` clause of a SQL statement, first check if the `EXPLAIN ANALYZE` output includes a `Selection` operator. The `Selection` operator filters the data produced by the previous operator. If it filters out a large volume of data (high selectivity), it suggests that the operation has not been pushed down effectively, and the previous operator is less efficient. This indicates that most of the filtering work is handled by the `Selection` operator.

If the `Selection` operator is present, examine the ratio of the `actRows` column for the `Selection` operator to the `actRows` column of the child operator below it. A low ratio, such as less than 10%, indicates that an index is needed or that the current index is not suitable.

Consider the example on the slide. The `Selection_6` operator is present, and its `operator info` column shows that it filters data based on the clause `HIRE_DATE BETWEEN '1990-01-01' AND '1990-01-31' AND GENDER = 'M'`. The `actRows` value for this operator is 1334, indicating that 1334 rows are produced as output. The child operator, `TableFullScan_5`, performs a full table scan, with an `actRows` value of 300024. This results in a ratio of output to input data for the `Selection_6` operator of 0.44%. Since this ratio is low (high selectivity), it suggests that an index is needed to improve the query performance.

Typically, to address such situations, methods like creating indexes, running `ANALYZE TABLE`, or using SQL bindings are employed for optimization. In this case, we will create an index on the `HIRE_DATE` column in the `WHERE` clause. Additional optimization techniques will be covered in subsequent chapters.

After creating an index on the `HIRE_DATE` column in the WHERE clause of the previous SQL statement, we found that in the `operator info` column of the `Selection_10(Probe)` operator, the corresponding filtering is the `GENDER` = 'M' clause in the statement, and the value of the `actRows` column is 1334. Its child operator is `TableRowIDScan_9`, and the value of its `actRows` column is 2244. The ratio of the `Selection_10(Probe)` operator to the `TableRowIDScan_9` operator is 59.45%. We can learn that after creating an index on the `HIRE_DATE` column, part of the filtering workload is pushed down to the index query. The input and output data of the `Selection_10(Probe)` operator are close, and the performance of the SQL statement has been improved after creating the index. Next, we will create a multi-column index.