limit and offset computation may eliminate and simplify the entire plan
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
drop table if exists t;
create table t(a int primary key, b int);
insert into t values (1, 11), (4, 44), (2, 22), (3, 33);
set session tidb_executor_concurrency = 4;
set @@session.tidb_hash_join_concurrency = 5;
set @@session.tidb_distsql_scan_concurrency = 15;
mysql> explain select a from (select a from t order by a limit 3 offset 5) as t1 order by a limit 3 offset 5;
+------------------------------+---------+-----------+---------------+-------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+---------+-----------+---------------+-------------------------------+
| Limit_17 | 3.00 | root | | offset:5, count:3 |
| └─Limit_41 | 3.00 | root | | offset:5, count:3 |
| └─TableReader_46 | 4.00 | root | | data:Limit_45 |
| └─Limit_45 | 4.00 | cop[tikv] | | offset:0, count:8 |
| └─TableFullScan_26 | 4.00 | cop[tikv] | table:t | keep order:true, stats:pseudo |
+------------------------------+---------+-----------+---------------+-------------------------------+
5 rows in set (0.01 sec)
```
after the subQ's application, we could say the result set is about 3 rows at most, while from the outer query block's imply, current all rows could be eliminated and abandoned for another offset 5 which is impossible, we can generate a table dual with row=0 directly.
TableDual_11 0.00 root rows:0
Contributor guide
Assessment
This issue has not been assessed yet.