andygrove commented on issue #1959: URL: https://github.com/apache/datafusion-ballista/issues/1959#issuecomment-4900048129
## Addendum — `target_partitions` sensitivity (why SMJ looked slower) The matrix above ran Ballista at `target_partitions=32`. Sweeping it shows that a good chunk of the SMJ gap was **under-partitioning**, and that SMJ and SHJ respond **oppositely**: | Ballista total (s) | p=32 | p=64 | p=96 | |---|---:|---:|---:| | **SortMergeJoin** | 560 | **466** | 528 | | **ShuffledHashJoin** | **398** | 413 | 492 | - **SMJ benefits from more partitions** (32 → 64 = **−17 %**). Sort-merge joins sort both sides, and sorting is superlinear, so smaller partitions mean much smaller sorts. The sweet spot is ~64 (≈4× the 16 task slots); 96 is past the overhead knee. - **SHJ does not** (32 is best). Hash joins don't sort, so shrinking partitions doesn't reduce a superlinear cost — more partitions just add shuffle-file / scheduling overhead. **Fair (best-partition) engine comparison:** | | Ballista (best) | Comet | ratio | |---|---:|---:|---:| | SMJ | 466 (p=64) | 356 | **1.31×** (was 1.57× at p=32) | | SHJ | 398 (p=32) | 347 | 1.15× | Per-query, the sort-heavy joins improve most going 32 → 64: **Q9 77→55, Q21 78→57, Q18 48→33, Q8 54→39, Q5 45→33 s.** ### Takeaways 1. Ballista's best config here is still **ShuffledHashJoin (398 s, within ~15 % of Comet)** — enabling hash join beats tuning SMJ partition count. 2. The rule-of-thumb "**`target_partitions` = total task slots**" (32 here) is **too low for SortMergeJoin** — SMJ wants ≈4× slots. For SHJ, matching slots is fine. 3. The residual after tuning — SMJ 1.31× / SHJ 1.15× vs Comet — is the real engine / shuffle-implementation difference, concentrated in a few outliers (**Q7 stays ~2× Comet even at good partitions**). Attributing that needs the shuffle-read metrics tracked in #1958. _(Single iteration, ~5–10 % run-to-run noise; the −17 % SMJ partition effect and the SMJ-vs-SHJ opposite response are well above 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. 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]
