Planner fails to push down window function to TiFlash
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```sql
WITH `star_history` AS (
SELECT
`repo_name`,
`created_at`,
`stars`
FROM
`github_repos`
)
SELECT
`repo_name`,
`created_at`,
`stars`,
rank() over (
PARTITION by `repo_name`
ORDER BY
`created_at`
) AS `prev_stars`
FROM
`star_history`
ORDER BY
`repo_name`,
`created_at`;
```
The plan doesn't push window function to TiFlash
```sql
+--------------------------+--------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+--------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------+
| Projection_32 | 325380316.00 | root | | gharchive_dev.github_repos.repo_name, gharchive_dev.github_repos.created_at, gharchive_dev.github_repos.stars, Column#47 |
| └─Window_34 | 325380316.00 | root | | rank()->Column#47 over(partition by gharchive_dev.github_repos.repo_name order by gharchive_dev.github_repos.created_at) |
| └─Sort_27 | 325380316.00 | root | | gharchive_dev.github_repos.repo_name, gharchive_dev.github_repos.created_at |
| └─TableReader_26 | 325380316.00 | root | | data:TableFullScan_25 |
| └─TableFullScan_25 | 325380316.00 | cop[tiflash] | table:github_repos | keep order:false |
+--------------------------+--------------+--------------+--------------------+--------------------------------------------------------------------------------------------------------------------------+
```
The order by column is the same as the partition and sort column of window. In order to avoid re-sorting, we choose to do the plan alternative of sort and window in TiDB. We currently do not support the gather merge operator, which requires sorting to be done in TiDB. At the same time, TiFlash's window is multi-threaded, and the output does not guarantee global order.
We should support gather merge operator, so that TiFlash does partial sort in parallel, the TiDB merge and keep the order so that we don't do the sort in a single TiDB node.
If we remove the `ORDER BY` clause, or change it to `ORDER BY created_at, repo_name`, it can push window to TiFlash:
```sql
explain
with `star_history` as ( select `repo_name` , `created_at` , `stars` from `github_repos` )
select `repo_name` , `created_at` , `stars` ,
rank() over(partition by repo_name order by created_at ) as prev_stars
from star_history
order by created_at, repo_name;
+----------------------------------+--------------+--------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+--------------+--------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_11 | 325380316.00 | root | | gharchive_dev.github_repos.created_at, gharchive_dev.github_repos.repo_name |
| └─TableReader_31 | 325380316.00 | root | | data:ExchangeSender_30 |
| └─ExchangeSender_30 | 325380316.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_14 | 325380316.00 | mpp[tiflash] | | gharchive_dev.github_repos.repo_name, gharchive_dev.github_repos.created_at, gharchive_dev.github_repos.stars, Column#47, stream_count: 8 |
| └─Window_29 | 325380316.00 | mpp[tiflash] | | rank()->Column#47 over(partition by gharchive_dev.github_repos.repo_name order by gharchive_dev.github_repos.created_at), stream_count: 8 |
| └─Sort_20 | 325380316.00 | mpp[tiflash] | | gharchive_dev.github_repos.repo_name, gharchive_dev.github_repos.created_at, stream_count: 8 |
| └─ExchangeReceiver_19 | 325380316.00 | mpp[tiflash] | | stream_count: 8 |
| └─ExchangeSender_18 | 325380316.00 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: gharchive_dev.github_repos.repo_name, collate: utf8mb4_bin], stream_count: 8 |
| └─TableFullScan_17 | 325380316.00 | mpp[tiflash] | table:github_repos | keep order:false |
+----------------------------------+--------------+--------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.