andygrove opened a new issue, #2662: URL: https://github.com/apache/datafusion-ballista/issues/2662
**Is your feature request related to a problem or challenge? Please describe what you are trying to do.** TPC-H q21 is the slowest query at SF1000: 817 s out of 3,374 s for all 22 queries (2 executors × 8 vCPU, `--memory-pool-size=48GB`, AQE on, local Parquet). This epic tracks the work to bring it down. Stage breakdown from the scheduler event log: | Stage | Wall | What | Rows in → out | |---|---|---|---| | 1 | 75 s | scan + filter `lineitem l1`, shuffle by `l_suppkey` | 6.0B → 3.79B | | 2 | **204 s** | `supplier ⋈ l1` (all 10M suppliers), reshuffle by `l_orderkey` | 3.80B → 3.79B | | 4 | **198 s** | sort-merge join with `orders`, *then* `nation = 'ARGENTINA'` | 4.52B → 73M | | 5 | 118 s | scan all of `lineitem l2` for `EXISTS` | 6.0B → 6.0B | | 7 | 56 s | scan `lineitem l3` for `NOT EXISTS` | 6.0B → 3.79B | | 6 | 137 s | semi join (SMJ) | 6.07B → 71M | | 8 | 72 s | anti join (SMJ) + partial aggregate | 3.86B → 3.7M | **1. Join order (~390 s).** Neither DataFusion nor Ballista reorders joins, so the plan follows the `FROM` order (`supplier, lineitem l1, orders, nation`) and the selective nation filter (1 in 25 suppliers) is applied after 3.79B rows have been joined, reshuffled and sort-merge-joined. Moving `nation` second in the `FROM` list (no other change) takes q21 from **817 s to 425 s**: stage 2 drops to 38 s (152M rows out) and the orders join to 11 s. That experiment only works with `ballista.optimizer.broadcast_join_threshold_rows=100000`, because of #2661. **2. `EXISTS` / `NOT EXISTS` (~320 s of the remaining 425 s).** Both subqueries scan and shuffle all of `lineitem` (6B and 3.79B rows) to semi/anti join against an outer side of only 73M rows. A dynamic filter on `l_orderkey` from the outer side pushed into those scans would avoid shuffling most of them. **3. Stage concurrency.** Stages 5 and 7 have no dependencies but start only after earlier stages finish (448 s and 567 s into the job). Whether running them earlier would help depends on spare executor CPU, which hasn't been measured. **Describe the solution you'd like** Sub-issues: - [ ] #2661 AQE broadcasts a join on an underestimated build side and fails with `OffsetOverflowError` (blocks any better join order for q21) - [ ] #1960 Surface collected column statistics to planners via `ShuffleReaderExec` (without distinct counts, join cardinality estimates downstream of an exchange collapse to the smaller side's row count, which caused #2661 and will mislead any join reordering) - [ ] Join reordering: a cost-based or heuristic join reorder, either upstream in DataFusion (see apache/datafusion#17718) or as a Ballista planner rule, so filtered dimension joins happen first - [ ] #1375 Investigate use of `DynamicFilters` in Ballista (key filter from the semi/anti join's outer side into the `lineitem` scans) - [ ] #1953 Reuse repeated exchanges/subplans (`l1` and `l3` are the same filtered `lineitem` scan; in the current plans they are shuffled on different keys, so this helps only if the plan is shaped to share them) - [ ] Measure executor CPU utilisation during q21 to decide whether independent stages should be scheduled earlier (related: #2652) **Describe alternatives you've considered** Rewriting the benchmark SQL to the better join order. That's useful to measure the potential win, but the benchmark has to use the standard query text, so the planner needs to find that order itself. **Additional context** Related: #2024 (AQE regresses join-heavy TPC-H queries at SF1000), #2520 (performance review tracking). The run used the build from #2659, which fixed the q5 OOM (#2656) that previously stopped the run before q21. -- 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]
