[GitHub] [spark] wangyum opened a new pull request, #40114: [SPARK-42513][SQL] Push down topK through join

[via GitHub]

wangyum opened a new pull request, #40114:
URL: https://github.com/apache/spark/pull/40114

   ### What changes were proposed in this pull request?
   
   This PR enhance `LimitPushDown` to support push down topK through join. The 
key idea is that if the order expressions comes from one join side and pushing 
down topK does not affect query result:
   1. Left outer join and order expressions come from the left side.
   2. Right outer join and order expressions come from the right side.
   3. Inner join, cross join, left outer join, right outer join, full outer 
join and join condition is empty and order expressions from one side.
   4. Left anti join, left semi join and join condition is empty and order 
expressions from left side.
   
   ### Why are the changes needed?
   
   Improve query performance.
   
   Case 1:
   ```scala
   spark.range(100000000).selectExpr("id as a", "id as 
b").write.saveAsTable("t1")
   spark.range(100000000).selectExpr("id as x", "id as 
y").write.saveAsTable("t2")
   
   sql("select * from t1 left join t2 on a = x order by b limit 5").collect()
   spark.sql("set 
spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.LimitPushDown")
   sql("select * from t1 left join t2 on a = x order by b limit 5").collect()
   ```
   
   Disable push down | Enable push down
   -- | --
   <img 
src="https://user-images.githubusercontent.com/5399861/220509675-b5bb96d2-c4cd-464f-b464-ff74c28a473c.png";
 width="200" height="730"> | <img 
src="https://user-images.githubusercontent.com/5399861/220509756-66b1e981-25a9-4abd-8359-d0a8cb9924e9.png";
 width="200" height="730">
   
   
   Case 2:
   ```scala
   spark.range(100000000).selectExpr("id % 10000 as a", "id as 
b").write.saveAsTable("t1")
   spark.range(100000000).selectExpr("id % 10000 as x", "id as 
y").write.saveAsTable("t2")
   
   sql("select * from t1 left join t2 on a = x order by b limit 5").collect()
   spark.sql("set 
spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.LimitPushDown")
   sql("select * from t1 left join t2 on a = x order by b limit 5").collect()
   ```
   
   Disable push down | Enable push down
   -- | --
   <img 
src="https://user-images.githubusercontent.com/5399861/220511495-2c9a31ac-b21d-4cd0-b916-b96640982ecc.png";
 width="200" height="730"> | <img 
src="https://user-images.githubusercontent.com/5399861/220510582-f02045b1-93b8-4849-90a4-7c2ae42c765f.png";
 width="200" height="730">
   
   
   ### 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.

To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org