andygrove commented on PR #2749: URL: https://github.com/apache/datafusion-ballista/pull/2749#issuecomment-7050436802
I ran some independent benchmarks on a 32-core / 124 GB Linux box at SF100, which is a step up from the SF10 numbers in the description. **Setup** - Three arms. `main` is the merge base `49b53be6e`. `off` is the PR head `01760d853` with `ballista.planner.runtime_filter.enabled = false`. `on` is the same build with it set to `true`. - A `release-nonlto` build, one scheduler and two executors (8 vcores and a 16 GiB pool each), `target_partitions = 16`, and AQE on (the default). - A fresh cluster for each run, in the order main, off, on, on, off, main. I report the median of the two runs per arm. D1 and D3 used four runs per arm and the same extra settings as in the description. - Results matched across all three arms for every query. TPC-H Q11 returns no rows at SF100 in any arm (its threshold is tuned for SF1), so I left it out. **Summary** | Workload | main (s) | off (s) | on (s) | on vs off | |---|---:|---:|---:|---:| | TPC-H SF100, 21 queries | 127.4 | 125.8 | 125.8 | +0.0% | | TPC-DS SF100, 99 queries | 342.7 | 340.6 | 341.9 | +0.4% | | Custom D1, partitioned fallback | 2.93 | 2.89 | 0.64 | -77.9% | | Custom D3, near-saturated Bloom | 2.92 | 2.94 | 2.96 | +0.7% | D1 is a great result. It went from 2.9s to 0.64s, and it was consistent in every round. With the flag off, the PR is neutral against `main`, so the default-off path costs nothing. **Where it fires at SF100** I ran `EXPLAIN ANALYZE` with the flag on for every query. A filter was bound in 1 TPC-H query (Q21) and 14 TPC-DS queries. Q19 no longer gets one at SF100, because `part` is too big to broadcast and both sides are shuffled at the same time. For those 15 queries, pruning lots of rows mostly didn't turn into wall-clock wins. | Query | rows pruned | off (s) | on (s) | on vs off | |---|---:|---:|---:|---:| | TPC-DS Q82 | 399.3 M | 2.20 | 2.56 | +16.5% | | TPC-DS Q37 | 399.3 M | 1.25 | 1.44 | +15.2% | | TPC-DS Q13 | 234.3 M | 1.79 | 1.90 | +6.3% | | TPC-DS Q51 | 291.0 M | 4.75 | 4.95 | +4.2% | | TPC-DS Q49 | 487.5 M | 2.10 | 2.16 | +3.2% | | TPC-DS Q85 | 60.7 M | 0.49 | 0.48 | -2.0% | | TPC-DS Q48 | 233.6 M | 1.29 | 1.28 | -0.9% | | TPC-H Q21 | 175.6 M | 14.30 | 14.31 | +0.0% | | other 7 TPC-DS | < 2 M each | | | -1% to +5% | Q37 and Q82 are the clearest case. Both on runs were slower than all four other runs. In Q82's stage 2, the apply sits on the `inventory` scan, below `FilterExec: inv_quantity_on_hand BETWEEN 100 AND 500` and a `CollectLeft` join whose build side has 70 rows. It hashes all 399M rows to drop rows that the cheap range filter and the broadcast probe would have discarded anyway. The join is already a perfect filter there, so the apply mostly repeats its work on more rows. Meanwhile, the big win in those queries is left on the table. The `store_sales` side (288M rows, 745 MB spilled) and the `catalog_sales` side (144M rows) of the final `LeftSemi` are sibling stages, so they bypass filter `id=1`. A couple of thoughts, all fine as follow-ups since this is default off. - In the same-stage broadcast case, would it make sense to only place the apply when something costly sits between the scan and the join? The filter could also go above cheap `FilterExec`s rather than directly on the scan. Q19's large `OR` predicate is the kind of case where it pays off. - Q37 and Q82 are good targets for the Task 4 waiting work, since a tiny build summary would cut a several-hundred-million-row shuffle. <details> <summary>TPC-H SF100, all queries</summary> | Query | main (s) | off (s) | on (s) | on vs main | on vs off | results match | |---|---:|---:|---:|---:|---:|:-:| | q1 | 3.06 | 3.14 | 3.11 | +1.5% | -1.0% | yes | | q2 | 2.04 | 2.05 | 2.05 | +0.8% | +0.2% | yes | | q3 | 6.62 | 6.61 | 6.49 | -2.0% | -1.9% | yes | | q4 | 2.90 | 3.10 | 2.84 | -1.9% | -8.3% | yes | | q5 | 12.86 | 13.01 | 13.15 | +2.2% | +1.1% | yes | | q6 | 1.00 | 1.02 | 1.03 | +2.6% | +1.3% | yes | | q7 | 5.16 | 4.85 | 4.90 | -5.1% | +1.0% | yes | | q8 | 3.83 | 3.77 | 3.84 | +0.4% | +1.9% | yes | | q9 | 12.96 | 12.09 | 12.26 | -5.5% | +1.3% | yes | | q10 | 6.67 | 7.08 | 6.82 | +2.3% | -3.7% | yes | | q12 | 3.66 | 3.51 | 3.55 | -2.9% | +1.1% | yes | | q13 | 5.47 | 5.11 | 5.07 | -7.3% | -0.9% | yes | | q14 | 2.06 | 2.13 | 2.04 | -0.9% | -4.3% | yes | | q15 | 1.98 | 2.01 | 2.06 | +3.5% | +2.3% | yes | | q16 | 1.52 | 1.54 | 1.60 | +5.2% | +3.6% | yes | | q17 | 15.77 | 15.77 | 15.85 | +0.5% | +0.5% | yes | | q18 | 15.46 | 14.95 | 15.11 | -2.3% | +1.1% | yes | | q19 | 2.63 | 2.59 | 2.63 | +0.2% | +1.6% | yes | | q20 | 5.29 | 5.33 | 5.22 | -1.2% | -2.1% | yes | | q21 | 14.30 | 14.30 | 14.31 | +0.1% | +0.0% | yes | | q22 | 2.13 | 1.86 | 1.93 | -9.4% | +3.8% | yes | | **Total** | 127.4 | 125.8 | 125.8 | -1.2% | +0.0% | | </details> <details> <summary>TPC-DS SF100, all queries</summary> | Query | main (s) | off (s) | on (s) | on vs main | on vs off | results match | |---|---:|---:|---:|---:|---:|:-:| | q1 | 0.41 | 0.39 | 0.39 | -5.0% | -0.1% | yes | | q2 | 0.78 | 0.73 | 0.72 | -7.5% | -1.0% | yes | | q3 | 0.80 | 0.68 | 0.68 | -15.8% | +0.0% | yes | | q4 | 18.87 | 18.87 | 18.25 | -3.3% | -3.3% | yes | | q5 | 2.77 | 2.61 | 2.58 | -7.0% | -1.1% | yes | | q6 | 3.45 | 3.65 | 3.50 | +1.4% | -4.2% | yes | | q7 | 1.94 | 1.77 | 1.75 | -9.7% | -1.2% | yes | | q8 | 0.56 | 0.55 | 0.56 | -0.3% | +2.7% | yes | | q9 | 3.61 | 3.39 | 3.46 | -4.1% | +2.2% | yes | | q10 | 0.61 | 0.58 | 0.59 | -2.1% | +2.9% | yes | | q11 | 8.79 | 9.23 | 8.99 | +2.2% | -2.6% | yes | | q12 | 0.53 | 0.54 | 0.54 | +1.4% | -0.7% | yes | | q13 | 1.82 | 1.79 | 1.90 | +4.4% | +6.3% | yes | | q14 | 9.82 | 9.75 | 9.67 | -1.5% | -0.8% | yes | | q15 | 0.47 | 0.44 | 0.47 | +0.6% | +6.2% | yes | | q16 | 1.80 | 1.71 | 1.79 | -0.6% | +4.3% | yes | | q17 | 1.85 | 1.81 | 1.79 | -2.7% | -0.8% | yes | | q18 | 1.41 | 1.43 | 1.45 | +2.4% | +0.9% | yes | | q19 | 0.87 | 0.87 | 0.88 | +1.1% | +0.8% | yes | | q20 | 0.85 | 0.85 | 0.86 | +0.7% | +0.2% | yes | | q21 | 1.03 | 0.98 | 0.97 | -6.0% | -0.9% | yes | | q22 | 5.87 | 5.98 | 5.98 | +1.9% | +0.1% | yes | | q23 | 35.74 | 36.02 | 35.93 | +0.5% | -0.2% | yes | | q24 | 5.60 | 5.54 | 5.55 | -1.0% | +0.0% | yes | | q25 | 2.06 | 2.03 | 2.07 | +0.5% | +2.0% | yes | | q26 | 0.86 | 0.81 | 0.82 | -5.4% | +1.1% | yes | | q27 | 1.59 | 1.53 | 1.51 | -4.7% | -1.2% | yes | | q28 | 4.23 | 4.23 | 4.20 | -0.8% | -0.7% | yes | | q29 | 1.79 | 1.78 | 1.79 | +0.3% | +0.6% | yes | | q30 | 0.31 | 0.32 | 0.33 | +4.7% | +2.5% | yes | | q31 | 2.34 | 2.35 | 2.35 | +0.4% | -0.3% | yes | | q32 | 0.55 | 0.51 | 0.54 | -2.4% | +4.4% | yes | | q33 | 1.60 | 1.59 | 1.62 | +1.3% | +1.7% | yes | | q34 | 0.80 | 0.80 | 0.79 | -1.1% | -1.1% | yes | | q35 | 1.02 | 1.03 | 1.03 | +0.5% | -0.1% | yes | | q36 | 1.88 | 1.90 | 1.95 | +3.8% | +2.7% | yes | | q37 | 1.24 | 1.25 | 1.44 | +15.6% | +15.2% | yes | | q38 | 1.83 | 1.92 | 1.92 | +4.6% | -0.2% | yes | | q39 | 4.00 | 3.97 | 3.99 | -0.2% | +0.5% | yes | | q40 | 7.03 | 6.83 | 7.25 | +3.1% | +6.1% | yes | | q41 | 0.09 | 0.09 | 0.10 | +12.3% | +15.5% | yes | | q42 | 0.65 | 0.61 | 0.62 | -4.5% | +0.7% | yes | | q43 | 0.67 | 0.65 | 0.65 | -3.3% | +0.9% | yes | | q44 | 1.07 | 1.00 | 1.01 | -5.2% | +0.9% | yes | | q45 | 0.36 | 0.36 | 0.35 | -3.3% | -2.9% | yes | | q46 | 1.49 | 1.45 | 1.49 | +0.1% | +2.8% | yes | | q47 | 3.38 | 3.43 | 3.47 | +2.7% | +1.1% | yes | | q48 | 1.32 | 1.29 | 1.28 | -2.8% | -0.9% | yes | | q49 | 2.14 | 2.10 | 2.16 | +1.1% | +3.2% | yes | | q50 | 0.87 | 0.85 | 0.86 | -1.3% | +1.6% | yes | | q51 | 4.70 | 4.75 | 4.95 | +5.2% | +4.2% | yes | | q52 | 0.63 | 0.65 | 0.67 | +5.3% | +3.1% | yes | | q53 | 0.70 | 0.70 | 0.68 | -2.9% | -3.3% | yes | | q54 | 0.95 | 0.96 | 0.97 | +1.5% | +0.7% | yes | | q55 | 0.61 | 0.59 | 0.59 | -2.2% | -0.2% | yes | | q56 | 1.34 | 1.34 | 1.36 | +1.5% | +1.3% | yes | | q57 | 1.79 | 1.80 | 1.85 | +3.4% | +2.4% | yes | | q58 | 1.65 | 1.60 | 1.69 | +2.5% | +5.8% | yes | | q59 | 1.71 | 1.73 | 1.74 | +2.0% | +0.9% | yes | | q60 | 1.70 | 1.65 | 1.67 | -1.7% | +1.4% | yes | | q61 | 0.12 | 0.11 | 0.11 | -4.3% | -1.8% | yes | | q62 | 0.60 | 0.59 | 0.58 | -4.4% | -2.0% | yes | | q63 | 0.69 | 0.66 | 0.66 | -3.4% | +0.8% | yes | | q64 | 19.46 | 19.41 | 19.19 | -1.4% | -1.1% | yes | | q65 | 2.96 | 2.91 | 2.94 | -0.6% | +1.1% | yes | | q66 | 1.07 | 1.03 | 1.01 | -5.7% | -1.8% | yes | | q67 | 28.78 | 27.23 | 27.94 | -2.9% | +2.6% | yes | | q68 | 1.69 | 1.68 | 1.63 | -3.7% | -2.9% | yes | | q69 | 0.65 | 0.65 | 0.65 | -0.8% | +0.4% | yes | | q70 | 3.23 | 3.28 | 3.18 | -1.5% | -2.8% | yes | | q71 | 1.12 | 1.10 | 1.09 | -2.3% | -0.7% | yes | | q72 | 19.76 | 19.87 | 19.91 | +0.8% | +0.2% | yes | | q73 | 0.70 | 0.68 | 0.70 | -0.9% | +2.1% | yes | | q74 | 5.14 | 5.35 | 5.33 | +3.6% | -0.5% | yes | | q75 | 5.70 | 5.72 | 5.78 | +1.5% | +1.0% | yes | | q76 | 1.17 | 1.15 | 1.15 | -1.9% | -0.6% | yes | | q77 | 1.27 | 1.28 | 1.27 | -0.2% | -0.4% | yes | | q78 | 18.63 | 18.74 | 18.60 | -0.1% | -0.7% | yes | | q79 | 1.57 | 1.57 | 1.61 | +2.8% | +3.1% | yes | | q80 | 29.80 | 29.63 | 29.91 | +0.3% | +0.9% | yes | | q81 | 0.36 | 0.34 | 0.35 | -3.2% | +4.6% | yes | | q82 | 2.24 | 2.20 | 2.56 | +14.5% | +16.5% | yes | | q83 | 0.25 | 0.27 | 0.24 | -4.3% | -10.3% | yes | | q84 | 0.12 | 0.12 | 0.12 | +2.9% | +4.6% | yes | | q85 | 0.52 | 0.49 | 0.48 | -6.8% | -2.0% | yes | | q86 | 0.31 | 0.30 | 0.31 | -0.3% | +3.5% | yes | | q87 | 1.91 | 2.03 | 1.87 | -2.4% | -8.0% | yes | | q88 | 3.13 | 3.07 | 3.06 | -2.2% | -0.1% | yes | | q89 | 0.76 | 0.76 | 0.75 | -2.0% | -1.6% | yes | | q90 | 0.20 | 0.20 | 0.20 | -2.0% | +0.8% | yes | | q91 | 0.17 | 0.16 | 0.17 | +1.8% | +3.7% | yes | | q92 | 0.32 | 0.32 | 0.32 | +0.3% | -0.6% | yes | | q93 | 8.22 | 8.04 | 8.15 | -0.9% | +1.4% | yes | | q94 | 0.81 | 0.80 | 0.78 | -3.7% | -2.6% | yes | | q95 | 1.61 | 1.76 | 1.66 | +3.0% | -5.7% | yes | | q96 | 0.45 | 0.48 | 0.46 | +3.1% | -3.0% | yes | | q97 | 2.63 | 2.54 | 2.77 | +5.4% | +9.0% | yes | | q98 | 1.93 | 1.95 | 1.92 | -0.8% | -2.0% | yes | | q99 | 1.50 | 1.53 | 1.52 | +1.2% | -0.7% | yes | | **Total** | 342.7 | 340.6 | 341.9 | -0.2% | +0.4% | | </details> -- 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]
