You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "r7raul1984 (via GitHub)" <gi...@apache.org> on 2025/10/09 01:47:31 UTC
[PR] [SPARK-53742][SQL] Push down the filter used in the count_if function [spark]
r7raul1984 opened a new pull request, #52554:
URL: https://github.com/apache/spark/pull/52554
### What changes were proposed in this pull request?
By pushing down the filter condition in the count_if function, we can reduce the volume of data that needs to be processed.
```
spark.sql("create table t1(a int, b int, c int) using parquet")
spark.sql("select count_if(a <>1) from t1").explain("cost")
```
Current:
```
== Optimized Logical Plan ==
Aggregate [count(if (NOT _common_expr_0#6) null else _common_expr_0#6) AS count_if((NOT (a = 1)))#4L], Statistics(sizeInBytes=16.0 B, rowCount=1)
+- Project [NOT (a#0 = 1) AS _common_expr_0#6], Statistics(sizeInBytes=1.0 B)
+- Relation spark_catalog.default.t1[a#0,b#1,c#2] parquet, Statistics(sizeInBytes=0.0 B)
```
After this pr:
```
== Optimized Logical Plan ==
Aggregate [count(if (NOT _common_expr_2#22) null else _common_expr_2#22) AS count_if((NOT (a = 1)))#21L], Statistics(sizeInBytes=16.0 B, rowCount=1)
+- Project [NOT (a#3 = 1) AS _common_expr_2#22], Statistics(sizeInBytes=1.0 B)
+- Filter (isnotnull(a#3) AND NOT (a#3 = 1)), Statistics(sizeInBytes=1.0 B)
+- Relation spark_catalog.default.t1[a#3,b#4,c#5] parquet, Statistics(sizeInBytes=0.0 B)
```
### Why are the changes needed?
Push down the conditional filter expressions in COUNT_IF to improve query performance.
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Unit test.
### Was this patch authored or co-authored using generative AI tooling?
No.
--
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
Re: [PR] [SPARK-53742][SQL] Push down the filter used in the count_if function [spark]
Posted by "r7raul1984 (via GitHub)" <gi...@apache.org>.
r7raul1984 commented on code in PR #52554:
URL: https://github.com/apache/spark/pull/52554#discussion_r2416285543
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -2776,6 +2777,71 @@ object RemoveLiteralFromGroupExpressions extends Rule[LogicalPlan] {
}
}
+/**
+ * To optimize performance, filter conditions should be pushed down in [[CountIf]] queries
+ * to reduce the volume of data being processed.
+ *
+ * Input Query :
+ * {{{
+ * SELECT count_if(c1 > 1) FROM ut1
+ * }}}
+ *
+ * Rewritten Query:
+ * {{{
+ * SELECT count_if(c1 > 1) FROM ut1 where c1 > 1
Review Comment:
When there is only one count_if, it can indeed be rewritten as SELECT count(*) FROM ut1 WHERE c1 > 1. However, considering the consistency of the code logic, I personally feel that it's unnecessary to check the number of count_if statements.
--
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
Re: [PR] [SPARK-53742][SQL] Push down the filter used in the count_if function [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #52554:
URL: https://github.com/apache/spark/pull/52554#issuecomment-3764531488
We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable.
If you'd like to revive this PR, please reopen it and ask a committer to remove the Stale tag!
--
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
Re: [PR] [SPARK-53742][SQL] Push down the filter used in the count_if function [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #52554: [SPARK-53742][SQL] Push down the filter used in the count_if function
URL: https://github.com/apache/spark/pull/52554
--
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
Re: [PR] [SPARK-53742][SQL] Push down the filter used in the count_if function [spark]
Posted by "beliefer (via GitHub)" <gi...@apache.org>.
beliefer commented on code in PR #52554:
URL: https://github.com/apache/spark/pull/52554#discussion_r2415885140
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala:
##########
@@ -2776,6 +2777,71 @@ object RemoveLiteralFromGroupExpressions extends Rule[LogicalPlan] {
}
}
+/**
+ * To optimize performance, filter conditions should be pushed down in [[CountIf]] queries
+ * to reduce the volume of data being processed.
+ *
+ * Input Query :
+ * {{{
+ * SELECT count_if(c1 > 1) FROM ut1
+ * }}}
+ *
+ * Rewritten Query:
+ * {{{
+ * SELECT count_if(c1 > 1) FROM ut1 where c1 > 1
Review Comment:
Why not rewrite to `SELECT count(_) FROM ut1 where c1 > 1` ?
--
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