You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/09/03 03:45:29 UTC
(datafusion) branch gh-readonly-queue/main/pr-24732-6ffc45d0815f5f1e0b912cf5f42b34a5ab236dce created (now 17115f368d)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-24732-6ffc45d0815f5f1e0b912cf5f42b34a5ab236dce
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at 17115f368d bench(h2o): window Top-N sweep over a declared-sorted input (#24732)
This branch includes the following new commits:
new 17115f368d bench(h2o): window Top-N sweep over a declared-sorted input (#24732)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: bench(h2o): window Top-N sweep over a declared-sorted input (#24732)
Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-24732-6ffc45d0815f5f1e0b912cf5f42b34a5ab236dce
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 17115f368dbdf0f21428c0991c3f8ec11f96c090
Author: Subham Singhal <Su...@users.noreply.github.com>
AuthorDate: Thu Sep 3 03:44:55 2026 +0000
bench(h2o): window Top-N sweep over a declared-sorted input (#24732)
## Which issue does this PR close?
Related to #6899 — adds benchmark coverage for the WindowTopN operators.
## Rationale for this change
The existing `h2o --subgroup window` Top-N sweep (q13–q29) registers `x`
with no declared ordering, so `output_ordering()` is `None`. Any plan
that depends on the input being sorted is unreachable from those
queries, however the data happens to be laid out on disk. This adds a
`window_sorted` subgroup that publishes the same sweep over a `WITH
ORDER` table, so ordering-dependent plans can be measured.
## What changes are included in this PR?
A `window_sorted` h2o subgroup: 16 queries covering ROW_NUMBER / RANK /
DENSE_RANK × 100 / 1K / 10K / 100K partitions, plus heavy-ties variants.
- Two `load` directives: the existing
`load_window_${SIZE}_${FORMAT}.sql` creates `x`, then a new script
writes a sorted copy via `COPY (... ORDER BY pk, ob DESC)` and registers
it `WITH ORDER (pk ASC, ob DESC)`. Reusing the existing loader keeps
both the `--size` and `--format` axes working with no duplication.
`load` is untimed, so the sort stays out of the measurement.
- `WITH ORDER` can only name columns, so the partition key (`id3 % N`)
and the tie expression are materialized as `pk` and `ob`.
- Asserts guard the three ways this could silently measure nothing: both
config flags took effect, the sorted copy holds every source row, and
`ob` has the cardinality the query name claims.
- `expect_plan PartitionedTopKExec` is deliberately the shared
substring, so the same file validates the heap operator and any
streaming variant that replaces it.
No Rust changes; benchmark files only.
## Are these changes tested?
`benchmark_runner h2o --subgroup window_sorted` runs all 16 queries
green. Row counts are exact: 2 per partition for the distinct-ORDER-BY
shapes, and 1,010,812 (RANK) / 2,020,722 (DENSE_RANK) for the tie shapes
— the top-1 and top-2 of 10 distinct values over 10M rows.
## Are there any user-facing changes?
No.
---
benchmarks/bench.sh | 76 +++++++++++++++++
.../h2o/benchmarks/window_sorted/q01.benchmark | 13 +++
.../h2o/benchmarks/window_sorted/q02.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q03.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q04.benchmark | 12 +++
.../h2o/benchmarks/window_sorted/q05.benchmark | 15 ++++
.../h2o/benchmarks/window_sorted/q06.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q07.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q08.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q09.benchmark | 17 ++++
.../h2o/benchmarks/window_sorted/q10.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q11.benchmark | 13 +++
.../h2o/benchmarks/window_sorted/q12.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q13.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q14.benchmark | 11 +++
.../h2o/benchmarks/window_sorted/q15.benchmark | 13 +++
.../h2o/benchmarks/window_sorted/q16.benchmark | 11 +++
benchmarks/sql_benchmarks/h2o/h2o.suite | 4 +
.../sql_benchmarks/h2o/init/load_window_sorted.sql | 40 +++++++++
.../h2o/init/window_sorted_cleanup.sql | 1 +
.../h2o/init/window_sorted_settings.sql | 24 ++++++
.../h2o/window_sorted.benchmark.template | 99 ++++++++++++++++++++++
22 files changed, 437 insertions(+)
diff --git a/benchmarks/bench.sh b/benchmarks/bench.sh
index df9b7f6c94..419fd5be3a 100755
--- a/benchmarks/bench.sh
+++ b/benchmarks/bench.sh
@@ -139,6 +139,12 @@ h2o_big_join: h2oai benchmark with large dataset (1e9 rows) fo
h2o_small_window: Extended h2oai benchmark with small dataset (1e7 rows) for window, default file format is csv
h2o_medium_window: Extended h2oai benchmark with medium dataset (1e8 rows) for window, default file format is csv
h2o_big_window: Extended h2oai benchmark with large dataset (1e9 rows) for window, default file format is csv
+h2o_small_window_sorted: Window Top-N over a declared-sorted h2o input, small dataset (1e7 rows), default file format is csv
+h2o_medium_window_sorted: Window Top-N over a declared-sorted h2o input, medium dataset (1e8 rows), default file format is csv
+h2o_big_window_sorted: Window Top-N over a declared-sorted h2o input, large dataset (1e9 rows), default file format is csv
+h2o_small_window_sorted_parquet: Window Top-N over a declared-sorted h2o input, small dataset (1e7 rows), source file format is parquet
+h2o_medium_window_sorted_parquet: Window Top-N over a declared-sorted h2o input, medium dataset (1e8 rows), source file format is parquet
+h2o_big_window_sorted_parquet: Window Top-N over a declared-sorted h2o input, large dataset (1e9 rows), source file format is parquet
h2o_small_parquet: h2oai benchmark with small dataset (1e7 rows) for groupby, file format is parquet
h2o_medium_parquet: h2oai benchmark with medium dataset (1e8 rows) for groupby, file format is parquet
h2o_big_parquet: h2oai benchmark with large dataset (1e9 rows) for groupby, file format is parquet
@@ -309,6 +315,26 @@ main() {
h2o_big_window)
data_h2o_join "BIG" "CSV"
;;
+ # the sorted window subgroup derives its data from the same
+ # source, then sorts it inside its load SQL
+ h2o_small_window_sorted)
+ data_h2o_join "SMALL" "CSV"
+ ;;
+ h2o_medium_window_sorted)
+ data_h2o_join "MEDIUM" "CSV"
+ ;;
+ h2o_big_window_sorted)
+ data_h2o_join "BIG" "CSV"
+ ;;
+ h2o_small_window_sorted_parquet)
+ data_h2o_join "SMALL" "PARQUET"
+ ;;
+ h2o_medium_window_sorted_parquet)
+ data_h2o_join "MEDIUM" "PARQUET"
+ ;;
+ h2o_big_window_sorted_parquet)
+ data_h2o_join "BIG" "PARQUET"
+ ;;
h2o_small_parquet)
data_h2o "SMALL" "PARQUET"
;;
@@ -534,6 +560,24 @@ main() {
h2o_big_window)
run_h2o_window "BIG" "CSV" "window"
;;
+ h2o_small_window_sorted)
+ run_h2o_window_sorted "small" "csv"
+ ;;
+ h2o_medium_window_sorted)
+ run_h2o_window_sorted "medium" "csv"
+ ;;
+ h2o_big_window_sorted)
+ run_h2o_window_sorted "big" "csv"
+ ;;
+ h2o_small_window_sorted_parquet)
+ run_h2o_window_sorted "small" "parquet"
+ ;;
+ h2o_medium_window_sorted_parquet)
+ run_h2o_window_sorted "medium" "parquet"
+ ;;
+ h2o_big_window_sorted_parquet)
+ run_h2o_window_sorted "big" "parquet"
+ ;;
h2o_small_parquet)
run_h2o "SMALL" "PARQUET"
;;
@@ -1266,6 +1310,38 @@ run_h2o_window() {
h2o_runner "$1" "$2" "window"
}
+# Runs the h2o window_sorted subgroup: window Top-N over an input that declares
+# the ordering the window requires.
+#
+# The `window` subgroup registers `x` with no declared ordering, so
+# `output_ordering()` is None and any plan that depends on a declared ordering is
+# unreachable from it, however the data happens to sit on disk. This subgroup's
+# load SQL writes a sorted copy and registers it `WITH ORDER`, then asserts a
+# per-partition top-K operator is actually in the plan so a silent fallback to
+# window-plus-filter cannot masquerade as a result.
+#
+# The sort happens in the untimed `load` step, so it stays out of the
+# measurement. Data comes from the same source as the window subgroup, so the
+# data step is data_h2o_join.
+#
+# H2O_FILE_TYPE selects the *source* format and so only affects that untimed
+# load: the measured query always reads the sorted Parquet copy, whatever the
+# source was. A Parquet source still makes the load markedly cheaper than
+# re-parsing a multi-GB CSV, which is why the `_parquet` entries exist; they are
+# expected to produce the same measured numbers as their CSV counterparts, not
+# different ones. Each entry pairs with the data step that generates its format.
+run_h2o_window_sorted() {
+ SIZE=${1:-"small"}
+ FILE_TYPE=${2:-"csv"}
+ echo "Running h2o window_sorted benchmark (size=${SIZE}, source format=${FILE_TYPE})..."
+ debug_run env BENCH_NAME=h2o \
+ BENCH_SUBGROUP=window_sorted \
+ H2O_BENCH_SIZE="${SIZE}" \
+ H2O_FILE_TYPE="${FILE_TYPE}" \
+ ${QUERY:+BENCH_QUERY="${QUERY}"} \
+ bash -c "$SQL_CARGO_COMMAND"
+}
+
# Runs the external aggregation benchmark
run_external_aggr() {
# Use TPC-H SF1 dataset
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q01.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q01.benchmark
new file mode 100644
index 0000000000..236220b759
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q01.benchmark
@@ -0,0 +1,13 @@
+# ROW_NUMBER top-2 per partition over the declared-sorted input, swept across
+# partition cardinality. This mirrors the `window` subgroup q14-q17 sweep, with
+# the partition key materialized as a column so the ordering can be declared.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=01
+FN=ROW_NUMBER
+PK_MOD=100
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q01_row_number_100_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q02.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q02.benchmark
new file mode 100644
index 0000000000..a9c5733d62
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q02.benchmark
@@ -0,0 +1,11 @@
+# ROW_NUMBER top-2, ~1K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=02
+FN=ROW_NUMBER
+PK_MOD=1000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q02_row_number_1k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q03.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q03.benchmark
new file mode 100644
index 0000000000..4a000af56b
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q03.benchmark
@@ -0,0 +1,11 @@
+# ROW_NUMBER top-2, ~10K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=03
+FN=ROW_NUMBER
+PK_MOD=10000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q03_row_number_10k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q04.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q04.benchmark
new file mode 100644
index 0000000000..77208a2024
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q04.benchmark
@@ -0,0 +1,12 @@
+# ROW_NUMBER top-2, ~100K partitions. Fewest rows per partition on the sweep,
+# so per-partition bookkeeping is amortized over the least work.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=04
+FN=ROW_NUMBER
+PK_MOD=100000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q04_row_number_100k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q05.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q05.benchmark
new file mode 100644
index 0000000000..e06acffd9a
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q05.benchmark
@@ -0,0 +1,15 @@
+# RANK top-2 per partition, mirroring the ROW_NUMBER sweep above. RANK retains
+# boundary ties, so `WHERE rnk <= 2` can keep more than 2 rows per partition and
+# the operator has to track the tied set rather than a fixed row count. `v2` is
+# near-unique, so these four measure RANK with the tie machinery idle; q09-q10
+# drive it.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=05
+FN=RANK
+PK_MOD=100
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q05_rank_100_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q06.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q06.benchmark
new file mode 100644
index 0000000000..d9421902b4
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q06.benchmark
@@ -0,0 +1,11 @@
+# RANK top-2, ~1K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=06
+FN=RANK
+PK_MOD=1000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q06_rank_1k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q07.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q07.benchmark
new file mode 100644
index 0000000000..7a2a49ebce
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q07.benchmark
@@ -0,0 +1,11 @@
+# RANK top-2, ~10K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=07
+FN=RANK
+PK_MOD=10000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q07_rank_10k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q08.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q08.benchmark
new file mode 100644
index 0000000000..409ed17bba
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q08.benchmark
@@ -0,0 +1,11 @@
+# RANK top-2, ~100K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=08
+FN=RANK
+PK_MOD=100000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q08_rank_100k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q09.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q09.benchmark
new file mode 100644
index 0000000000..9728f47081
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q09.benchmark
@@ -0,0 +1,17 @@
+# RANK top-2 with heavy ties: 10 distinct ORDER BY values, so a whole partition
+# slice ties at rank 1 and every tied row survives `rnk <= 2`. The tied set, not
+# the top-K bound, dominates the work.
+#
+# The cast is load-bearing. `v2` is Float64, so `v2 % 10` is float modulo and
+# stays near-unique — it produces no ties whatsoever. Truncating to an integer
+# first is what actually collapses the domain to 10 values.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=09
+FN=RANK
+PK_MOD=1000
+OB_EXPR=CAST(v2 AS BIGINT) % 10
+OB_BOUND=count(DISTINCT ob) = 10
+NAME=window_sorted_q09_rank_1k_partitions_heavy_ties
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q10.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q10.benchmark
new file mode 100644
index 0000000000..232e3f1b3b
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q10.benchmark
@@ -0,0 +1,11 @@
+# RANK top-2, ~10K partitions, heavy ties.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=10
+FN=RANK
+PK_MOD=10000
+OB_EXPR=CAST(v2 AS BIGINT) % 10
+OB_BOUND=count(DISTINCT ob) = 10
+NAME=window_sorted_q10_rank_10k_partitions_heavy_ties
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q11.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q11.benchmark
new file mode 100644
index 0000000000..d5d5d6b256
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q11.benchmark
@@ -0,0 +1,13 @@
+# DENSE_RANK top-2 per partition, mirroring the RANK sweep. DENSE_RANK keeps
+# every row whose ORDER BY value is among the 2 distinct-greatest in the
+# partition, so rows kept per partition is unbounded in rows-per-distinct-value.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=11
+FN=DENSE_RANK
+PK_MOD=100
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q11_dense_rank_100_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q12.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q12.benchmark
new file mode 100644
index 0000000000..c8d277ff04
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q12.benchmark
@@ -0,0 +1,11 @@
+# DENSE_RANK top-2, ~1K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=12
+FN=DENSE_RANK
+PK_MOD=1000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q12_dense_rank_1k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q13.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q13.benchmark
new file mode 100644
index 0000000000..be7246232c
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q13.benchmark
@@ -0,0 +1,11 @@
+# DENSE_RANK top-2, ~10K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=13
+FN=DENSE_RANK
+PK_MOD=10000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q13_dense_rank_10k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q14.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q14.benchmark
new file mode 100644
index 0000000000..9c773a93ea
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q14.benchmark
@@ -0,0 +1,11 @@
+# DENSE_RANK top-2, ~100K partitions.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=14
+FN=DENSE_RANK
+PK_MOD=100000
+OB_EXPR=v2
+OB_BOUND=count(DISTINCT ob) > 1000
+NAME=window_sorted_q14_dense_rank_100k_partitions
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q15.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q15.benchmark
new file mode 100644
index 0000000000..3a655c0b4d
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q15.benchmark
@@ -0,0 +1,13 @@
+# DENSE_RANK top-2, ~1K partitions, heavy ties. With 10 distinct ORDER BY values
+# the top-2 distinct values cover roughly a fifth of each partition, so this is
+# the heaviest surviving-row count on the sweep. See q09 on why the cast matters.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=15
+FN=DENSE_RANK
+PK_MOD=1000
+OB_EXPR=CAST(v2 AS BIGINT) % 10
+OB_BOUND=count(DISTINCT ob) = 10
+NAME=window_sorted_q15_dense_rank_1k_partitions_heavy_ties
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q16.benchmark b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q16.benchmark
new file mode 100644
index 0000000000..65baaed851
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/benchmarks/window_sorted/q16.benchmark
@@ -0,0 +1,11 @@
+# DENSE_RANK top-2, ~10K partitions, heavy ties.
+
+subgroup window_sorted
+
+template sql_benchmarks/h2o/window_sorted.benchmark.template
+QPAD=16
+FN=DENSE_RANK
+PK_MOD=10000
+OB_EXPR=CAST(v2 AS BIGINT) % 10
+OB_BOUND=count(DISTINCT ob) = 10
+NAME=window_sorted_q16_dense_rank_10k_partitions_heavy_ties
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/h2o.suite b/benchmarks/sql_benchmarks/h2o/h2o.suite
index 27d83285ba..cb2314b13c 100644
--- a/benchmarks/sql_benchmarks/h2o/h2o.suite
+++ b/benchmarks/sql_benchmarks/h2o/h2o.suite
@@ -31,3 +31,7 @@ description = "Run H2O window query 3."
[[examples]]
command = "cargo run --release --bin benchmark_runner -- h2o --subgroup join --size medium -f parquet"
description = "Run the H2O join queries with the medium Parquet dataset."
+
+[[examples]]
+command = "cargo run --release --bin benchmark_runner -- h2o --subgroup window_sorted"
+description = "Run the window Top-N sweep over an input that declares its ordering."
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/init/load_window_sorted.sql b/benchmarks/sql_benchmarks/h2o/init/load_window_sorted.sql
new file mode 100644
index 0000000000..6e8f2a67b7
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/init/load_window_sorted.sql
@@ -0,0 +1,40 @@
+-- Materializes a pre-sorted copy of the h2o window table that *declares* its
+-- ordering, for the `window_sorted` subgroup.
+--
+-- Why this exists: the `window` subgroup partitions by an expression
+-- (`id3 % N`) and, in its heavy-ties queries, orders by another (`v2 % 10`).
+-- `WITH ORDER` can only name columns, so both are materialized here as `pk`
+-- and `ob`. The queries then partition and order by plain columns, and the
+-- declared ordering can satisfy the window's requirement.
+--
+-- Parameters, supplied by window_sorted.benchmark.template so each benchmark
+-- writes only the shape it measures:
+-- PK_MOD partition count (100, 1000, 10000, 100000)
+-- OB_EXPR ORDER BY value expression (`v2` distinct, integer-cast for ties)
+--
+-- Note `v2` is Float64 here, so a plain `v2 % 10` is float modulo and stays
+-- near-unique. Producing genuine ties needs an integer cast first; the template
+-- asserts ob's resulting cardinality so a query cannot get this wrong silently.
+--
+-- The ORDER BY is load-bearing: the file has to be written in (pk, ob DESC)
+-- order for the WITH ORDER declaration below to be true. A declaration that
+-- does not match the file is not a planning error, it silently produces wrong
+-- results, so this ORDER BY and that WITH ORDER must be edited together.
+--
+-- `load` runs before `init` and is not timed, so this sort never enters the
+-- measurement.
+DROP TABLE IF EXISTS x_sorted;
+
+COPY (
+ SELECT id3 % ${PK_MOD:-1000} AS pk, ${OB_EXPR:-v2} AS ob
+ FROM x
+ WHERE v2 IS NOT NULL
+ ORDER BY pk ASC, ob DESC
+)
+TO 'sql_benchmarks/h2o/scratch/window_sorted.parquet'
+STORED AS PARQUET;
+
+CREATE EXTERNAL TABLE x_sorted
+STORED AS PARQUET
+WITH ORDER (pk ASC, ob DESC)
+LOCATION 'sql_benchmarks/h2o/scratch/window_sorted.parquet';
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/init/window_sorted_cleanup.sql b/benchmarks/sql_benchmarks/h2o/init/window_sorted_cleanup.sql
new file mode 100644
index 0000000000..9601519d48
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/init/window_sorted_cleanup.sql
@@ -0,0 +1 @@
+DROP TABLE IF EXISTS x_sorted;
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/init/window_sorted_settings.sql b/benchmarks/sql_benchmarks/h2o/init/window_sorted_settings.sql
new file mode 100644
index 0000000000..7ed8722614
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/init/window_sorted_settings.sql
@@ -0,0 +1,24 @@
+-- Session settings for the h2o window_sorted subgroup. `init` runs after
+-- `load`, so these apply to the measured query and not to the COPY that writes
+-- the sorted file.
+--
+-- enable_window_topn turns on the WindowTopN rewrite, making the baseline the
+-- existing per-partition top-K operator rather than an unoptimized
+-- window-plus-filter plan. The flag defaults to false, so without this the
+-- subgroup would measure a different plan shape than its name claims.
+--
+-- information_schema is enabled so the template can assert the flags actually
+-- took effect.
+--
+-- prefer_existing_sort decides *how* a declared input ordering is honored once
+-- some operator requires it. The heap top-K operator requires no input ordering,
+-- so on its own the ordering is simply dropped: the hash repartition that
+-- co-locates each partition key is inserted with preserve_order: false, and that
+-- is not a defect. For an operator that does require the ordering, the planner
+-- then has two ways to satisfy it — an order-preserving repartition, or a
+-- SortExec that re-sorts data already sorted on disk. This setting picks the
+-- former. Without it such an operator would measure a full sort and the sorted
+-- input would be pointless.
+set datafusion.catalog.information_schema = true;
+set datafusion.optimizer.enable_window_topn = true;
+set datafusion.optimizer.prefer_existing_sort = true;
\ No newline at end of file
diff --git a/benchmarks/sql_benchmarks/h2o/window_sorted.benchmark.template b/benchmarks/sql_benchmarks/h2o/window_sorted.benchmark.template
new file mode 100644
index 0000000000..9170511492
--- /dev/null
+++ b/benchmarks/sql_benchmarks/h2o/window_sorted.benchmark.template
@@ -0,0 +1,99 @@
+# Shared template for the h2o `window_sorted` subgroup: WindowTopN over an
+# input that *declares* the ordering the window requires.
+#
+# Parameters set by each qNN.benchmark:
+# QPAD zero-padded query id
+# FN window function (ROW_NUMBER, RANK, DENSE_RANK)
+# PK_MOD partition count (100, 1000, 10000, 100000)
+# OB_EXPR ORDER BY value expression (`v2` distinct, integer-cast for ties)
+# OB_BOUND SQL predicate pinning ob's cardinality, asserted below
+# NAME display name
+#
+# Why this subgroup exists: the `window` subgroup registers `x` with no declared
+# ordering, so `output_ordering()` is None and the planner cannot know the input
+# is sorted. Any operator that requires a declared ordering is therefore
+# unreachable from those queries, however the data happens to be laid out on
+# disk. This subgroup republishes the same sweep over a `WITH ORDER` table so
+# the ordering-dependent plans are reachable.
+#
+# The first `load` is the existing unsorted loader, which creates `x`; the
+# second reads `x` and writes the sorted `x_sorted`. Reusing the existing loader
+# keeps both the `--size` and `--format` axes working with no duplication.
+
+load sql_benchmarks/h2o/init/load_window_${H2O_BENCH_SIZE:-small}_${H2O_FILE_TYPE:-csv}.sql
+
+load sql_benchmarks/h2o/init/load_window_sorted.sql
+
+init sql_benchmarks/h2o/init/window_sorted_settings.sql
+
+name ${NAME}
+group h2o
+
+echo Loading ${H2O_BENCH_SIZE:-small} window ${H2O_FILE_TYPE:-csv} h2o data, sorted by (pk, ob DESC) over ${PK_MOD} partitions
+
+# Guard against a silent config no-op: with the rewrite off the plan is a plain
+# window plus filter, and the subgroup would measure something other than what
+# its name claims.
+assert I
+SELECT value = 'true' FROM information_schema.df_settings WHERE name = 'datafusion.optimizer.enable_window_topn';
+----
+true
+
+# Same guard for the setting that decides an ordering-requiring operator gets an
+# order-preserving repartition instead of a redundant SortExec.
+assert I
+SELECT value = 'true' FROM information_schema.df_settings WHERE name = 'datafusion.optimizer.prefer_existing_sort';
+----
+true
+
+# The sorted copy must hold every non-null-v2 row of the source. This catches a
+# COPY that silently wrote a partial file, which would otherwise show up only as
+# a suspiciously fast run.
+assert I
+SELECT count(*) = (SELECT count(*) FROM x WHERE v2 IS NOT NULL) FROM x_sorted;
+----
+true
+
+# The ORDER BY column really has the cardinality this query's name claims. A
+# tie-shape query whose `ob` turns out near-unique measures the no-ties case
+# under a heavy-ties name, and nothing else in the run would show it: the timings
+# stay plausible and only the output row count quietly collapses to K per
+# partition. `v2` is Float64, so this is a live trap — `v2 % 10` is float modulo
+# and yields no ties at all.
+assert I
+SELECT ${OB_BOUND} FROM x_sorted;
+----
+true
+
+# The per-partition top-K operator has to be in the plan, otherwise the
+# declared ordering was not picked up and this is just a window-plus-filter
+# measurement wearing the subgroup's name.
+#
+# `PartitionedTopKExec` is deliberately the string asserted rather than a
+# fully-qualified operator name: it is also a substring of the streaming
+# variant's name, so this same check holds on a branch where a streaming
+# operator replaces the heap one for declared-sorted input. That is the point of
+# the subgroup — the comparison is this file's numbers across two branches, so
+# the assertion must not be the thing that differs between them.
+expect_plan PartitionedTopKExec
+
+# `PartitionedTopKExec` alone only proves the plan has an operator that
+# distributes by the partition key; that operator requires no input ordering,
+# so it appears whether or not the scan below it declares one. Assert the
+# scan's own declared ordering too, so a `WITH ORDER` that silently stops
+# reaching `DataSourceExec` (e.g. a rewrite that drops it, or a future change
+# to `load_window_sorted.sql`) fails loudly here instead of just being absent
+# from a plan that still "passes" the check above.
+expect_plan output_ordering=[pk@0 ASC NULLS LAST, ob@1 DESC]
+
+run
+-- Top-2 per partition by ${FN} over the declared-sorted input.
+SELECT pk, ob FROM (
+ SELECT pk, ob,
+ ${FN}() OVER (PARTITION BY pk ORDER BY ob DESC) AS rnk
+ FROM x_sorted
+) sub_query WHERE rnk <= 2;
+
+result sql_benchmarks/h2o/results/window_sorted/${H2O_BENCH_SIZE:-small}/q${QPAD}.csv
+
+cleanup sql_benchmarks/h2o/init/window_sorted_cleanup.sql
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org