Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

some limit can merged with the adjacent topN #58747

Open
AilinKid opened this issue Jan 7, 2025 · 0 comments
Open

some limit can merged with the adjacent topN #58747

AilinKid opened this issue Jan 7, 2025 · 0 comments
Labels
planner/cascades issue/enhance/bug related to yams sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.

Comments

@AilinKid
Copy link
Contributor

AilinKid commented Jan 7, 2025

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
@AilinKid AilinKid added type/enhancement The issue or PR belongs to an enhancement. sig/planner SIG: Planner planner/cascades issue/enhance/bug related to yams labels Jan 7, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
planner/cascades issue/enhance/bug related to yams sig/planner SIG: Planner type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Development

No branches or pull requests

1 participant