jayzhan211 commented on PR #25550: URL: https://github.com/apache/datafusion/pull/25550#issuecomment-5814759561
**Setup.** `D JOIN (A LEFT JOIN B ON a.k = b.k) ON d.k = x.k`, 1M distinct keys spread over the whole range (so row group stats do not prune), both joins `Partitioned` (so the dynamic filter is the `CASE hash % N ... hash_lookup` form). The upper build side `D` holds 100 %, 50 %, 10 % or 1 % of the keys. - L1: A = 1M rows, B = 20M rows. `B` is the probe side, and the copy lands next to the lower join's own dynamic filter. - L2: A = 20M rows, B = 1M rows. The planner makes it a Right join with `B` as the build side, which gets no other filter. Base f7e2db3d9 vs this branch, 5 iterations per query, 3 rounds with the order alternated, median ms. Results are identical in every case. | case | density | pushdown off: branch / base | pushdown on: base ms | branch ms | branch / base per round | |---|---|---|---|---|---| | L1 (B = 20M probe) | 1.0 | 0.98 - 1.05 | 271 | 405 | 1.45 / 1.51 / 1.50 | | | 0.5 | 0.98 - 1.05 | 192 | 198 | 1.03 / 1.15 / 1.08 | | | 0.1 | 0.99 - 1.07 | 145 | 154 | 0.99 / 1.11 / 1.06 | | | 0.01 | 0.99 - 1.08 | 148 | 151 | 1.02 / 1.05 / 1.02 | | L2 (B = 1M build) | 1.0 | 1.00 - 1.04 | 221 | 266 | 1.20 / 1.26 / 1.09 | | | 0.5 | 1.01 - 1.06 | 77 | 73 | 0.86 / 0.99 / 0.90 | | | 0.1 | 1.02 - 1.02 | 67 | 76 | 1.10 / 1.20 / 1.01 | | | 0.01 | 0.99 - 1.03 | 50 | 49 | 1.04 / 0.96 / 0.99 | Each binary's own spread across rounds is 1.01 - 1.07 with pushdown off and up to 1.23 with pushdown on, so only L1 and L2 at density 1.0 are clearly outside noise. So you are right: - With pushdown off (the default) the copy only prunes and costs nothing measurable. - With pushdown on and a non-selective filter, the copy is a second hash lookup on every row of the other scan. L1 at density 1 is 1.5x slower. Base is already 2.2x slower with pushdown on than off for this query (271 vs 123 ms); the copy makes that worse. L1 is also the case where the copy is redundant. When the non-preserved side is the probe side (Left, LeftMark), the join's own dynamic filter already reaches it, and that filter is built from the preserved build side after the original filter pruned it, so it is at least as selective as the copy. The copy only adds information when the non-preserved side is the build side (Right, RightMark), which receives no other filter. That is the TPC-DS Q80 case (`sales LEFT JOIN returns` planned as a Right join with `returns` as build), where the copy prunes 94 % of `store_returns` and `catalog_returns`. -- 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]
