Why do we recommend to disable "preferSortMergeJoin"?

[xumingming]

https://github.com/apache/incubator-gluten/blob/800cadd0f4f71d0ebedb5fbf6428442ae52b77ac/docs/Configuration.md?plain=1#L21

Just curious, why do we recommend to disable preferSortMergeJoin? Do we have some kind of benchmark result? Would be great if you can share the benchmark results 👍

The reason I ask this is that Spark claims SortMergeJoin works better for large tables:

  val PREFER_SORTMERGEJOIN = buildConf("spark.sql.join.preferSortMergeJoin")
    .internal()
    .doc("When true, prefer sort merge join over shuffled hash join. " +
      "Sort merge join consumes less memory than shuffled hash join and it works efficiently " +
      "when both join tables are large. On the other hand, shuffled hash join can improve " +
      "performance (e.g., of full outer joins) when one of join tables is much smaller.")
    .version("2.0.0")
    .booleanConf
    .createWithDefault(true)

[xumingming]

@zhouyuan @z123 @PHILO-HE Do you have information to share? e.g. Do you use SortMergeJoin or ShuffledHashJoin in production?

[zhouyuan]

Hi, @xumingming
This is mostly due to the SortMergeJoin implementation in Velox is not optimal vs. HashJoin. In our TPCH benchmark the performance improvement of using HashJoin is up to ~2x (depends on query). For vanilla Spark I think their Hash Join impl. does not support spill, while Sort Merge Join does support spill. That maybe the main reason why Sort Merge Join is promoted.

I didn't have much information the production env, but for functionality and performance in Gluten/Velox - Hash Join is better. We are also improving the merge join code path in Velox recently but still requires more tests and validations from Gluten users.

thanks, -yuan

[xumingming]

@zhouyuan Thanks for the information, appreciated!