pingcap / pingcap/tidb

planner: utilize prefix of columns in order-limit clause to avoid full table scan

Open
#48,241 3 comments 4 reactions 0 assignees View on GitHub
plan-rewrite report/customer sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
For the case below, the order property of the primary key is not completely utilized.
Actually, we can optimize the `FullScan` to a `RangeScan`, whose range should be `[the_first_value_of_a_*, the_second_value_of_a_*)`:
![677f2b6b-6a1f-481d-b457-e6060ec78a32](https://github.com/pingcap/tidb/assets/7499936/2913abb4-c876-46b7-881b-5efa8f7f2203)

Another similar case, the `TableFullScan` can be optimized to a range scan as well:
```
select distinct a, c from t order by a limit 1:
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------+
| TopN_9 | 1.00 | root | | test.t.a, offset:0, count:1 |
| └─HashAgg_18 | 8000.00 | root | | group by:test.t.a, test.t.c, funcs:firstrow(test.t.a)->test.t.a, funcs:firstrow(test.t.c)->test.t.c |
| └─TableReader_19 | 8000.00 | root | | data:HashAgg_14 |
| └─HashAgg_14 | 8000.00 | cop[tikv] | | group by:test.t.a, test.t.c, |
| └─TableFullScan_17 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------------------------+
```

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.