alamb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6177740551
I had claude draw me a picture of the q17 plans
## DataFusion (SF100, measured)
Note: Times are elapsed_compute summed over 14 partitions.
```text
┌──────────────────────────────────┐
│ AggregateExec │
│ sum(l_extendedprice) │
│ out: 1 row │
└────────────────┬─────────────────┘
│ 53.84 K rows
┌────────────────┴─────────────────┐
│ HashJoinExec LeftSemi │
│ on p_partkey = l_partkey │
│ filter l_quantity < 0.2 * avg │
│ build 3.92 s join 74 ms │
│ probe hit rate 0.1% │
└───────┬─────────────────┬────────┘
BUILD │ │ PROBE
601.0 K rows │ │ 20.05 M rows
41.9 MB │ │
┌─────────────────────────┴────┐
┌────────┴─────────────────────────┐
│ HashJoinExec Inner │ │ ProjectionExec
│
│ on p_partkey = l_partkey │ │ 0.2 * avg(l_quantity)
│
│ out: 601.0 K rows │ │ out: 20.00 M rows
│
│ join 3.31 s │
└────────────────┬─────────────────┘
│ probe hit rate 0.1% │ │ 20.00 M
rows
└──────┬───────────────┬───────┘
┌────────────────┴─────────────────┐
BUILD │ │ PROBE │ AggregateExec
FinalPartitioned │
20.03 K rows │ │ 600.0 M │ gby l_partkey,
avg(l_quantity) │
3.2 MB │ │ rows │ in: 600.0 M out: 20.00 M
│
┌──────────────────┴────┐ ┌───────┴────────┐ │ 46.66 s (35 s in group
ids) │
│ RepartitionExec │ │ RepartitionExec│
└────────────────┬─────────────────┘
│ hash(p_partkey) │ │ hash(l_partkey)│ │ 600.0 M
rows
│ out: 20.03 K │ │ 600.0 M rows │
┌────────────────┴─────────────────┐
└──────────────────┬────┘ │ 22.4 GB moved │ │ RepartitionExec
hash(l_partkey) │
│ └───────┬────────┘ │ 600.0 M rows 17.9 GB
moved │
┌──────────────────┴────┐ │
└────────────────┬─────────────────┘
│ FilterExec │ │ │ 600.0 M
rows
│ p_brand = Brand#23 │ │
┌────────────────┴─────────────────┐
│ p_container= MED BOX │ │ │ AggregateExec Partial
│
│ in: 20.00 M │ │ │ gby l_partkey,
avg(l_quantity) │
│ out: 20.03 K │ │ │ in: 600.0 M out: 600.0 M
│
└──────────────────┬────┘ │ │ 598.5 M rows skipped
aggregation│
│ │
└────────────────┬─────────────────┘
┌──────────────────┴────┐ ┌───────┴────────┐ │ 600.0 M
rows
│ DataSourceExec │ │ DataSourceExec │
┌────────────────┴─────────────────┐
│ part │ │ lineitem │ │ DataSourceExec lineitem
│
│ out: 20.00 M rows │ │ out: 600.0 M │ │ out: 600.0 M rows
│
│ │ │ dyn filter: 0 │ │ dyn filter pruned: 0
│
│ │ │ pruned, 10.43 s│ │ 5.76 s
│
└───────────────────────┘ └────────────────┘
└──────────────────────────────────┘
```
# DuckDB v1.5.6 (SF1, measured, extrapoated to SF100 ("≈")
```text
┌──────────────────────────────────┐
│ UNGROUPED_AGGREGATE │
│ sum(l_extendedprice) │
│ out: 1 row │
└────────────────┬─────────────────┘
│ 587 rows (≈ 54 K)
┌────────────────┴─────────────────┐
│ FILTER │
│ l_quantity < 0.2 * avg │
│ in: 6,088 out: 587 │
└────────────────┬─────────────────┘
│ 6,088 rows (≈ 601 K)
┌────────────────┴─────────────────┐
│ HASH_JOIN RIGHT (delim join) │
│ on p_partkey IS NOT DISTINCT │
│ FROM p_partkey │
│ out: 6,088 rows │
└───────┬─────────────────┬────────┘
BUILD │ │ PROBE
6,088 rows │ │ 204 rows (≈ 20
K)
(≈ 601 K) │ │
┌─────────────────────────┴────┐
┌────────┴─────────────────────────┐
│ HASH_JOIN Inner │ │ PROJECTION
│
│ on l_partkey = p_partkey │ │ 0.2 * avg(l_quantity)
│
│ out: 6,088 rows (≈ 601 K) │ │ out: 204 rows
│
│ │
└────────────────┬─────────────────┘
│ │ │ 204 rows
└──────┬───────────────┬───────┘
┌────────────────┴─────────────────┐
BUILD │ │ PROBE │ HASH_GROUP_BY
│
204 rows │ │ 5,986,865│ gby p_partkey,
avg(l_quantity) │
(≈ 20 K) │ │ rows │ in: 6,088 out: 204
│
┌──────────────────┴────┐ ┌───────┴────────┐ │ (≈ 601 K -> 20 K)
│
│ TABLE_SCAN part │ │ TABLE_SCAN │
└────────────────┬─────────────────┘
│ p_brand = Brand#23 │ │ lineitem │ │ 6,088
rows (≈ 601 K)
│ p_container= MED BOX │ │ dyn filter: │
┌────────────────┴─────────────────┐
│ in: 200 K │ │ l_partkey │ │ HASH_JOIN Inner
│
│ out: 204 │ │ BETWEEN 2409 │ │ on l_partkey = p_partkey
│
│ │ │ AND 199510 │ │ out: 6,088 rows
│
└───────────────────────┘ │ in: 6.00 M │
└───────┬─────────────────┬────────┘
│ out: 5,986,865 │ BUILD │ │
PROBE
└────────────────┘ 204 │ │
5,986,865 rows
rows │ │
┌─────────────────┴─────┐
┌────────┴─────────────────────────┐
│ DELIM_SCAN │ │
TABLE_SCAN lineitem │
│ distinct p_partkey │ │ dyn
filter: │
│ of the LHS (the 204 │ │
l_partkey BETWEEN 2409 │
│ matching part keys) │ │ AND
199510 │
│ │ │ in: 6.00
M out: 5,986,865 │
└───────────────────────┘
└──────────────────────────────────┘
```
# Difference
it seems as if one problem is that our dynamic filter isn't being used
during the scan 🤔
<img width="1056" height="843" alt="Image"
src="https://github.com/user-attachments/assets/2b09086a-75c7-420c-a615-0d2fdcd9daf8";
/>
--
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]
