adriangb commented on PR #25727: URL: https://github.com/apache/datafusion/pull/25727#issuecomment-5826244915
## Benchmark summary (E3: adaptive placement) PR head 14efad6b83 vs merge-base 1be6b04 (`main`). PR side: `pushdown_filters = true`, `optional_filter_mode = adaptive`, `adaptive_filter_placement = true`. Times: min of 5 iterations (ms). Ratio = PR / main; **bold** = more than 7% change. Triggers: [vs main pushdown OFF](https://github.com/apache/datafusion/pull/25727#issuecomment-5826061955), [vs main pushdown ON](https://github.com/apache/datafusion/pull/25727#issuecomment-5826062087). This branch does not include #25681. Thus hash join, TopK and aggregate dynamic filters are not optional here: `optional_filter_mode` has no effect on them, and placement treats all conjuncts as required filters. ### Totals ClickBench totals exclude Q23, Q24 and Q26 (they fail on the PR side, see below). | Suite | main OFF | PR vs main OFF | faster / slower >7% | main ON | PR vs main ON | faster / slower >7% | |---|---|---|---|---|---|---| | TPC-H SF1 | 741 | 850 (**1.15**) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826166410) | 0 / 16 | 863 | 860 (1.00) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826165916) | 2 / 1 | | TPC-H SF10 | 6,294 | 7,919 (**1.26**) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826177658) | 2 / 18 | 7,895 | 7,876 (1.00) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826178723) | 4 / 4 | | TPC-DS SF1 | 9,143 | 7,894 (**0.86**) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826177943) | 26 / 37 | 7,843 | 7,881 (1.00) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826177329) | 3 / 2 | | ClickBench partitioned | 16,143 | 15,784 (0.98) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826203741) | 3 / 7 | 16,166 | 16,087 (1.00) [link](https://github.com/apache/datafusion/pull/25727#issuecomment-5826200786) | 10 / 2 | ### Key queries (earlier A5 regressions and wins) | Query | main OFF | PR (vs OFF job) | PR / main OFF | main ON | PR (vs ON job) | PR / main ON | |---|---|---|---|---|---|---| | TPC-H SF1 Q6 | 16.2 | 23.0 | **1.41** | 22.5 | 23.1 | 1.03 | | TPC-H SF1 Q4 | 17.3 | 24.2 | **1.40** | 26.0 | 24.7 | 0.95 | | TPC-H SF1 Q12 | 21.0 | 40.0 | **1.90** | 40.0 | 40.3 | 1.01 | | TPC-H SF1 Q7 | 42.5 | 44.8 | 1.05 | 44.9 | 46.2 | 1.03 | | TPC-H SF10 Q6 | 124 | 199 | **1.61** | 178 | 189 | 1.06 | | TPC-H SF10 Q4 | 111 | 247 | **2.22** | 293 | 252 | **0.86** | | TPC-H SF10 Q12 | 147 | 292 | **1.99** | 293 | 308 | 1.05 | | TPC-H SF10 Q17 | 573 | 487 | **0.85** | 491 | 490 | 1.00 | | TPC-H SF10 Q7 | 443 | 514 | **1.16** | 430 | 528 | **1.23** | | TPC-DS SF1 Q9 | 53.4 | 86.4 | **1.62** | 117 | 84.1 | **0.72** | | TPC-DS SF1 Q82 | 16.7 | 27.0 | **1.62** | 32.0 | 26.3 | **0.82** | | TPC-DS SF1 Q50 | 58.6 | 126 | **2.16** | 124 | 126 | 1.01 | | TPC-DS SF1 Q65 | 128 | 157 | **1.23** | 157 | 158 | 1.00 | | TPC-DS SF1 Q72 | 1,767 | 173 | **0.10** | 171 | 174 | 1.02 | | ClickBench partitioned Q10 | 64.0 | 90.1 | **1.41** | 88.0 | 88.6 | 1.01 | | ClickBench partitioned Q25 | 104 | 98.1 | 0.94 | 134 | 98.7 | **0.74** | | ClickBench partitioned Q24 | 40.2 | FAIL | | 44.3 | FAIL | | | ClickBench partitioned Q26 | 40.8 | FAIL | | 47.6 | FAIL | | | ClickBench partitioned Q23 | 3,041 | FAIL | | 131 | FAIL | | ### Problems - ClickBench Q23, Q24 and Q26 fail on the PR side in both jobs. All three are `ORDER BY "EventTime" LIMIT 10` queries with a static filter (TopK dynamic filter). The bot output does not show the error. - PR vs main pushdown ON is 1.00 in all suites. Placement does not move the TPC-H regressions (Q6, Q4, Q12 are equal to main pushdown ON). Possible cause (not verified): the cost term of `place_required` is only the fetch latency for each row, which is almost zero on local files, so each required conjunct stays a row filter. <details><summary>All queries with more than 7% change vs main pushdown OFF</summary> | Suite | Query | main | PR | ratio | |---|---|---|---|---| | TPC-H SF1 | Q2 | 18.6 | 20.0 | **1.08** | | TPC-H SF1 | Q14 | 24.7 | 26.6 | **1.08** | | TPC-H SF1 | Q19 | 32.0 | 34.9 | **1.09** | | TPC-H SF1 | Q16 | 14.0 | 15.7 | **1.12** | | TPC-H SF1 | Q22 | 14.1 | 15.8 | **1.12** | | TPC-H SF1 | Q10 | 40.6 | 46.1 | **1.13** | | TPC-H SF1 | Q11 | 13.6 | 15.4 | **1.14** | | TPC-H SF1 | Q18 | 61.3 | 69.8 | **1.14** | | TPC-H SF1 | Q8 | 40.3 | 46.8 | **1.16** | | TPC-H SF1 | Q21 | 55.9 | 66.0 | **1.18** | | TPC-H SF1 | Q3 | 28.1 | 34.2 | **1.22** | | TPC-H SF1 | Q9 | 49.0 | 65.0 | **1.33** | | TPC-H SF1 | Q15 | 30.8 | 42.0 | **1.36** | | TPC-H SF1 | Q4 | 17.3 | 24.2 | **1.40** | | TPC-H SF1 | Q6 | 16.2 | 23.0 | **1.41** | | TPC-H SF1 | Q12 | 21.0 | 40.0 | **1.90** | | TPC-H SF10 | Q18 | 694 | 555 | **0.80** | | TPC-H SF10 | Q17 | 573 | 487 | **0.85** | | TPC-H SF10 | Q14 | 169 | 188 | **1.11** | | TPC-H SF10 | Q7 | 443 | 514 | **1.16** | | TPC-H SF10 | Q2 | 89.7 | 105 | **1.17** | | TPC-H SF10 | Q11 | 62.8 | 75.5 | **1.20** | | TPC-H SF10 | Q8 | 352 | 427 | **1.21** | | TPC-H SF10 | Q9 | 510 | 624 | **1.22** | | TPC-H SF10 | Q16 | 63.7 | 84.0 | **1.32** | | TPC-H SF10 | Q5 | 343 | 454 | **1.32** | | TPC-H SF10 | Q20 | 257 | 342 | **1.33** | | TPC-H SF10 | Q13 | 293 | 391 | **1.34** | | TPC-H SF10 | Q15 | 295 | 396 | **1.34** | | TPC-H SF10 | Q21 | 648 | 997 | **1.54** | | TPC-H SF10 | Q10 | 287 | 454 | **1.58** | | TPC-H SF10 | Q6 | 124 | 199 | **1.61** | | TPC-H SF10 | Q3 | 215 | 396 | **1.84** | | TPC-H SF10 | Q12 | 147 | 292 | **1.99** | | TPC-H SF10 | Q22 | 58.2 | 120 | **2.07** | | TPC-H SF10 | Q4 | 111 | 247 | **2.22** | | TPC-DS SF1 | Q72 | 1,767 | 173 | **0.10** | | TPC-DS SF1 | Q8 | 36.7 | 15.2 | **0.41** | | TPC-DS SF1 | Q15 | 55.2 | 24.2 | **0.44** | | TPC-DS SF1 | Q54 | 54.7 | 29.5 | **0.54** | | TPC-DS SF1 | Q2 | 81.7 | 44.6 | **0.55** | | TPC-DS SF1 | Q58 | 110 | 73.2 | **0.66** | | TPC-DS SF1 | Q45 | 38.9 | 26.2 | **0.67** | | TPC-DS SF1 | Q59 | 117 | 79.2 | **0.68** | | TPC-DS SF1 | Q78 | 165 | 118 | **0.72** | | TPC-DS SF1 | Q11 | 284 | 219 | **0.77** | | TPC-DS SF1 | Q4 | 461 | 358 | **0.78** | | TPC-DS SF1 | Q80 | 96.6 | 76.2 | **0.79** | | TPC-DS SF1 | Q57 | 171 | 140 | **0.82** | | TPC-DS SF1 | Q66 | 78.6 | 64.8 | **0.82** | | TPC-DS SF1 | Q40 | 23.8 | 20.6 | **0.87** | | TPC-DS SF1 | Q83 | 33.9 | 29.7 | **0.88** | | TPC-DS SF1 | Q24 | 196 | 172 | **0.88** | | TPC-DS SF1 | Q74 | 167 | 147 | **0.88** | | TPC-DS SF1 | Q20 | 35.7 | 31.9 | **0.89** | | TPC-DS SF1 | Q56 | 39.1 | 35.1 | **0.90** | | TPC-DS SF1 | Q71 | 35.2 | 32.3 | **0.92** | | TPC-DS SF1 | Q98 | 43.1 | 39.8 | **0.92** | | TPC-DS SF1 | Q99 | 66.9 | 61.8 | **0.92** | | TPC-DS SF1 | Q16 | 6.65 | 6.15 | **0.92** | | TPC-DS SF1 | Q43 | 5.13 | 4.75 | **0.93** | | TPC-DS SF1 | Q94 | 39.0 | 36.2 | **0.93** | | TPC-DS SF1 | Q81 | 25.9 | 27.8 | **1.07** | | TPC-DS SF1 | Q33 | 37.6 | 40.3 | **1.07** | | TPC-DS SF1 | Q91 | 44.4 | 47.7 | **1.07** | | TPC-DS SF1 | Q32 | 20.8 | 22.4 | **1.08** | | TPC-DS SF1 | Q25 | 108 | 117 | **1.08** | | TPC-DS SF1 | Q21 | 17.2 | 18.8 | **1.09** | | TPC-DS SF1 | Q51 | 92.8 | 101 | **1.09** | | TPC-DS SF1 | Q96 | 23.9 | 26.5 | **1.11** | | TPC-DS SF1 | Q90 | 16.9 | 18.7 | **1.11** | | TPC-DS SF1 | Q14 | 395 | 441 | **1.11** | | TPC-DS SF1 | Q7 | 74.1 | 83.6 | **1.13** | | TPC-DS SF1 | Q31 | 110 | 125 | **1.14** | | TPC-DS SF1 | Q70 | 105 | 121 | **1.15** | | TPC-DS SF1 | Q35 | 72.5 | 83.5 | **1.15** | | TPC-DS SF1 | Q95 | 79.7 | 93.5 | **1.17** | | TPC-DS SF1 | Q86 | 26.0 | 30.5 | **1.17** | | TPC-DS SF1 | Q76 | 34.9 | 41.1 | **1.18** | | TPC-DS SF1 | Q10 | 61.3 | 72.9 | **1.19** | | TPC-DS SF1 | Q23 | 317 | 382 | **1.21** | | TPC-DS SF1 | Q89 | 35.6 | 43.2 | **1.21** | | TPC-DS SF1 | Q28 | 56.1 | 68.2 | **1.22** | | TPC-DS SF1 | Q65 | 128 | 157 | **1.23** | | TPC-DS SF1 | Q49 | 71.6 | 88.2 | **1.23** | | TPC-DS SF1 | Q87 | 64.1 | 80.0 | **1.25** | | TPC-DS SF1 | Q69 | 56.2 | 70.7 | **1.26** | | TPC-DS SF1 | Q75 | 143 | 182 | **1.27** | | TPC-DS SF1 | Q38 | 62.4 | 79.5 | **1.27** | | TPC-DS SF1 | Q48 | 95.4 | 123 | **1.29** | | TPC-DS SF1 | Q26 | 48.5 | 66.8 | **1.38** | | TPC-DS SF1 | Q64 | 362 | 518 | **1.43** | | TPC-DS SF1 | Q17 | 78.4 | 116 | **1.48** | | TPC-DS SF1 | Q18 | 106 | 162 | **1.53** | | TPC-DS SF1 | Q29 | 96.0 | 148 | **1.54** | | TPC-DS SF1 | Q85 | 104 | 163 | **1.58** | | TPC-DS SF1 | Q9 | 53.4 | 86.4 | **1.62** | | TPC-DS SF1 | Q82 | 16.7 | 27.0 | **1.62** | | TPC-DS SF1 | Q50 | 58.6 | 126 | **2.16** | | ClickBench partitioned | Q22 | 981 | 787 | **0.80** | | ClickBench partitioned | Q32 | 1,112 | 960 | **0.86** | | ClickBench partitioned | Q34 | 1,573 | 1,436 | **0.91** | | ClickBench partitioned | Q42 | 12.9 | 14.1 | **1.09** | | ClickBench partitioned | Q21 | 515 | 566 | **1.10** | | ClickBench partitioned | Q13 | 351 | 403 | **1.15** | | ClickBench partitioned | Q41 | 13.4 | 16.5 | **1.24** | | ClickBench partitioned | Q40 | 14.0 | 17.9 | **1.27** | | ClickBench partitioned | Q11 | 74.4 | 100 | **1.35** | | ClickBench partitioned | Q10 | 64.0 | 90.1 | **1.41** | </details> <details><summary>All queries with more than 7% change vs main pushdown ON</summary> | Suite | Query | main | PR | ratio | |---|---|---|---|---| | TPC-H SF1 | Q5 | 43.7 | 35.6 | **0.81** | | TPC-H SF1 | Q18 | 75.9 | 70.5 | **0.93** | | TPC-H SF1 | Q19 | 32.8 | 35.2 | **1.07** | | TPC-H SF10 | Q21 | 1,219 | 997 | **0.82** | | TPC-H SF10 | Q4 | 293 | 252 | **0.86** | | TPC-H SF10 | Q18 | 651 | 561 | **0.86** | | TPC-H SF10 | Q22 | 135 | 118 | **0.87** | | TPC-H SF10 | Q5 | 429 | 483 | **1.13** | | TPC-H SF10 | Q7 | 430 | 528 | **1.23** | | TPC-H SF10 | Q19 | 238 | 297 | **1.25** | | TPC-H SF10 | Q20 | 274 | 349 | **1.27** | | TPC-DS SF1 | Q9 | 117 | 84.1 | **0.72** | | TPC-DS SF1 | Q82 | 32.0 | 26.3 | **0.82** | | TPC-DS SF1 | Q28 | 74.3 | 68.2 | **0.92** | | TPC-DS SF1 | Q88 | 62.5 | 66.9 | **1.07** | | TPC-DS SF1 | Q54 | 27.7 | 30.2 | **1.09** | | ClickBench partitioned | Q25 | 134 | 98.7 | **0.74** | | ClickBench partitioned | Q35 | 342 | 291 | **0.85** | | ClickBench partitioned | Q7 | 16.1 | 13.7 | **0.85** | | ClickBench partitioned | Q37 | 36.8 | 33.2 | **0.90** | | ClickBench partitioned | Q36 | 70.7 | 64.1 | **0.91** | | ClickBench partitioned | Q40 | 20.4 | 18.6 | **0.91** | | ClickBench partitioned | Q14 | 309 | 282 | **0.91** | | ClickBench partitioned | Q12 | 288 | 263 | **0.91** | | ClickBench partitioned | Q34 | 1,627 | 1,500 | **0.92** | | ClickBench partitioned | Q42 | 15.1 | 14.0 | **0.93** | | ClickBench partitioned | Q4 | 233 | 258 | **1.10** | | ClickBench partitioned | Q9 | 466 | 517 | **1.11** | </details> **Conclusion:** the success criterion does not hold. TPC-H SF1 is 1.15x and TPC-H SF10 is 1.26x vs main pushdown OFF; TPC-DS (0.86x) and ClickBench (0.98x) are faster in total but have many slower queries, and ClickBench has 3 failures. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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]
