In dynamic pruning mode, the execution plan still shows partition:all

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

Original topic: 动态裁切模式下,执行计划仍然显示partition:all

| username: Raymond

When the dynamic partition pruning mode is enabled, the execution plan still shows partition:all.

Reproduction steps:
TiDB version: 6.1.5

The table structure of t1 is as follows:

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `h_record` int(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`id`)
(PARTITION `p0` VALUES LESS THAN (100),
 PARTITION `p1` VALUES LESS THAN (200),
 PARTITION `p2` VALUES LESS THAN (300),
 PARTITION `p3` VALUES LESS THAN (400));
insert into t1 values(101,1),(201,2),(301,3),(399,4);

The table structure of t2 is as follows:

CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into t2 values(101,0),(201,1),(301,2),(399,3);
analyze table t1;
analyze table t2;
set tidb_partition_prune_mode = 'dynamic';
mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.id,t1.h_record from t1 join t2 on t1.id=t2.id where t2.grade = 0;
+---------------------------------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| id                              | estRows | task      | access object          | operator info                                                                                                               |
+---------------------------------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_12                    | 0.01    | root      |                        | inner join, inner:IndexLookUp_11, outer key:fqtest.t2.id, inner key:fqtest.t1.id, equal cond:eq(fqtest.t2.id, fqtest.t1.id) |
| ├─TableReader_17(Build)         | 0.01    | root      |                        | data:Selection_16                                                                                                           |
| │ └─Selection_16                | 0.01    | cop[tikv] |                        | eq(fqtest.t2.grade, 0), not(isnull(fqtest.t2.id))                                                                           |
| │   └─TableFullScan_15          | 8.00    | cop[tikv] | table:t2               | keep order:false, stats:pseudo                                                                                              |
| └─IndexLookUp_11(Probe)         | 1.25    | root      | partition:all          |                                                                                                                             |
|   ├─Selection_10(Build)         | 1.25    | cop[tikv] |                        | not(isnull(fqtest.t1.id))                                                                                                   |
|   │ └─IndexRangeScan_8          | 1.25    | cop[tikv] | table:t1, index:id(id) | range: decided by [eq(fqtest.t1.id, fqtest.t2.id)], keep order:false, stats:pseudo                                          |
|   └─TableRowIDScan_9(Probe)     | 1.25    | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                                                              |
+---------------------------------+---------+-----------+------------------------+-----------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

In cases like this where dynamic partition pruning is implemented, why does the execution plan still show partition:all? Is it a display issue? The t1 table does not need to scan all partitions; logically, it should only show a specific partition, right?

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

EXPLAIN SELECT t1.id, t1.h_record FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.id = 101;
Writing it this way works. Filtering by T2’s grade and dynamic partition pruning with T1 are done simultaneously, and then the two tables are joined. The id condition of t1 will not be included in the dynamic partition pruning of T1.

| username: 考试没答案 | Original post link

The official documentation also has similar cases, you can check it out at:

| username: 考试没答案 | Original post link

The similarity between the official one and yours is still very high.

| username: Raymond | Original post link

  1. EXPLAIN SELECT t1.id, t1.h_record FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.id = 101;
    If written this way, you can directly use SELECT t1.id, t1.h_record FROM t1 WHERE t1.id = 101;
    There’s no need to join.

  2. The way you filter through T2’s grade condition and perform dynamic partition pruning with T1 happens simultaneously, and then the two tables join. The condition on t1.id will not be included in T1’s dynamic partition pruning.
    My understanding is that the value range of t1.id comes from the id of table t2. By looking at the execution plan, you can see that t1 is the inner table in the index join, and the values of the inner table are all passed from the outer table.

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

The optimization of partition pruning rules occurs during the query plan generation phase. In scenarios where the filter conditions can only be obtained during the execution phase, partition pruning optimization cannot be utilized.

| username: Raymond | Original post link

The ability to perform dynamic partition pruning indicates that this optimization was done during the execution plan generation phase.

| username: 人如其名 | Original post link

This display is reasonable because t1.id is a join condition rather than a filter condition, which can only be determined during the execution phase. The optimizer does not know which partition this value should fall into, so all partitions are possible. Displaying “all” is not an issue.

| username: Raymond | Original post link

In that case, I can understand. Although partition pruning is possible, the execution plan generation still doesn’t know which partition to prune.

| username: h5n1 | Original post link

Displaying partition:all is normal, indicating that dynamic partition pruning is effective. Otherwise, the execution plan will show a bunch of partitions, each displayed once, followed by a bunch of unions.

| username: Raymond | Original post link

After dynamic pruning takes effect, accessing partitioned tables, such as explain select * from t1 where id < 150;, no longer requires union all. Why is that? Is there a necessary relationship with dynamic pruning?

| username: 人如其名 | Original post link

Yes, because a dynamic pruning operator can access multiple partitions. A static pruning operator can only access one partition. Therefore, static pruning can only concatenate a bunch of union all, while dynamic pruning can display multiple partition tags within a single operator.

| username: Raymond | Original post link

Got it.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.