pingcap / pingcap/tidb

planner: cannot choose the best predicate to build IndexRangeScan for correlated queries in some cases

Open
#51,148 0 comments 0 reactions 0 assignees View on GitHub
plan-rewrite sig/planner type/enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.