Window sort support limit offset

[evenyag]

What type of enhancement is this?

Performance

What does the enhancement do?

Now window sort only supports limit without offset.

mysql> explain analyze select ts from cpu order by ts desc limit 1, 100;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stage | node | plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     0 |    0 |  MergeScanExec: peers=[4398046511104(1024, 0), ] metrics=[output_rows: 100, greptime_exec_read_cost: 0, ready_time: 1367518, first_consume_time: 2405064866, finish_time: 2405182055, ]
                                                                                                                                                                                                                                                                                   |
|     1 |    0 |  GlobalLimitExec: skip=1, fetch=100 metrics=[output_rows: 100, elapsed_compute: 1112, ]
  SortExec: TopK(fetch=101), expr=[ts@0 DESC], preserve_partitioning=[false] metrics=[output_rows: 101, elapsed_compute: 1542010568, row_replacements: 26402, ]
    SeqScan: region=4398046511104(1024, 0), partition_count=1 (0 memtable ranges, 6 file 1016 ranges) metrics=[output_rows: 103680000, mem_used: 1058324480, elapsed_poll: 626485335, elapsed_await: 809918628, ]
 |
|  NULL | NULL | Total rows: 100                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (2.43 sec)

Support offset can reduce fetching all rows.

Implementation challenges

No response

[LYZJU2019]

Can I give it a try?

[evenyag]

@waynexia Are you working on this?

[waynexia]

Sorry for the delay, I have written some code on this ticket but haven't noticed the update on this issue 😢 Are you willing to give a review when it's ready @LYZJU2019 ?

[LYZJU2019]

Sure! Will try

[waynexia]

FYI: #5018

[evenyag]

Offset is already supported in the latest main, I guess #4960 already fixed this. 🥵