support index join for range partition table

[zz-jason]

Description

Create a range partitioned table t:

create table t(a bigint, b bigint, index idx_a(a)) partition by range(a) (partition p0 values less than (10), partition p1 values less than (20));

Join itself using the TIDB_INLJ() SQL hint, you can find that the execution plan is not IndexJoin:

TiDB(root@127.0.0.1:test) > explain select /*+ TIDB_INLJ(t1, t2) */ * from t t1 join t t2 on t1.a = t2.a;
+----------------------------------+----------+-----------+------------------------+--------------------------------------------+
| id                               | estRows  | task      | access object          | operator info                              |
+----------------------------------+----------+-----------+------------------------+--------------------------------------------+
| HashJoin_12                      | 24975.00 | root      |                        | inner join, equal:[eq(test.t.a, test.t.a)] |
| ├─Union_27(Build)                | 19980.00 | root      |                        |                                            |
| │ ├─TableReader_30               | 9990.00  | root      |                        | data:Selection_29                          |
| │ │ └─Selection_29               | 9990.00  | cop[tikv] |                        | not(isnull(test.t.a))                      |
| │ │   └─TableFullScan_28         | 10000.00 | cop[tikv] | table:t2, partition:p0 | keep order:false, stats:pseudo             |
| │ └─TableReader_36               | 9990.00  | root      |                        | data:Selection_35                          |
| │   └─Selection_35               | 9990.00  | cop[tikv] |                        | not(isnull(test.t.a))                      |
| │     └─TableFullScan_34         | 10000.00 | cop[tikv] | table:t2, partition:p1 | keep order:false, stats:pseudo             |
| └─Union_14(Probe)                | 19980.00 | root      |                        |                                            |
|   ├─TableReader_17               | 9990.00  | root      |                        | data:Selection_16                          |
|   │ └─Selection_16               | 9990.00  | cop[tikv] |                        | not(isnull(test.t.a))                      |
|   │   └─TableFullScan_15         | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo             |
|   └─TableReader_23               | 9990.00  | root      |                        | data:Selection_22                          |
|     └─Selection_22               | 9990.00  | cop[tikv] |                        | not(isnull(test.t.a))                      |
|       └─TableFullScan_21         | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo             |
+----------------------------------+----------+-----------+------------------------+--------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

This task aims to support index join on range partitioned table.

Score

• 10672

Mentor(s)

• @winoros

Contact the mentors: #tidb-challenge-program channel in TiDB Community Slack Workspace

Recommended Skills

• TiDB SQL Optimizer

Learning Materials

• Chinese: TiDB 精选技术讲解文章
• English: Awesome-Database-Learning

[winoros]

This can be closed now? @tiancaiamao @lysu

[lysu]

@winoros now #18862 solve mainly the problem

but it still some improvement like this https://github.com/pingcap/tidb/pull/18862/files/6cafb7c8ca39bbcaaaad4e4ec3a7d3e9162fddad#r462291952 are WIP by @imtbkcat

maybe we can close after all known improve done

[crazycs520]

This issue can be closed now? @lysu @tiancaiamao

[lysu]

This issue can be closed now? @lysu @tiancaiamao

maybe still couldn't, because new implement is disable by default and it won't shipped with 5.0, maybe need waiting 5.X or 6.0 😞

[qw4990]

Close since this problem can be solved by #24150.

[zz-jason]

confirmed:

TiDB(root@127.0.0.1:test) > set @@tidb_partition_prune_mode='dynamic';
Query OK, 0 rows affected (0.00 sec)

TiDB(root@127.0.0.1:test) > create table t(a bigint, b bigint, index idx_a(a)) partition by range(a) (partition p0 values less than (10), partition p1 values less than (20));
Query OK, 0 rows affected (0.00 sec)

TiDB(root@127.0.0.1:test) > explain select /*+ TIDB_INLJ(t1, t2) */ * from t t1 join t t2 on t1.a = t2.a;
+---------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object            | operator info                                                                                               |
+---------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------------------------------+
| IndexJoin_12                    | 12487.50 | root      |                          | inner join, inner:IndexLookUp_11, outer key:test.t.a, inner key:test.t.a, equal cond:eq(test.t.a, test.t.a) |
| ├─TableReader_24(Build)         | 9990.00  | root      | partition:all            | data:Selection_23                                                                                           |
| │ └─Selection_23                | 9990.00  | cop[tikv] |                          | not(isnull(test.t.a))                                                                                       |
| │   └─TableFullScan_22          | 10000.00 | cop[tikv] | table:t1                 | keep order:false, stats:pseudo                                                                              |
| └─IndexLookUp_11(Probe)         | 1.25     | root      | partition:all            |                                                                                                             |
|   ├─Selection_10(Build)         | 1.25     | cop[tikv] |                          | not(isnull(test.t.a))                                                                                       |
|   │ └─IndexRangeScan_8          | 1.25     | cop[tikv] | table:t2, index:idx_a(a) | range: decided by [eq(test.t.a, test.t.a)], keep order:false, stats:pseudo                                  |
|   └─TableRowIDScan_9(Probe)     | 1.25     | cop[tikv] | table:t2                 | keep order:false, stats:pseudo                                                                              |
+---------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

TiDB version:

TiDB(root@127.0.0.1:test) > select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-2704-g734084fa2
Edition: Community
Git Commit Hash: 734084fa2bf2b2e9ba465e83c019418ee1d305a2
Git Branch: master
UTC Build Time: 2021-04-22 12:02:52
GoVersion: go1.15.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)