planner: cannot choose the best predicate to build IndexRangeScan for correlated queries in some cases
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
create table t_small (a int, b int);
create table t_big (a int, b int, key(a));
explain select 1 from t_small
where (select /*+ no_decorrelate() */ sum(b) from t_big
where t_big.a=t_small.a
and t_big.a BETWEEN 2 and 100000000000
) = 0;
+--------------------------------------------+------------+-----------+-------------------------+--------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------+------------+-----------+-------------------------+--------------------------------------------------------+
| Projection_11 | 10000.00 | root | | 1->Column#8 |
| └─Apply_13 | 10000.00 | root | | CARTESIAN inner join |
| ├─TableReader_15(Build) | 10000.00 | root | | data:TableFullScan_14 |
| │ └─TableFullScan_14 | 10000.00 | cop[tikv] | table:t_small | keep order:false, stats:pseudo |
| └─Selection_16(Probe) | 8000.00 | root | | eq(Column#7, 0) |
| └─MaxOneRow_17 | 10000.00 | root | | |
| └─StreamAgg_22 | 10000.00 | root | | funcs:sum(Column#16)->Column#7 |
| └─Projection_50 | 2500.00 | root | | cast(test.t_big.b, decimal(10,0) BINARY)->Column#16 |
| └─IndexLookUp_49 | 2500.00 | root | | |
| ├─Selection_48(Build) | 2500.00 | cop[tikv] | | eq(test.t_big.a, test.t_small.a) |
| │ └─IndexRangeScan_46 | 2500000.00 | cop[tikv] | table:t_big, index:a(a) | range:[2,100000000000], keep order:false, stats:pseudo |
| └─TableRowIDScan_47(Probe) | 2500.00 | cop[tikv] | table:t_big | keep order:false, stats:pseudo |
+--------------------------------------------+------------+-----------+-------------------------+--------------------------------------------------------+
```
In the case above, the optimizer chose `t_big.a BETWEEN 2 and 100000000000` to build the access range to access `t_big`, but obviously, using `t_big.a=t_small.a` is better in this case.
The reason is that if there are multiple CNF predicates on the same index and the optimizer cannot merge them into one, then it will choose the best CNF predicate from them, but in this case, it chose the wrong one. (see the function extractBestCNFItemRanges https://github.com/pingcap/tidb/blob/master/pkg/util/ranger/detacher.go#L244)
Contributor guide
Assessment
This issue has not been assessed yet.