wangyum commented on PR #37163:
URL: https://github.com/apache/spark/pull/37163#issuecomment-1182708274
@viirya Here is an example. AQE can be converted to broadcast join, but the
performance is worse than directly planned to broadcast join.
```scala
import org.apache.spark.benchmark.Benchmark
val numRows = 1024 * 1024 * 25
spark.sql(s"CREATE TABLE t1 using parquet AS SELECT id AS a, id AS b, id AS
c, id AS d, id AS e FROM range(${numRows}L)")
spark.sql(s"CREATE TABLE t2 using parquet AS SELECT id AS a, id AS b, id AS
c, id AS d, id AS e FROM range(${numRows}L)")
spark.sql("ANALYZE TABLE t1 COMPUTE STATISTICS FOR ALL COLUMNS")
spark.sql("ANALYZE TABLE t2 COMPUTE STATISTICS FOR ALL COLUMNS")
val benchmark = new Benchmark(s"Benchmark SPARK-39750", numRows, minNumIters
= 5)
Seq(false, true).foreach { enabled =>
val name = s"CBO is ${if (enabled) "Enabled" else "Disabled"}"
benchmark.addCase(name) { _ =>
withSQLConf("spark.sql.cbo.enabled" -> s"$enabled") {
spark.sql(s"SELECT * FROM t1 JOIN t2 ON t1.a = t2.a AND t2.b <
10000").write.format("noop").mode("Overwrite").save()
}
}
}
benchmark.run()
```
```
Java HotSpot(TM) 64-Bit Server VM 1.8.0_281-b09 on Mac OS X 10.15.7
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Benchmark SPARK-39750: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
CBO is Disabled 5277 5570
195 5.0 201.3 1.0X
CBO is Enabled 1378 1582
166 19.0 52.6 3.8X
```
Default | Enable CBO
-- | --
| <img width="622" alt="image"
src="https://user-images.githubusercontent.com/5399861/178641161-742b2fa7-9047-4a05-985e-32a240aa7cee.png";>
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/5399861/178641161-742b2fa7-9047-4a05-985e-32a240aa7cee.png"](https://user-images.githubusercontent.com/5399861/178641161-742b2fa7-9047-4a05-985e-32a240aa7cee.png"){kind=link}