Skip to content

Commit 4ca40d9

Browse files
SubhamSinghalalamb
andauthored
feat: enable datafusion.optimizer.enable_window_topn by default (apache#27197)
## Which issue does this PR close? - Closes apache#24608. ## Rationale for this change `enable_window_topn` has been off since apache#21479 because of high-cardinality regressions against the sort plan. The work items in apache#24608 are now done: - apache#23096, - apache#25730, - apache#25968, - apache#26133 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=false` for the baseline, same binary, Apple M4 Max: | Query | function, partitions | off (ms) | on (ms) | change | |---|---|---:|---:|---| | Q13 | ROW_NUMBER, id2 | 127.4 | 25.8 | 4.93x faster | | Q14 | ROW_NUMBER, 100 | 190.8 | 35.9 | 5.32x faster | | Q15 | ROW_NUMBER, 1K | 173.6 | 35.7 | 4.86x faster | | Q16 | ROW_NUMBER, 10K | 149.7 | 33.0 | 4.54x faster | | Q17 | ROW_NUMBER, 100K | 151.1 | 49.1 | 3.08x faster | | Q18 | RANK, 100 | 226.3 | 28.8 | 7.86x faster | | Q19 | RANK, 1K | 186.0 | 29.6 | 6.28x faster | | Q20 | RANK, 1K, heavy ties | 209.8 | 57.1 | 3.67x faster | | Q21 | RANK, id2 | 174.2 | 25.4 | 6.87x faster | | Q22 | RANK, id2, heavy ties | 190.5 | 37.2 | 5.13x faster | | Q23 | RANK, 100K | 188.1 | 63.5 | 2.96x faster | | Q24 | DENSE_RANK, 100 | 248.4 | 38.6 | 6.43x faster | | Q25 | DENSE_RANK, 1K | 199.6 | 36.9 | 5.40x faster | | Q26 | DENSE_RANK, 1K, heavy ties | 232.2 | 43.3 | 5.37x faster | | Q27 | DENSE_RANK, id2 | 211.3 | 45.7 | 4.63x faster | | Q28 | DENSE_RANK, id2, heavy ties | 208.7 | 53.6 | 3.89x faster | | Q29 | DENSE_RANK, 100K | 205.8 | 123.3 | 1.67x faster | ## What changes are included in this PR? - `enable_window_topn` defaults to `true`; its doc comment now lists ROW_NUMBER, RANK and DENSE_RANK and drops the high-cardinality warning. - `window_topn.slt` restores the setting with `RESET` instead of `SET ... = false`. - `configs.md` and `information_schema.slt` regenerated. - The h2o `window_sorted` settings 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_integration` pass with only the `information_schema.slt` default change; `qualify.slt`'s `rank <= 3` / `dense_rank <= 2` cases now run through `PartitionedTopKExec` and return the same results. `window_topn.slt` still covers the disabled path explicitly. ## Are there any user-facing changes? Yes. Top-K-per-partition queries (`rn <= K`, `rn < K` over ROW_NUMBER / RANK / DENSE_RANK) plan with `PartitionedTopKExec` instead of a full sort, so `EXPLAIN` output changes; results don't. Set `datafusion.optimizer.enable_window_topn = false` to get the old plan. --------- Co-authored-by: Andrew Lamb <[email protected]>
1 parent 7f83621 commit 4ca40d9

5 files changed

Lines changed: 13 additions & 16 deletions

File tree

‎benchmarks/sql_benchmarks/h2o/init/window_sorted_settings.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,8 @@
44
--
55
-- enable_window_topn turns on the WindowTopN rewrite, making the baseline the
66
-- existing per-partition top-K operator rather than an unoptimized
7-
-- window-plus-filter plan. The flag defaults to false, so without this the
8-
-- subgroup would measure a different plan shape than its name claims.
7+
-- window-plus-filter plan. The flag is set explicitly so the subgroup keeps
8+
-- measuring the plan shape its name claims even if the default changes.
99
--
1010
-- information_schema is enabled so the template can assert the flags actually
1111
-- took effect.

‎datafusion/common/src/config.rs‎

Lines changed: 4 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1659,13 +1659,10 @@ config_namespace! {
16591659
pub enable_window_limits: bool, default = true
16601660

16611661
/// When set to true, the optimizer will replace
1662-
/// Filter(rn<=K) → Window(ROW_NUMBER) → Sort patterns with a
1663-
/// PartitionedTopKExec that maintains per-partition heaps, avoiding
1664-
/// a full sort of the input.
1665-
/// When the window partition key has low cardinality, enabling this optimization
1666-
/// can improve performance. However, for high cardinality keys, it may
1667-
/// cause regressions in both memory usage and runtime.
1668-
pub enable_window_topn: bool, default = false
1662+
/// Filter(rn<=K) → Window(ROW_NUMBER, RANK or DENSE_RANK) → Sort
1663+
/// patterns with a PartitionedTopKExec that keeps only the top K rows
1664+
/// of each window partition, avoiding a full sort of the input.
1665+
pub enable_window_topn: bool, default = true
16691666

16701667
/// When set to true, the optimizer will rewrite a "top-1 per group"
16711668
/// pattern of the form `Filter(row_number() = 1)` over a `PARTITION BY` window into an

‎datafusion/sqllogictest/test_files/information_schema.slt‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -318,7 +318,7 @@ datafusion.optimizer.enable_topk_dynamic_filter_pushdown true
318318
datafusion.optimizer.enable_topk_repartition true
319319
datafusion.optimizer.enable_unions_to_filter false
320320
datafusion.optimizer.enable_window_limits true
321-
datafusion.optimizer.enable_window_topn false
321+
datafusion.optimizer.enable_window_topn true
322322
datafusion.optimizer.expand_views_at_output false
323323
datafusion.optimizer.filter_null_join_keys false
324324
datafusion.optimizer.hash_join_inlist_pushdown_max_distinct_values 150
@@ -488,7 +488,7 @@ datafusion.optimizer.enable_topk_dynamic_filter_pushdown true When set to true,
488488
datafusion.optimizer.enable_topk_repartition true When set to true, the optimizer will push TopK (Sort with fetch) below hash repartition when the partition key is a prefix of the sort key, reducing data volume before the shuffle.
489489
datafusion.optimizer.enable_unions_to_filter false When set to true, the logical optimizer will rewrite `UNION DISTINCT` branches that read from the same source and differ only by filter predicates into a single branch with a combined filter. This optimization is conservative and only applies when the branches share the same source and compatible wrapper nodes such as identical projections or aliases.
490490
datafusion.optimizer.enable_window_limits true When set to true, the optimizer will attempt to push limit operations past window functions, if possible
491-
datafusion.optimizer.enable_window_topn false When set to true, the optimizer will replace Filter(rn<=K) → Window(ROW_NUMBER) → Sort patterns with a PartitionedTopKExec that maintains per-partition heaps, avoiding a full sort of the input. When the window partition key has low cardinality, enabling this optimization can improve performance. However, for high cardinality keys, it may cause regressions in both memory usage and runtime.
491+
datafusion.optimizer.enable_window_topn true When set to true, the optimizer will replace Filter(rn<=K) → Window(ROW_NUMBER, RANK or DENSE_RANK) → Sort patterns with a PartitionedTopKExec that keeps only the top K rows of each window partition, avoiding a full sort of the input.
492492
datafusion.optimizer.expand_views_at_output false When set to true, if the returned type is a view type then the output will be coerced to a non-view. Coerces `Utf8View` to `LargeUtf8`, and `BinaryView` to `LargeBinary`.
493493
datafusion.optimizer.filter_null_join_keys false When set to true, the optimizer will insert filters before a join between a nullable and non-nullable column to filter out nulls on the nullable side. This filter can add additional overhead when the file format does not fully support predicate push down.
494494
datafusion.optimizer.hash_join_inlist_pushdown_max_distinct_values 150 Maximum number of distinct values (rows) in the build side of a hash join to be pushed down as an InList expression for dynamic filtering. Build sides with more rows than this will use hash table lookups instead. Set to 0 to always use hash table lookups. This provides an additional limit beyond `hash_join_inlist_pushdown_max_size` to prevent very large IN lists that might not provide much benefit over hash table lookups. This uses the deduplicated row count once the build side has been evaluated. The default is 150 values per partition. This is inspired by Trino's `max-filter-keys-per-column` setting. See: <https://trino.io/docs/current/admin/dynamic-filtering.html#dynamic-filter-collection-thresholds>

‎datafusion/sqllogictest/test_files/window_topn.slt‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1481,9 +1481,9 @@ SELECT id, pk, val FROM (
14811481
statement ok
14821482
DROP TABLE window_topn_dr_null_t;
14831483

1484-
# Reset config to default (false)
1484+
# Reset config to default
14851485
statement ok
1486-
SET datafusion.optimizer.enable_window_topn = false;
1486+
RESET datafusion.optimizer.enable_window_topn;
14871487

14881488
statement ok
14891489
create table t(c1 int, c2 int) as values (1, 2), (3, 4);
@@ -1527,7 +1527,7 @@ statement ok
15271527
set datafusion.execution.batch_size = 8192;
15281528

15291529
statement ok
1530-
set datafusion.optimizer.enable_window_topn = false;
1530+
RESET datafusion.optimizer.enable_window_topn;
15311531

15321532
# The filter's column index is resolved through the projection between the
15331533
# filter and the window. Here the outer query drops `v`, so the projection
@@ -1686,7 +1686,7 @@ statement ok
16861686
SET datafusion.explain.physical_plan_only = false;
16871687

16881688
statement ok
1689-
SET datafusion.optimizer.enable_window_topn = false;
1689+
RESET datafusion.optimizer.enable_window_topn;
16901690

16911691
statement ok
16921692
DROP TABLE window_topn_shift;

‎docs/source/user-guide/configs.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -143,7 +143,7 @@ The following configuration settings are available:
143143
| datafusion.optimizer.enable_round_robin_repartition | true | When set to true, the physical plan optimizer will try to add round robin repartitioning to increase parallelism to leverage more CPU cores |
144144
| datafusion.optimizer.enable_topk_aggregation | true | When set to true, the optimizer will attempt to perform limit operations during aggregations, if possible |
145145
| datafusion.optimizer.enable_window_limits | true | When set to true, the optimizer will attempt to push limit operations past window functions, if possible |
146-
| datafusion.optimizer.enable_window_topn | false | When set to true, the optimizer will replace Filter(rn<=K) → Window(ROW_NUMBER) → Sort patterns with a PartitionedTopKExec that maintains per-partition heaps, avoiding a full sort of the input. When the window partition key has low cardinality, enabling this optimization can improve performance. However, for high cardinality keys, it may cause regressions in both memory usage and runtime. |
146+
| datafusion.optimizer.enable_window_topn | true | When set to true, the optimizer will replace Filter(rn<=K) → Window(ROW_NUMBER, RANK or DENSE_RANK) → Sort patterns with a PartitionedTopKExec that keeps only the top K rows of each window partition, avoiding a full sort of the input. |
147147
| datafusion.optimizer.enable_row_number_to_aggregate | false | When set to true, the optimizer will rewrite a "top-1 per group" pattern of the form `Filter(row_number() = 1)` over a `PARTITION BY` window into an `Aggregate(first_value(... ORDER BY ...) GROUP BY partition)`. This avoids buffering / fully sorting the input, which is especially beneficial on wide payloads. Disabled by default because on nested/wide value types the aggregate only becomes memory efficient once the columnar nested-type `GroupsAccumulator` fast path is available; enabling it without that support can route wide payloads onto the slow per-group accumulator path. |
148148
| datafusion.optimizer.enable_topk_repartition | true | When set to true, the optimizer will push TopK (Sort with fetch) below hash repartition when the partition key is a prefix of the sort key, reducing data volume before the shuffle. |
149149
| datafusion.optimizer.enable_topk_dynamic_filter_pushdown | true | When set to true, the optimizer will attempt to push down TopK dynamic filters into the file scan phase. |

0 commit comments

Comments
 (0)