Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 能否直接输出优化器改写后的语句？
|
username: Raymond
Feedback on Requirement
If the optimizer rewrites the original statement, can it be displayed through show warnings;? This can help users understand the execution plan more clearly.
Scenario Involving the Requirement
Expose the rewritten statement by the optimizer.
Expected Requirement Behavior
Expose the rewritten statement by the optimizer.
Alternative Solutions
Background Information
explain analyze select count(*) from (select * from a order by c1) a;
This statement is just an example and doesn’t have much practical significance. In the logical optimization phase, the order by action is removed and rewritten as select count(0) from a;. From the execution plan, it should be done this way.
mysql> explain select count(*) from (select * from a order by c1) a;
+--------------------------+---------+-----------+-----------------------------+-------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+---------+-----------+-----------------------------+-------------------------------+
| StreamAgg_8 | 1.00 | root | | funcs:count(1)->Column#4 |
| └─IndexReader_20 | 3.00 | root | | index:IndexFullScan_19 |
| └─IndexFullScan_19 | 3.00 | cop[tikv] | table:a, index:index_c1(c1) | keep order:true, stats:pseudo |
+--------------------------+---------+-----------+-----------------------------+-------------------------------+
3 rows in set (0.00 sec)
Through this example, we want TiDB to expose the rewritten statement via show warnings; to help users better judge the execution process of the statement.
For example, MySQL’s behavior:
By this method, the optimizer’s rewritten statement can be exposed. Can TiDB do the same? Just a personal suggestion, not sure if other experts think it’s necessary.