pingcap / pingcap/tidb

some limit can merged with the adjacent topN

Open
#58,747 0 comments 0 reactions 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;

explain select b from (select b from t order by b limit 10 offset 10) as t1 order by b limit 10 offset 5;
mysql> explain select b from (select b from t order by b limit 10 offset 10) as t1 order by b limit 10 offset 5;
+------------------------------+---------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+---------+-----------+---------------+--------------------------------+
| Limit_17 | 4.00 | root | | offset:5, count:10 |
| └─TopN_33 | 4.00 | root | | test.t.b, offset:10, count:10 |
| └─TableReader_39 | 4.00 | root | | data:TopN_38 |
| └─TopN_38 | 4.00 | cop[tikv] | | test.t.b, offset:0, count:20 |
| └─TableFullScan_23 | 4.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+------------------------------+---------+-----------+---------------+--------------------------------+
5 rows in set (0.00 sec)

the top limit can be merged with the adjacent topN as `topN:offset(15):count(5)`

TopN_14 5.00 root planner__cascades__integration.t.b, offset:15, count:5
└─TableReader_16 20.00 root data:TopN_17
└─TopN_17 20.00 cop[tikv] planner__cascades__integration.t.b, offset:0, count:20
└─TableFullScan_19 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.