You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "beliefer (via GitHub)" <gi...@apache.org> on 2023/02/23 11:13:47 UTC
[GitHub] [spark] beliefer opened a new pull request, #40142: [SPARK-41171][SQL] Infer window limit and push down it through window when partitionSpec is empty
beliefer opened a new pull request, #40142:
URL: https://github.com/apache/spark/pull/40142
### What changes were proposed in this pull request?
Sometimes, the SQL exists filter which condition compares the window function `ROW_NUMBER()` with number. For example,
```
SELECT *,
ROW_NUMBER() OVER(ORDER BY a) AS rn
FROM Tab1
WHERE rn <= 5
```
We can create a `Limit` as the parent node of `Window` node. Such as: `Limit(Literal(5), Window)` and the optimizer rule `LimitPushDownThroughWindow` will push down the `Limit` as the child of `Window`. After this optimization, Spark executes top n and reduce the data size before shuffle. We can consider the SQL adjusted as the below.
```
SELECT *,
ROW_NUMBER() OVER(ORDER BY a) AS rn
FROM
(SELECT *
FROM Tab1
ORDER BY a LIMIT 5) t
```
In short, it supports following pattern:
```
SELECT (... row_number()
OVER (
ORDER BY ... ) AS rn)
WHERE rn (==|<|<=) k
AND other conditions
```
### Why are the changes needed?
Improve the performance by infer window limit and push down it through window when partitionSpec is empty.
### Does this PR introduce _any_ user-facing change?
'No'.
New feature.
### How was this patch tested?
The top n (Limit + Sort) have better performance than WindowGroupLimit if the window function is RowNumber and Window partitionSpec is empty.
The new micro benchmark
```
Java HotSpot(TM) 64-Bit Server VM 1.8.0_311-b11 on Mac OS X 10.16
Intel(R) Core(TM) i7-9750H CPU @ 2.60GHz
Benchmark Top-K: Best Time(ms) Avg Time(ms) Stdev(ms) Rate(M/s) Per Row(ns) Relative
-----------------------------------------------------------------------------------------------------------------------------------------------
ROW_NUMBER (PARTITION: , WindowGroupLimit: false) 11054 11684 406 1.9 527.1 1.0X
ROW_NUMBER (PARTITION: , WindowGroupLimit: true) 1737 1772 23 12.1 82.8 6.4X
ROW_NUMBER (PARTITION: PARTITION BY b, WindowGroupLimit: false) 24570 24698 97 0.9 1171.6 0.4X
ROW_NUMBER (PARTITION: PARTITION BY b, WindowGroupLimit: true) 6645 6916 219 3.2 316.9 1.7X
RANK (PARTITION: , WindowGroupLimit: false) 11590 11917 237 1.8 552.7 1.0X
RANK (PARTITION: , WindowGroupLimit: true) 2697 2865 107 7.8 128.6 4.1X
RANK (PARTITION: PARTITION BY b, WindowGroupLimit: false) 25357 25476 94 0.8 1209.1 0.4X
RANK (PARTITION: PARTITION BY b, WindowGroupLimit: true) 6625 6755 92 3.2 315.9 1.7X
DENSE_RANK (PARTITION: , WindowGroupLimit: false) 11876 12137 184 1.8 566.3 0.9X
DENSE_RANK (PARTITION: , WindowGroupLimit: true) 2678 2961 147 7.8 127.7 4.1X
DENSE_RANK (PARTITION: PARTITION BY b, WindowGroupLimit: false) 25407 27601 700 0.8 1211.5 0.4X
DENSE_RANK (PARTITION: PARTITION BY b, WindowGroupLimit: true) 6841 7008 184 3.1 326.2 1.6X
```
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] beliefer commented on pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
Posted by "beliefer (via GitHub)" <gi...@apache.org>.
beliefer commented on PR #40142:
URL: https://github.com/apache/spark/pull/40142#issuecomment-1447849095
@cloud-fan @dongjoon-hyun Thank you very much!
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] beliefer commented on a diff in pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
Posted by "beliefer (via GitHub)" <gi...@apache.org>.
beliefer commented on code in PR #40142:
URL: https://github.com/apache/spark/pull/40142#discussion_r1135208242
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -130,6 +130,7 @@ abstract class Optimizer(catalogManager: CatalogManager)
val operatorOptimizationBatch: Seq[Batch] = {
Batch("Operator Optimization before Inferring Filters", fixedPoint,
operatorOptimizationRuleSet: _*) ::
+ Batch("Infer window group limit", Once, InferWindowGroupLimit) ::
Review Comment:
Got it.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan closed pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan closed pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
URL: https://github.com/apache/spark/pull/40142
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] beliefer commented on pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
Posted by "beliefer (via GitHub)" <gi...@apache.org>.
beliefer commented on PR #40142:
URL: https://github.com/apache/spark/pull/40142#issuecomment-1442784894
ping @cloud-fan cc @wangyum
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on PR #40142:
URL: https://github.com/apache/spark/pull/40142#issuecomment-1447623059
thanks, merging to master!
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] [spark] cloud-fan commented on a diff in pull request #40142: [SPARK-41171][SQL] Infer and push down window limit through window if partitionSpec is empty
Posted by "cloud-fan (via GitHub)" <gi...@apache.org>.
cloud-fan commented on code in PR #40142:
URL: https://github.com/apache/spark/pull/40142#discussion_r1134795703
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -130,6 +130,7 @@ abstract class Optimizer(catalogManager: CatalogManager)
val operatorOptimizationBatch: Seq[Batch] = {
Batch("Operator Optimization before Inferring Filters", fixedPoint,
operatorOptimizationRuleSet: _*) ::
+ Batch("Infer window group limit", Once, InferWindowGroupLimit) ::
Review Comment:
I think this is an unrelated change and is actually a regression. We may infer filters and it's better to run window limit optimization as late as possible. @beliefer can we revert this change about moving the rule?
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org