The original SQL runs normally, but wrapping it in an outer layer makes it very slow (Has an unrelated subquery been optimized into a related subquery?)

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 原sql运行正常外面包一层就很慢 (不相关子查询优化为相关子查询了?)

| username: 莫何者也

[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;

| username: 莫何者也 | Original post link

Is this related to the impact of predicate pushdown?
The optimization documentation only specifies enabling predicate pushdown, not disabling it.

| username: 莫何者也 | Original post link

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?

| username: FutureDB | Original post link

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.

| username: Kongdom | Original post link

:thinking: 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.

| username: Soysauce520 | Original post link

Try adding a hint with select /*+ NO_DECORRELATE() */ sum(A.qty).

| username: TiDBer_刚 | Original post link

D should have an index.

| username: WinterLiu | Original post link

This question is quite advanced and beyond the scope.

| username: 帅的掉渣 | Original post link

Execution plan? You can use USE_INDEX to specify the use of a particular index.

| username: h5n1 | Original post link

Try using this internal SQL with the hint to force the order: SELECT /*+ LEADING(t1, t2) */.

| username: dba远航 | Original post link

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';
| username: zhaokede | Original post link

This is just for convenience, the execution plan should still be the same.

| username: 莫何者也 | Original post link

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.

  1. Is there a more general method to specify not to push down?
  2. 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.

| username: 莫何者也 | Original post link

The same, it will still push the conditions down for execution.

| username: 莫何者也 | Original post link

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.

| username: Kongdom | Original post link

:thinking: As you said, this might be a negative optimization. You can submit a request to TiDB.

| username: FutureDB | Original post link

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.

| username: 有猫万事足 | Original post link

It is recommended to provide cluster on-site information.

Because if it cannot be reproduced, it cannot be fixed.

| username: 濱崎悟空 | Original post link

Look at the execution plan + index hints

| username: Kongdom | Original post link

:muscle: This tool is great.