Partitioned Table Join Issue

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

Original topic: 分区表join问题

| username: qihuajun

To improve efficiency, please provide the following information. A clear problem description will help resolve the issue faster:
【TiDB Usage Environment】


  1. 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?

  2. 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

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.

| username: h5n1 | Original post link

Try enabling dynamic pruning by setting set session tidb_partition_prune_mode = 'dynamic'; and then test again.

| username: qihuajun | Original post link

We previously encountered more serious issues when enabling dynamic pruning: 升级6.1后,TiFlash服务异常 - #45,来自 windtalker - TiDB 的问答社区

| username: ddhe9527 | Original post link

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.

| username: h5n1 | Original post link

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.

| username: qihuajun | Original post link

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.

| username: qihuajun | Original post link

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.

| username: h5n1 | Original post link

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.

| username: ddhe9527 | Original post link

Remove INL_JOIN and try adding a hint to make ws use the primary key.

| username: h5n1 | Original post link

In the official response to your previous post, they mentioned the optimization progress and that all subsequent operations will use MPP mode.

| username: zhouzeru | Original post link

set session tidb_partition_prune_mode=dynamic

| username: 人如其名 | Original post link

Bro, can you provide the complete SQL statement (including variables) and the full execution plan?

| username: qihuajun | Original post link

Okay, I’ll wait for the release.

| username: yilong | Original post link

If you want to manually specify the join order, you can refer to the following two hints: