pingcap / pingcap/tidb

Is it possibe to support Projection push down on IndexLookUp?

Open
#44,902 1 comment 0 reactions 0 assignees View on GitHub
type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**

**Describe the feature you'd like:**

```sql
set tidb_opt_projection_push_down = on;

CREATE TABLE `t` (
`i` BIGINT(11) NOT NULL,
`long_string` VARCHAR(1024) NOT NULL,
KEY `idx_str`(`long_string`(10))
);
```
```sql
mysql> explain SELECT i FROM t FORCE INDEX(`idx_str`) WHERE long_string LIKE 'a%';
+---------------------------------+---------+-----------+-------------------------------------+-------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+-------------------------------------+-------------------------------------------------+
| Projection_4 | 250.00 | root | | test.t.i |
| └─IndexLookUp_9 | 250.00 | root | | |
| ├─IndexRangeScan_6(Build) | 250.00 | cop[tikv] | table:t, index:idx_str(long_string) | range:["a","b"), keep order:false, stats:pseudo |
| └─Selection_8(Probe) | 250.00 | cop[tikv] | | like(test.t.long_string, "a%", 92) |
| └─TableRowIDScan_7 | 250.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+-------------------------------------+-------------------------------------------------+
5 rows in set (0.00 sec)
```

As we can see, the `IndexRangeScan` will return `i` and `long_string` to tidb. However, it is unnecessary to return `long_string`, which will result in a large overhead when the string is very long.
The optimal execution plan may looks like:
```sql
+-------------------------------+---------+-----------+-------------------------------------+-------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+-------------------------------------+-------------------------------------------------+
| IndexLookUp_9 | 250.00 | root | | |
| ├─Projection_4 | 250.00 | cop[tikv] | | test.t.i |
| | └─IndexRangeScan_6(Build) | 250.00 | cop[tikv] | table:t, index:idx_str(long_string) | range:["a","b"), keep order:false, stats:pseudo |
| └─Selection_8(Probe) | 250.00 | cop[tikv] | | like(test.t.long_string, "a%", 92) |
| └─TableRowIDScan_7 | 250.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+-------------------------------------+-------------------------------------------------+
```

**Describe alternatives you've considered:**

**Teachability, Documentation, Adoption, Migration Strategy:**

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.