Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 原sql运行正常外面包一层就很慢 (不相关子查询优化为相关子查询了?)
[TiDB Usage Environment] Production Environment
[TiDB Version] 7.4 and 7.5
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: The SQL itself is relatively simple and runs normally, but when the framework wraps an outer layer, it becomes very slow]
The following SQL, the inner SQL is a multi-table join that summarizes quantities by name, and this SQL runs normally; its result has only a few rows of data;
However, when wrapping an outer layer query, the entire SQL becomes very slow.
SELECT * FROM (
SELECT sum(Q.pe_qty) qty, N.pe_name
FROM
xxx_Qty Q
INNER JOIN xxx_Name ON Q.name_id = N.name_id
WHERE XXX
GROUP BY
N.PE_NAME
) T_A_0
WHERE T_A_0.PE_NAME = '高XX';
Since the inner SQL runs well, I haven’t posted the detailed specifics;
How to make the outer layer not affect the optimization execution of the original inner SQL
Supplement: Looking at the execution plan, it applies the condition from the outer wrapped SQL to the inner SQL and executes this filter in advance;
Is this related to the impact of predicate pushdown?
The optimization documentation only specifies enabling predicate pushdown, not disabling it.
The issue is clear:
select * from (
select sum(A.qty), D.name from
A
inner join B on xxx
inner join C on xxx
inner join D on xxx
group by D.name
) Temp
where temp.name = 'name1'
There are multiple table joins internally, and under normal circumstances, the optimized join order is A B C D. However, when the outer layer includes the D.name condition, this condition does not have an index and is inefficient. TiDB changes the join order and filters using D.name first.
Dear experts, is there any optimization hint in TiDB to make the entire internal SQL completely independent?
It is estimated that the outer filter conditions are pushed down to the inner layer for execution. You can check the specific execution plan for this.
This optimization logic seems correct, right? Filter by condition first to reduce the data volume.
Maybe you can add an index? Or try putting the where condition after the inner join’s on clause.
Try adding a hint with select /*+ NO_DECORRELATE() */ sum(A.qty)
.
This question is quite advanced and beyond the scope.
Execution plan? You can use USE_INDEX to specify the use of a particular index.
Try using this internal SQL with the hint to force the order: SELECT /*+ LEADING(t1, t2) */
.
Try using the WITH table as follows:
WITH t AS (
SELECT SUM(A.qty), D.name
FROM A
INNER JOIN B ON xxx
INNER JOIN C ON xxx
INNER JOIN D ON xxx
GROUP BY D.name
)
SELECT * FROM t WHERE t.name = 'name1';
This is just for convenience, the execution plan should still be the same.
Indeed, it is like this, pushed down.
Since this field is not suitable for indexing, adding an index is not considered for now.
The current solution is to rewrite the SQL as follows:
select * from A
inner join B
inner join C
STRAIGHT_JOIN D
where xxx
Force the join order to prevent the optimizer from executing D’s query prematurely.
This modification is effective, but it may not be the best solution.
- Is there a more general method to specify not to push down?
- Are there any TiDB experts who can track this issue? The fundamental problem is that TiDB made a negative optimization; why did it judge such an inefficient plan as a good plan?
Additional information: A/B/C have other relatively good index filtering conditions;
Table D is relatively large; D.name is not suitable for indexing, at least not considered for now.
The same, it will still push the conditions down for execution.
Under normal circumstances, if D.name has an index, or if the preceding tables A, B, and C do not have better conditions, pushing down might be reasonable. However, in this case, A, B, and C have good filtering conditions; D.name does not have an index; and table D is also relatively large.
As you said, this might be a negative optimization. You can submit a request to TiDB.
The essence of this issue is that the TiDB optimizer has problems with cost estimation in certain scenarios, leading to the selection of incorrect execution plans. We have encountered this quite often in practice. Currently, what can be done on the application side is to bind the execution plan.
It is recommended to provide cluster on-site information.
Because if it cannot be reproduced, it cannot be fixed.
Look at the execution plan + index hints