planner: index predicates based on column prefix are not pushed down to index side if they can't be used to construct scan ranges
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
```
create table t (
a int,
b int,
url varchar(20),
d int,
key k(a, b, url(10))
);
explain select * from t where a=1 and url='0123';
+-------------------------------+---------+-----------+-----------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+-----------------------------+---------------------------------------------+
| IndexLookUp_8 | 1.00 | root | | |
| ├─IndexRangeScan_5(Build) | 10.00 | cop[tikv] | table:t, index:k(a, b, url) | range:[1,1], keep order:false, stats:pseudo |
| └─Selection_7(Probe) | 1.00 | cop[tikv] | | eq(managedcatalog.t.url, "0123") |
| └─TableRowIDScan_6 | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+-----------------------------+---------------------------------------------+
```
In the query above, `url='0123'` cannot be used to construct the scan range since predicates on `b` is missing and `b` is in front of `url` in index columns.
In this case, the optimizer still should push `url='0123'` to the index side, but it doesn't, and finally this plan is sub-optimal and leads more double-read requests.
### 2. What did you expect to see? (Required)
The predicate `url='0123'` should be pushed down to the index side.
### 3. What did you see instead (Required)
The predicate is pushed down to table side.
### 4. What is your TiDB version? (Required)
Master.
Contributor guide
Assessment
This issue has not been assessed yet.