You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by GitBox <gi...@apache.org> on 2021/01/15 06:36:12 UTC
[GitHub] [spark] wangyum opened a new pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
wangyum opened a new pull request #31193:
URL: https://github.com/apache/spark/pull/31193
### What changes were proposed in this pull request?
This pr disable LeftSemi/LeftAnti push down over Aggregate.
### Why are the changes needed?
LeftSemi/LeftAnti push down over Aggregate may affect performance. for example:
```sql
SELECT i_item_sk ss_item_sk
FROM item,
(SELECT
distinct
iss.i_brand_id brand_id,
iss.i_class_id class_id,
iss.i_category_id category_id
FROM store_sales, item iss, date_dim d1
WHERE ss_item_sk = iss.i_item_sk
AND ss_sold_date_sk = d1.d_date_sk
AND d1.d_year BETWEEN 1999 AND 1999 + 2
INTERSECT
SELECT
distinct
ics.i_brand_id,
ics.i_class_id,
ics.i_category_id
FROM catalog_sales, item ics, date_dim d2
WHERE cs_item_sk = ics.i_item_sk
AND cs_sold_date_sk = d2.d_date_sk
AND d2.d_year BETWEEN 1999 AND 1999 + 2
INTERSECT
SELECT
distinct
iws.i_brand_id,
iws.i_class_id,
iws.i_category_id
FROM web_sales, item iws, date_dim d3
WHERE ws_item_sk = iws.i_item_sk
AND ws_sold_date_sk = d3.d_date_sk
AND d3.d_year BETWEEN 1999 AND 1999 + 2) x
WHERE i_brand_id = brand_id
AND i_class_id = class_id
AND i_category_id = category_id;
```
This query is rewritten from [q14b](https://github.com/apache/spark/blob/a78d6ce376edf2a8836e01f47b9dff5371058d4c/sql/core/src/test/resources/tpcds/q14b.sql#L2-L32).
CBO enabled | CBO disabled
-- | --
The statistics are more accurate, so it will be converted to broadcast join, and then push down aggregate. | It will not push down aggregate.
 | 
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
Unit test.
----------------------------------------------------------------
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.
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 #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-776411489
Is this PR still valid? IIRC @wangyum has submitted another PR to replace 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.
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] AmplabJenkins removed a comment on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
AmplabJenkins removed a comment on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760690501
----------------------------------------------------------------
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.
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 #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
cloud-fan commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760699379
why do we need to disable it completely?
----------------------------------------------------------------
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.
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] c21 commented on a change in pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
c21 commented on a change in pull request #31193:
URL: https://github.com/apache/spark/pull/31193#discussion_r572724880
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushDownLeftSemiAntiJoin.scala
##########
@@ -53,22 +50,6 @@ object PushDownLeftSemiAntiJoin extends Rule[LogicalPlan]
p.copy(child = Join(gChild, rightOp, joinType, newJoinCond, hint))
}
- // LeftSemi/LeftAnti over Aggregate, only push down if join can be planned as broadcast join.
- case join @ Join(agg: Aggregate, rightOp, LeftSemiOrAnti(_), _, _)
- if agg.aggregateExpressions.forall(_.deterministic) && agg.groupingExpressions.nonEmpty &&
Review comment:
* aggregation reduces data
* left semi / left anti broadcast join reduces data
I feel these two heuristics are both legit, and with current statistics available in logical plan, it's hard to always make right decision. If we really need this, maybe add a disable-by-default config for pushing down these join over aggregate? (by default we still follow existing behavior)
----------------------------------------------------------------
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.
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] wangyum closed pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
wangyum closed pull request #31193:
URL: https://github.com/apache/spark/pull/31193
----------------------------------------------------------------
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.
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] SparkQA commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760712164
Kubernetes integration test status failure
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/38670/
----------------------------------------------------------------
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.
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] SparkQA commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
SparkQA commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760710298
Kubernetes integration test starting
URL: https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder-K8s/38670/
----------------------------------------------------------------
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.
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] wangyum commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
wangyum commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760809459
Generally speaking, aggregation can reduce the amount of data. Pushdown aggregation will move the aggregation after the join, which will cause to shuffle amount of data to join. If join can be broadcast, we'd better to do aggregation first to reduce the amount of shuffle data.
----------------------------------------------------------------
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.
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] c21 commented on a change in pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
c21 commented on a change in pull request #31193:
URL: https://github.com/apache/spark/pull/31193#discussion_r572724880
##########
File path: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushDownLeftSemiAntiJoin.scala
##########
@@ -53,22 +50,6 @@ object PushDownLeftSemiAntiJoin extends Rule[LogicalPlan]
p.copy(child = Join(gChild, rightOp, joinType, newJoinCond, hint))
}
- // LeftSemi/LeftAnti over Aggregate, only push down if join can be planned as broadcast join.
- case join @ Join(agg: Aggregate, rightOp, LeftSemiOrAnti(_), _, _)
- if agg.aggregateExpressions.forall(_.deterministic) && agg.groupingExpressions.nonEmpty &&
Review comment:
* aggregation reduces data
* left semi / left anti broadcast join reduces data
I feel these two heuristics are both legit, and with current statistics available in logical plan, it's hard to always make right decision. If we really need this, maybe add a disable-by-default config for pushing down these join over aggregate?
----------------------------------------------------------------
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.
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] wangyum commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
wangyum commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760735189
Push down over Aggregate affect performance, for example:
```sql
SELECT i_item_sk ss_item_sk
FROM item,
(SELECT
distinct
iss.i_brand_id brand_id,
iss.i_class_id class_id,
iss.i_category_id category_id
FROM store_sales, item iss, date_dim d1
WHERE ss_item_sk = iss.i_item_sk
AND ss_sold_date_sk = d1.d_date_sk
AND d1.d_year BETWEEN 1999 AND 1999 + 2
INTERSECT
SELECT
distinct
ics.i_brand_id,
ics.i_class_id,
ics.i_category_id
FROM catalog_sales, item ics, date_dim d2
WHERE cs_item_sk = ics.i_item_sk
AND cs_sold_date_sk = d2.d_date_sk
AND d2.d_year BETWEEN 1999 AND 1999 + 2
INTERSECT
SELECT
distinct
iws.i_brand_id,
iws.i_class_id,
iws.i_category_id
FROM web_sales, item iws, date_dim d3
WHERE ws_item_sk = iws.i_item_sk
AND ws_sold_date_sk = d3.d_date_sk
AND d3.d_year BETWEEN 1999 AND 1999 + 2) x
WHERE i_brand_id = brand_id
AND i_class_id = class_id
AND i_category_id = category_id;
```
Push down(enable CBO) takes 3.5 minutes, but it only takes 25 seconds if disable push down(disable CBO). So it is hard to say pushing down always has benefit.
----------------------------------------------------------------
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.
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] AmplabJenkins commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760713230
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder-K8s/38670/
----------------------------------------------------------------
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.
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] wangyum commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
wangyum commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-777994631
Yes, we can close it now.
----------------------------------------------------------------
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.
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] AmplabJenkins commented on pull request #31193: [SPARK-34117][SQL] Disable LeftSemi/LeftAnti push down over Aggregate
Posted by GitBox <gi...@apache.org>.
AmplabJenkins commented on pull request #31193:
URL: https://github.com/apache/spark/pull/31193#issuecomment-760690501
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/134085/
----------------------------------------------------------------
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.
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