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]

Reply via email to