Questions about Dynamic Pruning in TiDB

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

Original topic: tidb 动态裁切的疑问

| username: Raymond

Dear teachers,

Why can’t TiDB’s partitioned table use index join in static pruning mode, like the example in the documentation?


I don’t know if my thinking is correct. Like the example in the documentation:
mysql> explain select /*+ TIDB_INLJ(t1, t2) / t1. from t1, t2 where t2.code = 0 and t2.id = t1.id;
Without dynamic pruning mode, the value of t2.id cannot be directly obtained, so it cannot be associated with t1.id, which is why index join cannot be used (unable to use the index of t1.id for index join).

| username: xfworld | Original post link

Global indexes currently do not support partition pruning (secondary indexes). It is estimated that this will be improved in version 6.5.X, so you can keep an eye on it.

You are correct in your understanding. Currently, partition pruning is suitable for narrowing the data range, but it lacks support for secondary indexes.

| username: Raymond | Original post link

Thank you for the reply. Is this related to global indexes?

| username: xfworld | Original post link

Without an index, it can only perform a table scan.

| username: TI表弟 | Original post link

You must use the id as a condition, right? where t1.id<30, otherwise, what are you trimming…

| username: Raymond | Original post link

I am very curious, index join is implemented on MySQL, but partition pruning is not implemented. This execution plan is a bit confusing to me.

| username: xfworld | Original post link

There’s nothing to be curious about; the scenarios faced are different. MySQL also has partitioning, but it currently doesn’t support this “capability.”

The execution plan is estimated through the collection and statistical information, so deviations or differences are normal.

| username: tidb菜鸟一只 | Original post link

In version 6.5.0, regardless of whether dynamic pruning or static pruning is used, the execution plan remains the same, and it is always a hash join.

| username: tidb菜鸟一只 | Original post link

image


image

| username: h5n1 | Original post link

Collect some statistics.

| username: Raymond | Original post link

The value of @@tidb_partition_prune_mode is dynamic.

| username: tidb菜鸟一只 | Original post link

The collected ones are all the latest statistics.

| username: tidb菜鸟一只 | Original post link

Looking at the picture, both static and dynamic settings have been configured.

| username: h5n1 | Original post link

My 6.5 test has no issues

| username: tidb菜鸟一只 | Original post link

My issue was that I used the wrong table, the one created for previous tests. Indeed, dynamic pruning uses index join, while static pruning uses hash join.