Repository navigation
feat: enable datafusion.optimizer.enable_window_topn by default - #27197
Merged
Merged
Conversation
github-actions
Bot
added
documentation
Contributor
|
run benchmark h2o_small_window_parquet |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_small_window_parquetResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_small_window_parquetCPU Details (lscpu)Details
Resource Usageh2o_small_window_parquet — base (merge-base)
h2o_small_window_parquet — branch
File an issue against this benchmark runner |
Contributor
|
run benchmarks |
Contributor
|
run benchmark h2o_small_parquet h2o_medium_parquet h2o_medium_window_parquet |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark clickbench_partitionedResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark tpcdsResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark tpchResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_medium_window_parquetResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_medium_parquetResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark running (GKE) | trigger CPU Details (lscpu)Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_small_parquetResults will be posted here when complete File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark tpchCPU Details (lscpu)Details
Resource Usagetpch — base (merge-base)
tpch — branch
File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_small_parquetCPU Details (lscpu)Details
Resource Usageh2o_small_parquet — base (merge-base)
h2o_small_parquet — branch
File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark tpcdsCPU Details (lscpu)Details
Resource Usagetpcds — base (merge-base)
tpcds — branch
File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_medium_parquetCPU Details (lscpu)Details
Resource Usageh2o_medium_parquet — base (merge-base)
h2o_medium_parquet — branch
File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark clickbench_partitionedCPU Details (lscpu)Details
Resource Usageclickbench_partitioned — base (merge-base)
clickbench_partitioned — branch
File an issue against this benchmark runner |
|
🤖 Benchmark completed (GKE) | trigger Instance: Comparing window-topn-default-on (29ee2ad) to 4bf32cb (merge-base) diff Run configurationrun benchmark h2o_medium_window_parquetCPU Details (lscpu)Details
Resource Usageh2o_medium_window_parquet — base (merge-base)
h2o_medium_window_parquet — branch
File an issue against this benchmark runner |
alamb
approved these changes
Nov 11, 2026
alamb
left a comment
alamb
left a comment
Contributor
There was a problem hiding this comment.
thank you so much for this work @SubhamSinghal -- this is great
I did a final test locally just to give it a try
CREATE EXTERNAL TABLE hits STORED AS PARQUET
LOCATION 'benchmarks/data/hits_partitioned';Here is the query
SELECT count(*) FROM (
SELECT "RegionID", "EventTime", "WatchID",
ROW_NUMBER() OVER (PARTITION BY "RegionID" ORDER BY "EventTime" DESC) AS rn
FROM hits
) WHERE rn <= 3;Then enabled via
SET datafusion.optimizer.enable_window_topn = true;On main (before this PR): Elapsed 3.671 seconds.
With the flag: Elapsed 0.246 seconds. 😎
And the explain plan shows
| | ┌─────────────┴─────────────┐ |
| | │ PartitionedTopKExec │ |
| | │ -------------------- │ |
| | │ emit: │ |
| | │ [row_number() PARTITION BY│ |
| | │ [hits.RegionID] ORDER BY │ |
| | │ [hits.EventTime DESC │ |
| | │ NULLS FIRST] RANGE │ |
| | │ BETWEEN UNBOUNDED │ |
| | │ PRECEDING AND │ |
| | │ CURRENT ROW] │ |
| | │ │ |
| | │ fetch: 3 │ |
| | │ fn: row_number │ |
| | │ │ |
| | │ order: │ |
| | │ [EventTime@0 DESC] │ |
| | │ │ |
| | │ partition: │ |
| | │ [RegionID@1] │ |
| | └─────────────┬─────────────┘ |Also, it would be sweet if we got a writeup of this feature in time for datafusion-57
github-merge-queue
Bot
removed this pull request from the merge queue due to failed status checks
Nov 13, 2026
github-merge-queue
Bot
removed this pull request from the merge queue due to failed status checks
Nov 13, 2026
alamb
enabled auto-merge
November 13, 2026 21:05
github-merge-queue
Bot
removed this pull request from the merge queue due to failed status checks
Nov 13, 2026
Contributor
SubhamSinghal
force-pushed
the
window-topn-default-on
branch
from
November 14, 2026 08:40
97a94cd to
2d15352
Compare
Contributor
Author
|
Resolved build failure. |
Contributor
|
Thank you @SubhamSinghal -- this is epic |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Which issue does this PR close?
datafusion.optimizer.enable_window_topn) by default #24608.Rationale for this change
enable_window_topnhas been off since #21479 because of high-cardinality regressions against the sort plan. The work items in #24608 are now done:The h2o window benchmark's Top-N queries (
benchmarks/queries/h2o/window.sql, Q13–Q29, small dataset,J1_1e7_1e7_NA.parquet) are now faster with the flag on in every case, up to 100K partitions and including the heavy-ties variants.dfbench h2o --iterations 5, median,DATAFUSION_OPTIMIZER_ENABLE_WINDOW_TOPN=falsefor the baseline, same binary, Apple M4 Max:What changes are included in this PR?
enable_window_topndefaults totrue; its doc comment now lists ROW_NUMBER, RANK and DENSE_RANK and drops the high-cardinality warning.window_topn.sltrestores the setting withRESETinstead ofSET ... = false.configs.mdandinformation_schema.sltregenerated.window_sortedsettings comment no longer says the flag defaults to false (it still sets it explicitly).What is the testing strategy for this PR?
Existing coverage. With the flag on by default, the whole sqllogictest suite and
core_integrationpass with only theinformation_schema.sltdefault change;qualify.slt'srank <= 3/dense_rank <= 2cases now run throughPartitionedTopKExecand return the same results.window_topn.sltstill covers the disabled path explicitly.Are there any user-facing changes?
Yes. Top-K-per-partition queries (
rn <= K,rn < Kover ROW_NUMBER / RANK / DENSE_RANK) plan withPartitionedTopKExecinstead of a full sort, soEXPLAINoutput changes; results don't. Setdatafusion.optimizer.enable_window_topn = falseto get the old plan.