select * from (select number, rank() over( partition by number % 3 order by number ) c from numbers(1000000) ) where c < 3;
🐳 :) explain pipeline select * from (select number, rank() over( partition by number % 3 order by number ) c from numbers(1000000) ) where c < 3;
-[ EXPLAIN ]-----------------------------------
CompoundBlockOperator(Project) × 16
TransformFilter × 16
Transform Window × 16
TransformWindowPartitionSort × 16
TransformWindowPartitionSpillReader × 16
Merge to Resize × 16
Merge to TransformWindowPartitionBucket × 1
TransformWindowPartitionSpillWriter × 16
TransformWindowPartitionScatter × 16
CompoundBlockOperator(Map) × 16
NumbersSourceTransform × 16
11 rows explain in 0.044 sec. Processed 0 rows, 0B (0 row/s, 0B/s)
Since we only need Top 3 rank results partitioned by number % 3 and order by number, we can introduce a TopK window operator after CompoundBlockOperator and filter the results in advance.
Summary
Description for this feature.
Example Query:
Since we only need Top 3 rank results partitioned by
number % 3
and order by number, we can introduce a TopK window operator afterCompoundBlockOperator
and filter the results in advance.This optimization also works for Q44 of TPCDS.