pingcap/tidb
GitHub で見るplanner: predicates cannot be pushed into sub-queries for HashJoin
Open
#36,551 opened on 2022年7月26日
feature/reviewinggood first issuehelp wantedsig/plannertype/enhancement
Repository metrics
- Stars
- (40,090 stars)
- PR merge metrics
- (平均マージ 14d 4h) (30d で 346 merged PRs)
説明
Enhancement
mysql> CREATE TABLE t1 (
-> risk_id varchar(32) NOT NULL,
-> tran_id int NOT NULL,
-> KEY (`risk_id`)
-> );
mysql> CREATE TABLE t2 (
-> risk_id varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
-> -- risk_id varchar(32) NOT NULL,
-> tran_id int NOT NULL,
-> KEY (`risk_id`)
-> );
mysql> explain select /*+ hash_join(t1) */ t1.risk_id
-> from t1
-> where t1.risk_id = 'xxxx'
-> and exists (select 1 from t2 where t2.risk_id=t1.risk_id);
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
| HashJoin_18 | 8.00 | root | | semi join, equal:[eq(test.t1.risk_id, test.t2.risk_id)] |
| ├─IndexReader_24(Build) | 10000.00 | root | | index:IndexFullScan_23 |
| │ └─IndexFullScan_23 | 10000.00 | cop[tikv] | table:t2, index:risk_id(risk_id) | keep order:false, stats:pseudo |
| └─IndexReader_20(Probe) | 10.00 | root | | index:IndexRangeScan_19 |
| └─IndexRangeScan_19 | 10.00 | cop[tikv] | table:t1, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
The predicate t1.risk_id = 'xxxx' should be propagated to t2 and the optimizer should generate a IndexRangeScan for t2.
If remove COLLATE utf8mb4_general_ci, this predicate can be pushed down.
mysql> CREATE TABLE t2 (
-> -- risk_id varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
-> risk_id varchar(32) NOT NULL,
-> tran_id int NOT NULL,
-> KEY (`risk_id`)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> explain select /*+ hash_join(t1) */ t1.risk_id
-> from t1
-> where t1.risk_id = 'xxxx'
-> and exists (select 1 from t2 where t2.risk_id=t1.risk_id);
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
| HashJoin_21 | 8.00 | root | | semi join, equal:[eq(test.t1.risk_id, test.t2.risk_id)] |
| ├─IndexReader_25(Build) | 10.00 | root | | index:IndexRangeScan_24 |
| │ └─IndexRangeScan_24 | 10.00 | cop[tikv] | table:t2, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo |
| └─IndexReader_23(Probe) | 10.00 | root | | index:IndexRangeScan_22 |
| └─IndexRangeScan_22 | 10.00 | cop[tikv] | table:t1, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo |
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+