[ https://issues.apache.org/jira/browse/SPARK-36290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17389715#comment-17389715 ]
Yuming Wang commented on SPARK-36290: ------------------------------------- {code:java} spark.sql("create table t1 using parquet select cast(id as string) as a, cast(id as string) as b from range(1)") spark.sql("create table t2 using parquet select cast(id as string) as x from range(1)") spark.sql("set spark.sql.autoBroadcastJoinThreshold=-1") spark.sql("SELECT t1.* FROM t1 JOIN t2 ON coalesce(t1.a, t1.b)=t2.x").show {code} The {{Coalesce}} evaluation: {noformat} at org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection.Pmod_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection.apply(Unknown Source) at org.apache.spark.sql.execution.exchange.ShuffleExchangeExec$.$anonfun$prepareShuffleDependency$6(ShuffleExchangeExec.scala:311) at org.apache.spark.sql.execution.exchange.ShuffleExchangeExec$.$anonfun$prepareShuffleDependency$6$adapted(ShuffleExchangeExec.scala:311) at org.apache.spark.sql.execution.exchange.ShuffleExchangeExec$.$anonfun$prepareShuffleDependency$14(ShuffleExchangeExec.scala:380) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection.apply(Unknown Source) at org.apache.spark.sql.execution.SortExec$$anon$1.computePrefix(SortExec.scala:90) at org.apache.spark.sql.execution.UnsafeExternalRowSorter.insertRow(UnsafeExternalRowSorter.java:137) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage3.sort_addToSorter_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage3.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:759) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage5.smj_findNextJoinRows_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage5.smj_findNextJoinRows_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage5.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$2.hasNext(WholeStageCodegenExec.scala:778) {noformat} > Push down join condition evaluation > ----------------------------------- > > Key: SPARK-36290 > URL: https://issues.apache.org/jira/browse/SPARK-36290 > Project: Spark > Issue Type: Improvement > Components: SQL > Affects Versions: 3.3.0 > Reporter: Yuming Wang > Priority: Major > > {code:scala} > val numRows = 1024 * 1024 * 15 > spark.sql(s"CREATE TABLE t1 using parquet AS select id as a, id as b from > range(${numRows}L)") > spark.sql(s"CREATE TABLE t2 using parquet AS select id as a, id as b from > range(${numRows}L)") > val benchmark = new Benchmark("Benchmark push down join condition > evaluation", numRows, minNumIters = 5) > Seq(false, true).foreach { pushDownEnabled => > val name = s"Join Condition Evaluation ${if (pushDownEnabled) > s"(Pushdown)" else ""}" > benchmark.addCase(name) { _ => > withSQLConf("spark.sql.pushDownJoinConditionEvaluationevaluation" -> > s"$pushDownEnabled") { > spark.sql("SELECT t1.* FROM t1 JOIN t2 ON translate(t1.a, '123', > 'abc') = translate(t2.a, '123', > 'abc')").write.format("noop").mode("Overwrite").save() > } > } > } > benchmark.run() > {code} > {noformat} > Java HotSpot(TM) 64-Bit Server VM 1.8.0_251-b08 on Mac OS X 10.15.7 > Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz > Benchmark push down join condition evaluation: Best Time(ms) Avg Time(ms) > Stdev(ms) Rate(M/s) Per Row(ns) Relative > ----------------------------------------------------------------------------------------------------------------------------- > Join Condition Evaluation 32459 34521 > 1465 0.5 2063.7 1.0X > Join Condition Evaluation (Pushdown) 19483 20350 > 812 0.8 1238.7 1.7X > {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org