pingcap / pingcap/tidb

limit and offset computation may eliminate and simplify the entire plan

Open
#58,749 0 comments 1 reaction 0 assignees View on GitHub
planner/cascades sig/planner type/enhancement
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

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.