# Evaluating Join Operations with `EXPLAIN ANALYZE`

__*Is the index created a composite index?*__
__*Shall we drop the gender indexing for a comparison? Just use the birth_date in the index.*__

+ Tuning is possible when
  + the `actRows` value of the parent operator in a table join is significantly lower than that of its child operator
  + the `time` in the `execution info` column is much higher for the parent operator compared to its children


![`smart`](TuningJoinByExplainAnalyze01_en_001_20230129.png)

------------------------------------------------------------------------------
Here is a method to quickly evaluate or optimize the performance of join SQL statements using the `EXPLAIN ANALYZE` output. If the `actRows` value of the parent operator in a table join is significantly lower than that of its child operator, or if the `time` in the `execution info` column is much higher for the parent operator compared to its children, it suggests potential tuning opportunities for the chosen join algorithm.

For example, consider the SQL query on the slide. The `actRows` for the table join operator `HashJoin_20` is 892 rows, with an execution time of 1.21 seconds. The first child operator, `TableReader_25(Build)`, has `actRows` of 93 rows and an execution time of 210.8 milliseconds. The second child operator, `TableReader_22(Probe)`, has `actRows` of 2,844,047 rows and an execution time of 881.6 milliseconds. The `actRows` for `HashJoin_20` is much smaller than that of `TableReader_22(Probe)` (892 vs. 2,844,047, or approximately 0.003%). This discrepancy suggests that the `HashJoin` algorithm may not be ideal for this SQL query and might require adjustment.

Additionally, examining the first child operator, `TableReader_25(Build)`, reveals a `Selection_24` operator with `actRows` of 93 rows. Its child operator, `TableFullScan_23`, has 300,024 rows, resulting in a ratio of 93/300,024, or approximately 0.003%. This indicates that an index might be missing on the filtering conditions of the `EMPLOYEES` table. To address this, consider adding an index to the `BIRTH_DATE` column of the `EMPLOYEES` table.