Is it possibe to support Projection push down on IndexLookUp?
- 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
Assessment
This issue has not been assessed yet.