pingcap / pingcap/tidb

filter on `row_number()` can be used on optimize `window_sort` to `window_topN`

Open
#39,792 2 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
```
mysql> desc test;
+-------+---------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+------+---------+-------+
| id | int(11) | YES | | NULL | |
| value | date | YES | | NULL | |
+-------+---------+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql> explain select * from (select *, row_number() over (order by value) as rn from test) d where rn < 10;
+------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------+
| TableReader_30 | 1.60 | root | | data:ExchangeSender_29 |
| └─ExchangeSender_29 | 1.60 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Selection_28 | 1.60 | mpp[tiflash] | | lt(Column#5, 10) |
| └─Window_25 | 2.00 | mpp[tiflash] | | row_number()->Column#5 over(order by test.test.value rows between current row and current row) |
| └─Sort_17 | 2.00 | mpp[tiflash] | | test.test.value |
| └─ExchangeReceiver_16 | 2.00 | mpp[tiflash] | | |
| └─ExchangeSender_15 | 2.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─TableFullScan_14 | 2.00 | mpp[tiflash] | table:test | keep order:false, stats:pseudo |
+------------------------------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
```
As shown above, for window function row_number(), there is a common usage that user only wants to get the first N rows, so user will add a filter on the results of row_number().
However, TiDB's plan for this kind of query requires global sort on all data set, if the orignal data set is big, the performance is bad.
A potential optimization is the optimizer push `where rn < 100` to the sort operation, and convert `sort` to `topn`

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.