# Dimensions Of Physical Optimization

![left](./student-guide/diagram/16-Dimension-of-Urbanization-01-01.png)
```
SELECT t1.a 
FROM t1 JOIN t2 ON t1.c1 = t2.c1 
ORDER BY t1.c1
```
------------------------------------------------------------
Physical optimization In order to solve the problem that multiple optimal solutions exist after optimization according to rules in logical optimization, it needs to be determined by the actual DataSource operator of the execution plan. In addition, it has a lot to do with the configuration and load of the responding physical machine, and theoretically the optimal execution plan should be solved with the optimal resources.

For example, the original SQL text is “select t1.a from t1 join t2 on t1.c1 = t2.c1 order by t1.c1;”, then its execution plan is because the aggregation operator pair “order by t1.c1" may have Sort or There are two ways to nominalSort, and then there are 3 join methods for t1 and t2 in the Join operator, Hash Join, Merge Join or Index Join. At the suboperator layer, there will also be two operators, Index Scan or Table Scan, depending on the join method and the index conditions on t1 and t2. It is not only necessary to optimize the rules through the logic optimizer, but also to select the optimal execution plan to complete the SQL request by using the description of the table, cluster resources and load in the statistics.

The physical optimizer first needs to determine whether to walk an index or a full table scan based on the statistics. Normally, the index scan will be triggered by setting a percentage based on the number of rows/total rows drawn, and a full table scan will be taken above this value. In addition, when selecting an index, it is also determined which composite index is appropriate to follow based on the index situation, such as the number of statistics that match the distinct values of the leading column of the index. In addition, the logical operators that have been built for the logical execution plan need to be implement in the physical optimizer, such as the aggregation operator hash operator or the stream operator, the hash join, index join or merge join operator, and the sort operator.

The push down mentioned in the logic optimizer is push down to the child node computation. Downloading is also considered in physical optimization. This push down refers to calculations performed in the coprocessor module in TiKV. Currently, the supported push down calculations are mainly aggregation operators, limit operators, and topN operator push down calculations.