peter-toth commented on code in PR #57602:
URL: https://github.com/apache/spark/pull/57602#discussion_r3688446688
##########
docs/sql-performance-tuning.md:
##########
@@ -168,6 +168,31 @@ SELECT /*+ REBALANCE_BY_SIZE('128m', c) */ * FROM t;
For more details please refer to the documentation of [Partitioning
Hints](sql-ref-syntax-qry-select-hints.html#partitioning-hints).
+## Tuning Window Functions
+
+Top-k window queries (such as filtering on <code>row_number</code>,
<code>rank</code> or
+<code>dense_rank</code>) plan a window group limit on both sides of the
shuffle: a partial one
+before the shuffle to reduce the data feeding it, and a final one after. The
following configuration
+tunes that behavior.
+
+<table class="spark-config">
+ <thead><tr><th>Property Name</th><th>Default</th><th>Meaning</th><th>Since
Version</th></tr></thead>
+ <tr>
+ <td><code>spark.sql.execution.bypassPartialWindowGroupLimit</code></td>
+ <td>false</td>
+ <td>
+ When true, skips the pre-shuffle partial window group limit for
partitioned top-k window
+ queries and runs only a single window group limit after the shuffle.
Bypassing the partial
+ window group limit can improve performance when the pre-shuffle
reduction ratio is low. The
Review Comment:
**Finding 6.** This is the claim the whole PR rests on, and nothing in the
PR measures it.
`sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/TopKBenchmark.scala`
already benchmarks this exact shape -- 20M rows in 11 partitions, `PARTITION
BY b` and unpartitioned, all three rank-like functions,
`WINDOW_GROUP_LIMIT_THRESHOLD` on and off -- with checked-in results under
`sql/core/benchmarks/TopKBenchmark-results.txt`, so the harness is free.
Worth noting that its partitioned case sits at the wrong end of the range
for this flag: `id % 1024 as b` over 20M rows in 11 partitions is ~1.8k rows
per key per input partition against `limit = 200`, so the partial prunes ~90%
and the bypass should lose there. The workload the config targets is the
opposite -- more keys than the partial can prune, i.e. fewer rows per key per
input partition than `limit`:
```scala
spark.range(0, N, 1, 11).selectExpr("id as a", "id % 1024 as b", "id %
4000000 as c")
...
Seq("PARTITION BY b", "PARTITION BY c").foreach { partition =>
Seq(false, true).foreach { bypass =>
benchmark.addCase(s"$function ($partition, bypassPartial: $bypass)") { _
=>
withSQLConf(BYPASS_PARTIAL_WINDOW_GROUP_LIMIT.key -> bypass.toString) {
f(function, partition)
}
}
}
}
```
A pair of numbers from each end answers the question a reader of this config
doc actually has -- "is my reduction ratio low enough" -- and shows whether
skipping the pre-shuffle sort pays for the bigger shuffle when the partial does
prune.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
