# Subquery Optimization - Case Study 4

+ Index Join will be more efficient. Why it is not used?
  + In TiDB, if the subquery contains aggregation functions, the outer query can not use Index Join or Index Hash Join

![`smart`](Subquery09_aa_001_20240220.png)

------------------------------------------------------------------------------
In the `SQL Statements` section of the `TiDB Dashboard`, it's evident that the optimizer has rewritten the subquery as a `left outer join`, utilizing a `hash join` to implement it. Interestingly, `HashJoin_13` processes only 10 rows, whereas its child operator `HashAgg_28(Probe)` reads 10,000 rows. As we've previously discussed, this approach isn't particularly efficient for joining tables.

Given that both tables, `T1` and `T2`, have highly selective indexes on the `col1` column and that `T2` has a significantly smaller data volume compared to `T1`, one might wonder why the `left outer join` doesn't utilize an `index join` or `index hash join`. The reason lies in the fact that if the `left outer join` employed an `index join` or `index hash join`, the inner query wouldn't be able to perform an aggregate operation. Consequently, the optimizer opted for a `hash join` within the `left outer join`.

In conclusion, the execution plan selected by the optimizer is not optimal, and using optimizer hints could enhance performance.