pingcap / pingcap/tidb

planner: index predicates based on column prefix are not pushed down to index side if they can't be used to construct scan ranges

Open
#61,990 3 comments 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
report/customer sig/planner type/question
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

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.