Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 分区表join问题
To improve efficiency, please provide the following information. A clear problem description will help resolve the issue faster:
【TiDB Usage Environment】
【Problem】
-
As shown in Figure 1 in the attachment, when I execute a join query on a partitioned table, the partitioned table as the driven table has 3 partitions being queried. Only one partition uses the primary key index, while the other two partitions perform full table scans. The query conditions for these 3 partitions are exactly the same. Why does this happen?
-
As shown in Figure 2 in the attachment, I added the SQL Hint /*+ INL_JOIN(r, ws) */, but the execution plan did not follow the hint’s instruction. Instead, it treated the first table in the hint as the driven table.
【TiDB Version】 5.7.25-TiDB-v6.1.1
【Attachments】
Figure 1:
Figure 2:
For questions related to performance optimization or troubleshooting, please download and run the script. Be sure to select all and copy-paste the terminal output.
Try enabling dynamic pruning by setting set session tidb_partition_prune_mode = 'dynamic'; and then test again.
We previously encountered more serious issues when enabling dynamic pruning: 升级6.1后，TiFlash服务异常 - #45，来自 windtalker - TiDB 的问答社区
The hint has taken effect, and the hash join has been changed to an index nested loop join, but the order of the driving tables is reversed. You can try adding another hint to make the ws table use the primary key index to see if it can correct the driving order.
In your other post, the backlog was caused by a large number of cop requests being sent to TiFlash after dynamic pruning or in other cases of full table scans. Is this environment an upgrade from that version? Looking at your current execution plan, it all goes through TiKV. You can try enabling dynamic pruning and then use /read_from_storage(tikv[xx])/ to force it to go through TiKV and avoid TiFlash. Without dynamic pruning, table joins cannot recognize the join condition values for partition pruning.
After adding the use index hint, it remains the same. Not only did the order of the driving tables not change, but the query on the ws table, which has two partitions, still performs a full table scan.
Upgraded from 6.1 to 6.1.1.
Now it’s impossible to force queries to go through TiKV using the read_from_storage hint. Once partition pruning is enabled, it’s too many queries to modify.
In the test environment, queries are indeed normal with dynamic partition pruning enabled:
But the issue with partition pruning under TiFlash in production hasn’t been fixed, so I’m hesitant to enable dynamic partition pruning.
You can first set it at the session level to observe whether the execution plan is normal after enabling it. Enabling it globally does have a significant impact. You can bind the original SQL with hints using the SPM method, but TiDB does not yet support hints for optimizer-related parameters such as enabling dynamic pruning. I heard that the official team will introduce a variable hint in the future. For now, we can only wait for the official fix for this TiFlash issue.
Remove INL_JOIN and try adding a hint to make ws use the primary key.
In the official response to your previous post, they mentioned the optimization progress and that all subsequent operations will use MPP mode.
set session tidb_partition_prune_mode=dynamic
Bro, can you provide the complete SQL statement (including variables) and the full execution plan?
Okay, I’ll wait for the release.
If you want to manually specify the join order, you can refer to the following two hints:
|
username: qihuajun
|
username: 
|
username: 
|
username: 
|
username: 
|
username: