You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2026/10/09 19:33:26 UTC
[I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
alamb opened a new issue, #26169:
URL: https://github.com/apache/datafusion/issues/26169
### Is your feature request related to a problem or challenge?
@ritchie46 posted a blog https://pola.rs/posts/release-polars-2/ recently that provides benchmark numbers for Polars 2.0 that make Datafusion look quite bad:
<img width="1323" height="833" alt="Image" src="https://github.com/user-attachments/assets/a05b95f0-b651-4f09-848b-b1b6902808f8" />
I suspect it is telling that
### Describe the solution you'd like
I would like the performance of DataFusion to be better (obviously)
### Describe alternatives you've considered
My strong suspicion is that the difference is largely caused by the fact that TPC-H and TPC-DS require good join ordering for high performance, something that DataFusion has been historically weak at
I also think this problem is also likely due to the same thing:
> Polars and both DuckDB versions completed all queries. DataFusion timed out on TPC-DS q72 (and once on q67) and ran out of memory on TPC-H q18 on c7a.4xlarge; those queries are excluded from the results above for all engines.
Another corroborating piece of evidence is that the post does not mention ClickBench https://benchmark.clickhouse.com/ where we have focused a huge amount of effort on benchmarking and the difference is much less pronounced. See for example the [results](https://benchmark.clickhouse.com/#system=+ti%20rud|olrP&type=-&machine=-6t|ca2|ae-|6ax|g4e|6ale|3al&cluster_size=-&opensource=-&hardware=+c&tuned=+n&storage=-&metric=combined&queries=-) here:
<img width="1141" height="224" alt="Image" src="https://github.com/user-attachments/assets/f27f9e10-c2e4-40ad-ae2b-c0122b3a59b1" />
### Additional context
_No response_
### Willingness to contribute
None
--
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6100090110
Here are flamegraphs for q18, q17, q11, q07, and q02:
[flamegraphs.zip](https://github.com/user-attachments/files/33287486/flamegraphs.zip)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6113365820
Yeah i looked at it today and IMO the join order was the major issue. I don't have the explain plan available now.
I wanted to check whether there are some "lower" hanging fruits for improvements.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6125013565
[Common Subplan Elinimation](https://github.com/pola-rs/polars/blob/e59ccf142958693058de5880c8f04582c5866df0/crates/polars-plan/src/plans/optimizer/cse/cspe.rs#L18) also seems to make a difference in some queries where the `lineitem` table is scanned multiple times: https://github.com/apache/datafusion/issues/8777
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6087888122
Some related work
- @Dandandan 's work on better JoinOrdering here: https://github.com/apache/datafusion/pull/24456
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6188699498
Running `DATAFUSION_OPTIMIZER_HASH_JOIN_INLIST_PUSHDOWN_MAX_DISTINCT_VALUES=0 DATAFUSION_EXECUTION_PARQUET_PUSHDOWN_FILTERS=true cargo run --profile release-nonlto --bin dfbench -- tpch --query 17 --format parquet --iterations 1 --p
ath ./benchmarks/data/tpch_sf10/` with the patch from https://github.com/apache/datafusion/pull/26249 does put us close to polars (0.41s for polars, 0.5s for datafusion) on my machine.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "adriangb (via GitHub) via github" <gi...@datafusion.apache.org>.
adriangb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6626454770
Btw we've merged https://github.com/apache/datafusion/pull/26249!
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6113886480
Ok I've started my PC before saying something wrong.
Query:
```sql
SELECT
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
GROUP BY
l_orderkey
HAVING
sum(l_quantity) > 300)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
ORDER BY
o_totalprice DESC,
o_orderdate
LIMIT 100;
```
Polars (streaming):
```text
SORT BY [slice: (0, 100), descending: [true, false], nulls_last: [false, true]] [col("o_totalprice"), col("o_orderdate")]
AGGREGATE[maintain_order: false]
[col("l_quantity").sum(null_on_empty=true)] BY [col("c_name"), col("c_custkey"), col("o_orderkey"), col("o_orderdate"), col("o_totalprice")]
FROM
simple π 6/6 ["c_name", "c_custkey", ... 4 other columns]
INNER JOIN:
BUILD SIDE: PreferLeft
LEFT PLAN ON: [col("o_orderkey")]
INNER JOIN:
BUILD SIDE: PreferRight
LEFT PLAN ON: [col("c_custkey")]
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet]
PROJECT 2/8 COLUMNS
SELECTION: col("c_custkey").dynamic_predicate()
ESTIMATED ROWS: 1500000
RIGHT PLAN ON: [col("o_custkey")]
simple π 4/4 ["o_orderkey", "o_totalprice", ... 2 other columns]
SEMI JOIN:
BUILD SIDE: PreferRight
LEFT PLAN ON: [col("o_orderkey")]
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet]
PROJECT 4/9 COLUMNS
SELECTION: col("o_orderkey").dynamic_predicate()
ESTIMATED ROWS: 15000000
RIGHT PLAN ON: [col("l_orderkey")]
simple π 1/1 ["l_orderkey"]
FILTER col("__POLARS_HAVING_0")
FROM
AGGREGATE[maintain_order: false]
[(col("l_quantity").sum(null_on_empty=true) > 300).alias("__POLARS_HAVING_0")] BY [col("l_orderkey")]
FROM
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet]
PROJECT 2/16 COLUMNS
ESTIMATED ROWS: 59986052
END SEMI JOIN
END INNER JOIN
RIGHT PLAN ON: [col("l_orderkey")]
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet]
PROJECT 2/16 COLUMNS
SELECTION: col("l_orderkey").dynamic_predicate()
ESTIMATED ROWS: 59986052
END INNER JOIN
```
Polars (in-memory):
```text
SORT BY [slice: (0, 100), descending: [true, false], nulls_last: [false, true]] [col("o_totalprice"), col("o_orderdate")]
AGGREGATE[maintain_order: false]
[col("l_quantity").sum(null_on_empty=true)] BY [col("c_name"), col("c_custkey"), col("o_orderkey"), col("o_orderdate"), col("o_totalprice")]
FROM
simple π 6/6 ["c_name", "c_custkey", ... 4 other columns]
INNER JOIN:
LEFT PLAN ON: [col("o_orderkey")]
INNER JOIN:
BUILD SIDE: PreferLeft
LEFT PLAN ON: [col("c_custkey")]
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet]
PROJECT 2/8 COLUMNS
ESTIMATED ROWS: 1500000
RIGHT PLAN ON: [col("o_custkey")]
simple π 4/4 ["o_orderkey", "o_totalprice", ... 2 other columns]
SEMI JOIN:
LEFT PLAN ON: [col("o_orderkey")]
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet]
PROJECT 4/9 COLUMNS
ESTIMATED ROWS: 15000000
RIGHT PLAN ON: [col("l_orderkey")]
simple π 1/1 ["l_orderkey"]
FILTER col("__POLARS_HAVING_0")
FROM
AGGREGATE[maintain_order: false]
[(col("l_quantity").sum(null_on_empty=true) > 300).alias("__POLARS_HAVING_0")] BY [col("l_orderkey")]
FROM
CACHE[id: d50bd95e-fd24-4dc5-abdb-26543a5d8a6f]
Parquet SCAN [/home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet]
PROJECT 2/16 COLUMNS
ESTIMATED ROWS: 59986052
END SEMI JOIN
END INNER JOIN
RIGHT PLAN ON: [col("l_orderkey")]
simple π 2/2 ["l_quantity", "l_orderkey"]
CACHE[id: d50bd95e-fd24-4dc5-abdb-26543a5d8a6f]
END INNER JOIN
```
DataFusion:
```text
SortPreservingMergeExec: [o_totalprice@4 DESC, o_orderdate@3 ASC NULLS LAST], fetch=100, metrics=[output_rows=100, elapsed_compute=44.56µs, output_bytes=20.1 KB, output_batches=1]
SortExec: TopK(fetch=100), expr=[o_totalprice@4 DESC, o_orderdate@3 ASC NULLS LAST], preserve_partitioning=[true], metrics=[output_rows=624, elapsed_compute=1.14ms, output_bytes=56.1 KB, output_batches=24, row_replacements=624]
AggregateExec: mode=SinglePartitioned, gby=[c_name@1 as c_name, c_custkey@0 as c_custkey, o_orderkey@2 as o_orderkey, o_orderdate@4 as o_orderdate, o_totalprice@3 as o_totalprice], aggr=[sum(lineitem.l_quantity)], metrics=[output_rows=624, elapsed_compute=1.97ms, output_bytes=66.6 KB, output_batches=24, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=267.9 K, aggregate_arguments_time=19.82µs, aggregation_time=82.10µs, emitting_time=160.07µs, time_calculating_group_ids=1.24ms]
HashJoinExec: mode=Partitioned, join_type=RightSemi, on=[(l_orderkey@0, o_orderkey@2)], metrics=[output_rows=4.37 K, elapsed_compute=13.96s, output_bytes=12.9 MB, output_batches=24, array_map_created_count=0, build_input_batches=24, build_input_rows=624, input_batches=7.33 K, input_rows=59.99 M, build_mem_used=1.59 M, build_time=6.96s, join_time=364.55ms, avg_fanout=100% (4.37 K/4.37 K), probe_hit_rate=0.01% (4.37 K/59.99 M)]
FilterExec: sum(lineitem.l_quantity)@1 > Some(30000),25,2, projection=[l_orderkey@0], metrics=[output_rows=624, elapsed_compute=231.40ms, output_bytes=1536.0 KB, output_batches=24, selectivity=0% (624/15.00 M)]
AggregateExec: mode=FinalPartitioned, gby=[l_orderkey@0 as l_orderkey], aggr=[sum(lineitem.l_quantity)], metrics=[output_rows=15.00 M, elapsed_compute=6.63s, output_bytes=43.3 GB, output_batches=1.85 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=1.91 B, aggregate_arguments_time=2.75ms, aggregation_time=1.41s, emitting_time=15.80ms, time_calculating_group_ids=5.19s]
RepartitionExec: partitioning=Hash([l_orderkey@0], 24), input_partitions=24, metrics=[output_rows=15.00 M, elapsed_compute=4.40ms, output_bytes=346.5 MB, output_batches=1.85 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=10.85s, repartition_time=731.14ms, send_time=2.64s]
AggregateExec: mode=Partial, gby=[l_orderkey@0 as l_orderkey], aggr=[sum(lineitem.l_quantity)], metrics=[output_rows=15.00 M, elapsed_compute=8.69s, output_bytes=43.1 GB, output_batches=1.84 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, skipped_aggregation_rows=0, peak_mem_used=958.3 M, aggregate_arguments_time=5.34ms, aggregation_time=1.27s, emitting_time=6.26ms, time_calculating_group_ids=7.39s, reduction_factor=25.01% (15.00 M/59.99 M)]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:0..106115546], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:106115546..212231092], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:212231092..318346638], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:318346638..424462184], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:424462184..530577730], ...]}, projection=[l_orderkey, l_quantity], file_type=parquet, metrics=[output_rows=59.99 M, elapsed_compute=15.39ms, output_bytes=1382.7 MB, output_batches=7.48 K, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, row_groups_pruned_bloom_filter=524 total → 524 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched
, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_scanned=205.8 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=22.21µs, metadata_load_time=2.79ms, page_index_eval_time=48ns, row_pushdown_eval_time=48ns, statistics_eval_time=48ns, time_elapsed_opening=40.45ms, time_elapsed_processing=1.45s, time_elapsed_scanning_total=10.77s, time_elapsed_scanning_until_data=147.24ms, output_rows_skew=0.03%, scan_efficiency_ratio=8.08% (205.8 M/2.55 B)]
HashJoinExec: mode=Partitioned, join_type=Inner, on=[(o_orderkey@2, l_orderkey@0)], projection=[c_custkey@0, c_name@1, o_orderkey@2, o_totalprice@3, o_orderdate@4, l_quantity@6], metrics=[output_rows=59.99 M, elapsed_compute=15.45s, output_bytes=540.0 GB, output_batches=7.33 K, array_map_created_count=0, build_input_batches=1.85 K, build_input_rows=15.00 M, input_batches=7.33 K, input_rows=59.99 M, build_mem_used=3.10 B, build_time=5.63s, join_time=9.71s, avg_fanout=100% (59.99 M/59.99 M), probe_hit_rate=100% (59.99 M/59.99 M)]
RepartitionExec: partitioning=Hash([o_orderkey@2], 24), input_partitions=24, metrics=[output_rows=15.00 M, elapsed_compute=5.00ms, output_bytes=2.5 GB, output_batches=1.85 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=7.18s, repartition_time=1.00s, send_time=7.73s]
HashJoinExec: mode=Partitioned, join_type=Inner, on=[(c_custkey@0, o_custkey@1)], projection=[c_custkey@0, c_name@1, o_orderkey@2, o_totalprice@4, o_orderdate@5], metrics=[output_rows=15.00 M, elapsed_compute=4.60s, output_bytes=11.5 GB, output_batches=1.84 K, array_map_created_count=0, build_input_batches=192, build_input_rows=1.50 M, input_batches=1.84 K, input_rows=15.00 M, build_mem_used=242.0 M, build_time=195.65ms, join_time=4.39s, avg_fanout=100% (15.00 M/15.00 M), probe_hit_rate=100% (15.00 M/15.00 M)]
RepartitionExec: partitioning=Hash([c_custkey@0], 24), input_partitions=24, metrics=[output_rows=1.50 M, elapsed_compute=421.35µs, output_bytes=179.8 MB, output_batches=192, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=301.66ms, repartition_time=31.25ms, send_time=232.35ms]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:0..5780685], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:5780685..11561370], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:11561370..17342055], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:17342055..23122740], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:23122740..28903425], ...]}, projection=[c_custkey, c_name], file_type=parquet, metrics=[output_rows=1.50 M, elapsed_compute=723.00µs, output_bytes=228.2 MB, output_batches=210, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=35 total → 35 matched, row_groups_pruned_bloom_filter=35 total → 35 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_grou
ps=0 total → 0 matched, batches_split=0, bytes_scanned=19.30 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=1.64µs, metadata_load_time=845.94µs, page_index_eval_time=48ns, row_pushdown_eval_time=48ns, statistics_eval_time=48ns, time_elapsed_opening=9.64ms, time_elapsed_processing=147.08ms, time_elapsed_scanning_total=560.13ms, time_elapsed_scanning_until_data=228.43ms, output_rows_skew=10.91%, scan_efficiency_ratio=13.91% (19.30 M/138.7 M)]
RepartitionExec: partitioning=Hash([o_custkey@1], 24), input_partitions=24, metrics=[output_rows=15.00 M, elapsed_compute=6.10ms, output_bytes=518.9 MB, output_batches=1.84 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=2.81s, repartition_time=426.87ms, send_time=4.97s]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:0..28214315], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:28214315..56428630], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:56428630..84642945], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:84642945..112857260], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:112857260..141071575], ...]}, projection=[o_orderkey, o_custkey, o_totalprice, o_orderdate], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 24 WHEN 0 THEN o_custkey@1 >= 20 AND o_custkey@1 <= 1499995 AND hash_lookup WHEN 1 THEN o_custkey@1 >= 1 AND o_custkey@1 <= 1499982 AND hash_lookup WHEN 2 THEN o_custkey@1 >= 13 AND o_custkey@1 <= 1499952 AND hash_lookup WHEN 3 THEN o_custkey@1 >= 9 AND o_custkey@1 <= 1499974 AND hash_lookup WHEN 4 THE
N o_custkey@1 >= 2 AND o_custkey@1 <= 1499994 AND hash_lookup WHEN 5 THEN o_custkey@1 >= 6 AND o_custkey@1 <= 1499998 AND hash_lookup WHEN 6 THEN o_custkey@1 >= 12 AND o_custkey@1 <= 1499986 AND hash_lookup WHEN 7 THEN o_custkey@1 >= 11 AND o_custkey@1 <= 1499990 AND hash_lookup WHEN 8 THEN o_custkey@1 >= 5 AND o_custkey@1 <= 1499993 AND hash_lookup WHEN 9 THEN o_custkey@1 >= 40 AND o_custkey@1 <= 1499950 AND hash_lookup WHEN 10 THEN o_custkey@1 >= 10 AND o_custkey@1 <= 1499987 AND hash_lookup WHEN 11 THEN o_custkey@1 >= 41 AND o_custkey@1 <= 1499988 AND hash_lookup WHEN 12 THEN o_custkey@1 >= 53 AND o_custkey@1 <= 1499977 AND hash_lookup WHEN 13 THEN o_custkey@1 >= 24 AND o_custkey@1 <= 1499996 AND hash_lookup WHEN 14 THEN o_custkey@1 >= 26 AND o_custkey@1 <= 1499922 AND hash_lookup WHEN 15 THEN o_custkey@1 >= 43 AND o_custkey@1 <= 1499997 AND hash_lookup WHEN 16 THEN o_custkey@1 >= 39 AND o_custkey@1 <= 1499963 AND hash_lookup WHEN 17 THEN o_custkey@1 >= 3 AND o_custkey@1 <= 14999
80 AND hash_lookup WHEN 18 THEN o_custkey@1 >= 77 AND o_custkey@1 <= 1499984 AND hash_lookup WHEN 19 THEN o_custkey@1 >= 16 AND o_custkey@1 <= 1499999 AND hash_lookup WHEN 20 THEN o_custkey@1 >= 4 AND o_custkey@1 <= 1499979 AND hash_lookup WHEN 21 THEN o_custkey@1 >= 17 AND o_custkey@1 <= 1499964 AND hash_lookup WHEN 22 THEN o_custkey@1 >= 15 AND o_custkey@1 <= 1500000 AND hash_lookup WHEN 23 THEN o_custkey@1 >= 30 AND o_custkey@1 <= 1499983 AND hash_lookup ELSE false END ] AND DynamicFilter [ CASE hash_repartition % 24 WHEN 0 THEN o_orderkey@0 >= 1544643 AND o_orderkey@0 <= 59237092 AND o_orderkey@0 IN (SET) ([48314274, 52826116, 53407847, 37683008, 46837987, 15738658, 40753383, 28077922, 57974274, 10442629, 8506019, 20662370, 50839616, 3043270, 11028230, 59237092, 1544643, 56089923, 21057986, 54796358, 49172291, 34343042, 30008928, 44883872, 19730208, 19730468, 37531878]) WHEN 1 THEN o_orderkey@0 >= 2366755 AND o_orderkey@0 <= 57137254 AND o_orderkey@0 IN (SET) ([46486080, 5293568
5, 18353314, 32991271, 8075589, 31018979, 5832321, 20714883, 41064486, 38093280, 41325120, 51929955, 16801991, 13432994, 17016006, 38488484, 2366755, 39492870, 12251526, 21973952, 22470437, 57137254, 24690087, 24923202, 40021697]) WHEN 2 THEN o_orderkey@0 >= 6651397 AND o_orderkey@0 <= 59138305 AND o_orderkey@0 IN (SET) ([46678469, 18152960, 30567815, 18367712, 55342658, 58547488, 59138305, 15960071, 9727175, 16020773, 18762278, 23660263, 23711776, 32161635, 33899427, 56516384, 34590402, 42262528, 6651397, 45053063, 12378086]) WHEN 3 THEN o_orderkey@0 >= 1474818 AND o_orderkey@0 <= 56741287 AND o_orderkey@0 IN (SET) ([53395493, 30702500, 18449123, 55807047, 37828134, 8590657, 41274690, 31587747, 51155008, 51409728, 53623108, 1474818, 54375559, 38643041, 21213895, 54915267, 4527553, 7125602, 36667107, 7456839, 56741287, 22040514, 24378656, 22291718, 37023521]) WHEN 4 THEN o_orderkey@0 >= 2329187 AND o_orderkey@0 <= 53938401 AND o_orderkey@0 IN (SET) ([30864451, 50277955, 10805828, 47
071686, 37874913, 31450375, 31601378, 51532000, 31855814, 53938401, 28916832, 16783360, 44143908, 17272195, 21296295, 29805379, 2329187, 42146816, 44800711, 39949734, 12563331]) WHEN 5 THEN o_orderkey@0 >= 4259524 AND o_orderkey@0 <= 57743845 AND o_orderkey@0 IN (SET) ([17927718, 57743845, 37775877, 51063047, 9615783, 28584225, 52032003, 29294434, 21312615, 38926278, 44331334, 4259524, 12099683, 24319555, 57173283, 49619845, 49704768]) WHEN 6 THEN o_orderkey@0 >= 551136 AND o_orderkey@0 <= 55864096 AND o_orderkey@0 IN (SET) ([46147206, 15373056, 15394692, 53397509, 2662214, 30728833, 40376484, 33140323, 12809282, 20315522, 5489475, 551136, 55864096, 28378659, 23129479, 13393635, 23245603, 47452993, 41662242, 28506404, 53650310, 43438883, 43906817, 52415075, 1971680, 33868740, 48943904, 54765253, 34130272, 39395047, 44890534, 39455748, 39732833, 12237830, 5007490, 27027302, 24581062]) WHEN 7 THEN o_orderkey@0 >= 4478371 AND o_orderkey@0 <= 53972800 AND o_orderkey@0 IN (SET) ([4808560
3, 18188388, 43328641, 33002339, 25332608, 12906020, 28092902, 8574884, 8991333, 9436480, 53972800, 32054309, 21140833, 6210853, 44542087, 6517825, 4478371, 24281478, 49453025, 49519297, 49608135]) WHEN 8 THEN o_orderkey@0 >= 2942469 AND o_orderkey@0 <= 55722822 AND o_orderkey@0 IN (SET) ([42891712, 53101121, 33026341, 27729440, 33162084, 33274246, 55722822, 10889601, 31041542, 2942469, 31192034, 23338693, 41396289, 18869634, 32122243, 52312869, 52566243, 38397669, 13841255, 21530176, 6292038, 14891232, 15036034, 22408480, 37282215]) WHEN 9 THEN o_orderkey@0 >= 1263015 AND o_orderkey@0 <= 58441889 AND o_orderkey@0 IN (SET) ([45581186, 46098247, 15572326, 43207233, 32937312, 7887841, 55229349, 57823175, 22789733, 58441889, 20676097, 2869152, 1263015, 1742403, 29326886, 3342468, 3883783, 14392483, 4720454, 57324993]) WHEN 10 THEN o_orderkey@0 >= 29158 AND o_orderkey@0 <= 59993957 AND o_orderkey@0 IN (SET) ([45889221, 47773829, 35542498, 20038309, 53246051, 43322471, 29158, 27658564, 1
2845285, 5296167, 37707840, 31121184, 41277440, 9304229, 51253283, 16215716, 51841767, 29122912, 59993957, 38562980, 14046658, 4134341, 19546182, 14544192, 32624836, 19851237, 4745607, 4806726, 36828227]) WHEN 11 THEN o_orderkey@0 >= 983201 AND o_orderkey@0 <= 56952741 AND o_orderkey@0 IN (SET) ([47953029, 46685344, 17668135, 17701571, 30417318, 18302693, 30775045, 28184576, 31826535, 31932481, 51759617, 983201, 18927141, 28800353, 16416640, 19157927, 43980355, 32393895, 34201984, 6203745, 6581665, 19427463, 35893095, 14875232, 26465922, 56952741]) WHEN 12 THEN o_orderkey@0 >= 857959 AND o_orderkey@0 <= 58390470 AND o_orderkey@0 IN (SET) ([15157120, 17929698, 32937731, 8231942, 13165024, 58390470, 50776837, 31165796, 15955906, 857959, 43416326, 17301668, 13683589, 17361729, 17612003, 44723233, 44886083, 4290656, 12353700, 26471747, 27531430, 49461377, 49671844]) WHEN 13 THEN o_orderkey@0 >= 6882 AND o_orderkey@0 <= 55876839 AND o_orderkey@0 IN (SET) ([47688132, 48300454, 48301091, 4
8395044, 53092067, 7810823, 6882, 40449575, 5156581, 13185729, 10603495, 10740227, 565574, 55876839, 22897223, 20693186, 43609317, 43952871, 43997089, 20971013, 13563296, 38728164, 48920836, 29749060, 34216164, 42378407, 39353991, 35922529, 4267751, 15104033, 20016800, 4702759, 4722021, 37128899, 27247074]) WHEN 14 THEN o_orderkey@0 >= 7062629 AND o_orderkey@0 <= 59930883 AND o_orderkey@0 IN (SET) ([45460769, 35383045, 37591043, 43245189, 20324257, 32699424, 32766721, 30338976, 30356293, 40685988, 25859617, 58504644, 31105221, 10906593, 9782273, 43423427, 28546531, 59313570, 18923648, 23794694, 59930883, 48862754, 17366275, 56217669, 56240807, 14211942, 44257860, 7062629, 24518212]) WHEN 15 THEN o_orderkey@0 >= 2232932 AND o_orderkey@0 <= 58982979 AND o_orderkey@0 IN (SET) ([47886951, 52679876, 52993379, 25243237, 33414662, 33450849, 58000519, 40876003, 46834406, 5849444, 8754087, 3037414, 58982979, 31831749, 11231077, 54188961, 52458145, 32338533, 54849831, 2232932, 34472643, 34937
604, 26011682, 4439686, 36327201, 7379172, 26977863]) WHEN 16 THEN o_orderkey@0 >= 2745894 AND o_orderkey@0 <= 55747171 AND o_orderkey@0 IN (SET) ([17812705, 46794464, 12701702, 50172704, 10408580, 40705506, 25797412, 55747171, 47058404, 2745894, 2995076, 51031042, 51184261, 11484962, 6082306, 42359809, 3861123, 32579840, 27034435, 7549735, 27580742, 49931207]) WHEN 17 THEN o_orderkey@0 >= 502886 AND o_orderkey@0 <= 54840071 AND o_orderkey@0 IN (SET) ([30768103, 40462630, 8319046, 502886, 20386147, 22855844, 38363558, 31536773, 9816576, 28911364, 54185891, 48526950, 44178691, 38465697, 33928775, 17457767, 54840071, 2199712, 5984582, 6091649, 3767271, 42290181, 44769410, 26587169, 7165026, 22017989, 36910982, 37004583, 22513536, 49739366]) WHEN 18 THEN o_orderkey@0 >= 1481925 AND o_orderkey@0 <= 58919845 AND o_orderkey@0 IN (SET) ([42681408, 18200676, 30332516, 40356833, 46892804, 10858721, 20524164, 2806245, 41068385, 58919845, 31677313, 51553093, 53601824, 11599171, 16424550, 32148
069, 43846177, 1481925, 52469894, 39154146, 42021606, 6515330, 39710883, 36420615, 26707522, 22098118, 22253443, 24979495]) WHEN 19 THEN o_orderkey@0 >= 735366 AND o_orderkey@0 <= 58684996 AND o_orderkey@0 IN (SET) ([47880193, 47969124, 48307430, 5200102, 57968195, 10678725, 735366, 58684996, 967334, 18871616, 19217698, 32305890, 33615872, 21135524, 13837220, 29731045, 34181762, 6222658, 34748294, 19628135, 35999840, 15071267, 39702437, 19960768, 49474597, 49896961]) WHEN 20 THEN o_orderkey@0 >= 1845057 AND o_orderkey@0 <= 59417473 AND o_orderkey@0 IN (SET) ([42997059, 46757697, 30218567, 30615650, 8028004, 40714112, 2761378, 5746311, 8433440, 23062983, 9128386, 41588929, 51041954, 11141606, 51748135, 59417473, 18965830, 19232581, 29089474, 52426951, 38422560, 1845057, 38914978, 34509573, 14614786, 12331750, 36866503, 56968864, 37307554, 27458500, 49864035]) WHEN 21 THEN o_orderkey@0 >= 8073922 AND o_orderkey@0 <= 59057792 AND o_orderkey@0 IN (SET) ([45897379, 8073922, 25109123, 552
26209, 55385158, 28309383, 40944001, 58647078, 58673379, 59057792, 9506693, 43491654, 23906277, 54409761, 38656259, 34142818, 56497062, 12285222, 26458949, 22220514, 24813703, 57524647]) WHEN 22 THEN o_orderkey@0 >= 4739650 AND o_orderkey@0 <= 59565889 AND o_orderkey@0 IN (SET) ([45968358, 46096647, 46226405, 35098820, 55084354, 55799200, 50380160, 30976483, 20649506, 28447970, 9827201, 23671233, 16089345, 23813318, 23957318, 59565889, 33836992, 48881602, 33954756, 34764101, 44232641, 34551296, 14389831, 19738853, 36168806, 12314084, 4739650, 39837541, 6899299, 26584295, 49847329]) WHEN 23 THEN o_orderkey@0 >= 1436544 AND o_orderkey@0 <= 56788359 AND o_orderkey@0 IN (SET) ([45231655, 15191270, 35527463, 53066306, 7611718, 46777222, 10184165, 10612710, 28346247, 23166179, 8639938, 11120293, 16139072, 23861472, 19094241, 1436544, 52340710, 52357185, 29109127, 2096705, 13971749, 14577123, 11785570, 7235014, 56788359, 27529377]) ELSE false END ] AND DynamicFilter [ empty ], metrics=[out
put_rows=15.00 M, elapsed_compute=7.73ms, output_bytes=517.6 MB, output_batches=1.85 K, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=154 total → 154 matched, row_groups_pruned_bloom_filter=154 total → 154 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_scanned=329.4 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=2.82µs, metadata_load_time=682.59µs, page_index_eval_time=48ns, row_pushdown_eval_time=48ns, statistics_eval_time=48ns, time_elapsed_opening=94.44ms, time_elapsed_processing=1.58s, time_elapsed_scanning_total=12.44s, time_elapsed_scanning_until_data=390.64ms, output_rows_skew=0.61%,
scan_efficiency_ratio=48.65% (329.4 M/677.1 M)]
RepartitionExec: partitioning=Hash([l_orderkey@0], 24), input_partitions=24, metrics=[output_rows=59.99 M, elapsed_compute=11.65ms, output_bytes=1375.1 MB, output_batches=7.33 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=1.84s, repartition_time=908.00ms, send_time=1.82s]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:0..106115546], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:106115546..212231092], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:212231092..318346638], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:318346638..424462184], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:424462184..530577730], ...]}, projection=[l_orderkey, l_quantity], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 24 WHEN 0 THEN l_orderkey@0 >= 101 AND l_orderkey@0 <= 59999974 AND hash_lookup WHEN 1 THEN l_orderkey@0 >= 1 AND l_orderkey@0 <= 59999462 AND hash_lookup WHEN 2 THEN l_orderkey@0 >= 513 AND l_orderkey@0 <= 59999939 AND hash_lookup WHEN 3 THEN l_orderkey@0 >= 66 AND l_orderkey@0 <= 59999908 AND hash_lookup WHEN 4 TH
EN l_orderkey@0 >= 2 AND l_orderkey@0 <= 59999623 AND hash_lookup WHEN 5 THEN l_orderkey@0 >= 6 AND l_orderkey@0 <= 59999975 AND hash_lookup WHEN 6 THEN l_orderkey@0 >= 256 AND l_orderkey@0 <= 59999969 AND hash_lookup WHEN 7 THEN l_orderkey@0 >= 160 AND l_orderkey@0 <= 59999874 AND hash_lookup WHEN 8 THEN l_orderkey@0 >= 5 AND l_orderkey@0 <= 59999878 AND hash_lookup WHEN 9 THEN l_orderkey@0 >= 67 AND l_orderkey@0 <= 59999430 AND hash_lookup WHEN 10 THEN l_orderkey@0 >= 257 AND l_orderkey@0 <= 59999968 AND hash_lookup WHEN 11 THEN l_orderkey@0 >= 386 AND l_orderkey@0 <= 59999972 AND hash_lookup WHEN 12 THEN l_orderkey@0 >= 68 AND l_orderkey@0 <= 59999719 AND hash_lookup WHEN 13 THEN l_orderkey@0 >= 1063 AND l_orderkey@0 <= 59999943 AND hash_lookup WHEN 14 THEN l_orderkey@0 >= 352 AND l_orderkey@0 <= 59999905 AND hash_lookup WHEN 15 THEN l_orderkey@0 >= 192 AND l_orderkey@0 <= 59999841 AND hash_lookup WHEN 16 THEN l_orderkey@0 >= 39 AND l_orderkey@0 <= 59999942 AND hash_lookup WHEN 1
7 THEN l_orderkey@0 >= 3 AND l_orderkey@0 <= 59999396 AND hash_lookup WHEN 18 THEN l_orderkey@0 >= 289 AND l_orderkey@0 <= 59999971 AND hash_lookup WHEN 19 THEN l_orderkey@0 >= 135 AND l_orderkey@0 <= 59999940 AND hash_lookup WHEN 20 THEN l_orderkey@0 >= 4 AND l_orderkey@0 <= 59999879 AND hash_lookup WHEN 21 THEN l_orderkey@0 >= 38 AND l_orderkey@0 <= 60000000 AND hash_lookup WHEN 22 THEN l_orderkey@0 >= 675 AND l_orderkey@0 <= 59999745 AND hash_lookup WHEN 23 THEN l_orderkey@0 >= 32 AND l_orderkey@0 <= 59999970 AND hash_lookup ELSE false END ], metrics=[output_rows=59.99 M, elapsed_compute=12.72ms, output_bytes=1382.7 MB, output_batches=7.48 K, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, row_groups_pruned_bloom_filter=524 total → 524 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_scanned=20
5.8 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=2.45µs, metadata_load_time=2.00ms, page_index_eval_time=48ns, row_pushdown_eval_time=48ns, statistics_eval_time=48ns, time_elapsed_opening=50.13ms, time_elapsed_processing=1.14s, time_elapsed_scanning_total=12.49s, time_elapsed_scanning_until_data=52.21ms, output_rows_skew=0.03%, scan_efficiency_ratio=8.08% (205.8 M/2.55 B)]
```
So, we seem to compute the join of all (`customer` x `order` x `lineitem`), most of which are thrown away at right semi join which filters out all small (`sum(l_quantity) < 300`) orders.
Enabling Pushdown Filters:
```text
SortPreservingMergeExec: [o_totalprice@4 DESC, o_orderdate@3 ASC NULLS LAST], fetch=100, metrics=[output_rows=100, elapsed_compute=40.41µs, output_bytes=20.1 KB, output_batches=1]
SortExec: TopK(fetch=100), expr=[o_totalprice@4 DESC, o_orderdate@3 ASC NULLS LAST], preserve_partitioning=[true], metrics=[output_rows=624, elapsed_compute=549.00µs, output_bytes=56.1 KB, output_batches=24, row_replacements=624]
AggregateExec: mode=SinglePartitioned, gby=[c_name@1 as c_name, c_custkey@0 as c_custkey, o_orderkey@2 as o_orderkey, o_orderdate@4 as o_orderdate, o_totalprice@3 as o_totalprice], aggr=[sum(lineitem.l_quantity)], metrics=[output_rows=624, elapsed_compute=872.93µs, output_bytes=66.6 KB, output_batches=24, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=267.9 K, aggregate_arguments_time=15.42µs, aggregation_time=34.82µs, emitting_time=76.25µs, time_calculating_group_ids=487.47µs]
HashJoinExec: mode=Partitioned, join_type=RightSemi, on=[(l_orderkey@0, o_orderkey@2)], metrics=[output_rows=4.37 K, elapsed_compute=15.09s, output_bytes=12.9 MB, output_batches=24, array_map_created_count=0, build_input_batches=24, build_input_rows=624, input_batches=24, input_rows=4.37 K, build_mem_used=1.59 M, build_time=7.71s, join_time=743.03µs, avg_fanout=100% (4.37 K/4.37 K), probe_hit_rate=100% (4.37 K/4.37 K)]
FilterExec: sum(lineitem.l_quantity)@1 > Some(30000),25,2, projection=[l_orderkey@0], metrics=[output_rows=624, elapsed_compute=196.44ms, output_bytes=1536.0 KB, output_batches=24, selectivity=0% (624/15.00 M)]
AggregateExec: mode=FinalPartitioned, gby=[l_orderkey@0 as l_orderkey], aggr=[sum(lineitem.l_quantity)], metrics=[output_rows=15.00 M, elapsed_compute=7.40s, output_bytes=43.3 GB, output_batches=1.85 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, peak_mem_used=1.91 B, aggregate_arguments_time=3.36ms, aggregation_time=1.53s, emitting_time=14.12ms, time_calculating_group_ids=5.84s]
RepartitionExec: partitioning=Hash([l_orderkey@0], 24), input_partitions=24, metrics=[output_rows=15.00 M, elapsed_compute=5.27ms, output_bytes=346.5 MB, output_batches=1.85 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=11.79s, repartition_time=846.65ms, send_time=3.58s]
AggregateExec: mode=Partial, gby=[l_orderkey@0 as l_orderkey], aggr=[sum(lineitem.l_quantity)], metrics=[output_rows=15.00 M, elapsed_compute=9.07s, output_bytes=43.1 GB, output_batches=1.84 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, skipped_aggregation_rows=0, peak_mem_used=958.3 M, aggregate_arguments_time=7.74ms, aggregation_time=1.34s, emitting_time=5.77ms, time_calculating_group_ids=7.69s, reduction_factor=25.01% (15.00 M/59.99 M)]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:0..106115546], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:106115546..212231092], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:212231092..318346638], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:318346638..424462184], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:424462184..530577730], ...]}, projection=[l_orderkey, l_quantity], file_type=parquet, metrics=[output_rows=59.99 M, elapsed_compute=22.61ms, output_bytes=1382.7 MB, output_batches=7.48 K, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, row_groups_pruned_bloom_filter=524 total → 524 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched
, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_scanned=205.8 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=1.72µs, metadata_load_time=2.28ms, page_index_eval_time=48ns, row_pushdown_eval_time=48ns, statistics_eval_time=48ns, time_elapsed_opening=50.13ms, time_elapsed_processing=1.88s, time_elapsed_scanning_total=11.69s, time_elapsed_scanning_until_data=60.62ms, output_rows_skew=0.03%, scan_efficiency_ratio=8.08% (205.8 M/2.55 B)]
HashJoinExec: mode=Partitioned, join_type=Inner, on=[(o_orderkey@2, l_orderkey@0)], projection=[c_custkey@0, c_name@1, o_orderkey@2, o_totalprice@3, o_orderdate@4, l_quantity@6], metrics=[output_rows=4.37 K, elapsed_compute=7.80ms, output_bytes=12.9 MB, output_batches=24, array_map_created_count=0, build_input_batches=24, build_input_rows=624, input_batches=24, input_rows=4.37 K, build_mem_used=10.44 M, build_time=3.66ms, join_time=1.05ms, avg_fanout=100% (4.37 K/4.37 K), probe_hit_rate=100% (4.37 K/4.37 K)]
RepartitionExec: partitioning=Hash([o_orderkey@2], 24), input_partitions=24, metrics=[output_rows=624, elapsed_compute=151.58µs, output_bytes=9.9 MB, output_batches=24, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=29.17s, repartition_time=963.27µs, send_time=4.97ms]
HashJoinExec: mode=Partitioned, join_type=Inner, on=[(c_custkey@0, o_custkey@1)], projection=[c_custkey@0, c_name@1, o_orderkey@2, o_totalprice@4, o_orderdate@5], metrics=[output_rows=624, elapsed_compute=274.77ms, output_bytes=9.9 MB, output_batches=24, array_map_created_count=0, build_input_batches=192, build_input_rows=1.50 M, input_batches=24, input_rows=624, build_mem_used=242.0 M, build_time=261.84ms, join_time=1.90ms, avg_fanout=100% (624/624), probe_hit_rate=100% (624/624)]
RepartitionExec: partitioning=Hash([c_custkey@0], 24), input_partitions=24, metrics=[output_rows=1.50 M, elapsed_compute=506.78µs, output_bytes=179.8 MB, output_batches=192, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=235.35ms, repartition_time=40.98ms, send_time=326.35ms]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:0..5780685], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:5780685..11561370], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:11561370..17342055], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:17342055..23122740], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/customer.parquet:23122740..28903425], ...]}, projection=[c_custkey, c_name], file_type=parquet, metrics=[output_rows=1.50 M, elapsed_compute=896.82µs, output_bytes=228.2 MB, output_batches=210, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=35 total → 35 matched, row_groups_pruned_bloom_filter=35 total → 35 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_grou
ps=0 total → 0 matched, batches_split=0, bytes_scanned=19.30 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=3.68µs, metadata_load_time=15.30ms, page_index_eval_time=48ns, row_pushdown_eval_time=48ns, statistics_eval_time=48ns, time_elapsed_opening=34.13ms, time_elapsed_processing=205.74ms, time_elapsed_scanning_total=574.53ms, time_elapsed_scanning_until_data=126.31ms, output_rows_skew=10.91%, scan_efficiency_ratio=13.91% (19.30 M/138.7 M)]
RepartitionExec: partitioning=Hash([o_custkey@1], 24), input_partitions=24, metrics=[output_rows=624, elapsed_compute=393.10µs, output_bytes=6.8 MB, output_batches=24, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=24.27s, repartition_time=10.80ms, send_time=3.66ms]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:0..28214315], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:28214315..56428630], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:56428630..84642945], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:84642945..112857260], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/orders.parquet:112857260..141071575], ...]}, projection=[o_orderkey, o_custkey, o_totalprice, o_orderdate], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 24 WHEN 0 THEN o_custkey@1 >= 20 AND o_custkey@1 <= 1499995 AND hash_lookup WHEN 1 THEN o_custkey@1 >= 1 AND o_custkey@1 <= 1499982 AND hash_lookup WHEN 2 THEN o_custkey@1 >= 13 AND o_custkey@1 <= 1499952 AND hash_lookup WHEN 3 THEN o_custkey@1 >= 9 AND o_custkey@1 <= 1499974 AND hash_lookup WHEN 4 THE
N o_custkey@1 >= 2 AND o_custkey@1 <= 1499994 AND hash_lookup WHEN 5 THEN o_custkey@1 >= 6 AND o_custkey@1 <= 1499998 AND hash_lookup WHEN 6 THEN o_custkey@1 >= 12 AND o_custkey@1 <= 1499986 AND hash_lookup WHEN 7 THEN o_custkey@1 >= 11 AND o_custkey@1 <= 1499990 AND hash_lookup WHEN 8 THEN o_custkey@1 >= 5 AND o_custkey@1 <= 1499993 AND hash_lookup WHEN 9 THEN o_custkey@1 >= 40 AND o_custkey@1 <= 1499950 AND hash_lookup WHEN 10 THEN o_custkey@1 >= 10 AND o_custkey@1 <= 1499987 AND hash_lookup WHEN 11 THEN o_custkey@1 >= 41 AND o_custkey@1 <= 1499988 AND hash_lookup WHEN 12 THEN o_custkey@1 >= 53 AND o_custkey@1 <= 1499977 AND hash_lookup WHEN 13 THEN o_custkey@1 >= 24 AND o_custkey@1 <= 1499996 AND hash_lookup WHEN 14 THEN o_custkey@1 >= 26 AND o_custkey@1 <= 1499922 AND hash_lookup WHEN 15 THEN o_custkey@1 >= 43 AND o_custkey@1 <= 1499997 AND hash_lookup WHEN 16 THEN o_custkey@1 >= 39 AND o_custkey@1 <= 1499963 AND hash_lookup WHEN 17 THEN o_custkey@1 >= 3 AND o_custkey@1 <= 14999
80 AND hash_lookup WHEN 18 THEN o_custkey@1 >= 77 AND o_custkey@1 <= 1499984 AND hash_lookup WHEN 19 THEN o_custkey@1 >= 16 AND o_custkey@1 <= 1499999 AND hash_lookup WHEN 20 THEN o_custkey@1 >= 4 AND o_custkey@1 <= 1499979 AND hash_lookup WHEN 21 THEN o_custkey@1 >= 17 AND o_custkey@1 <= 1499964 AND hash_lookup WHEN 22 THEN o_custkey@1 >= 15 AND o_custkey@1 <= 1500000 AND hash_lookup WHEN 23 THEN o_custkey@1 >= 30 AND o_custkey@1 <= 1499983 AND hash_lookup ELSE false END ] AND DynamicFilter [ CASE hash_repartition % 24 WHEN 0 THEN o_orderkey@0 >= 1544643 AND o_orderkey@0 <= 59237092 AND o_orderkey@0 IN (SET) ([37683008, 28077922, 52826116, 40753383, 15738658, 53407847, 50839616, 57974274, 10442629, 3043270, 20662370, 48314274, 56089923, 21057986, 1544643, 59237092, 8506019, 49172291, 37531878, 11028230, 34343042, 30008928, 54796358, 46837987, 19730208, 19730468, 44883872]) WHEN 1 THEN o_orderkey@0 >= 2366755 AND o_orderkey@0 <= 57137254 AND o_orderkey@0 IN (SET) ([32991271, 3809328
0, 52935685, 13432994, 18353314, 8075589, 20714883, 5832321, 38488484, 39492870, 21973952, 22470437, 31018979, 41064486, 51929955, 57137254, 41325120, 2366755, 40021697, 16801991, 46486080, 17016006, 24690087, 24923202, 12251526]) WHEN 2 THEN o_orderkey@0 >= 6651397 AND o_orderkey@0 <= 59138305 AND o_orderkey@0 IN (SET) ([55342658, 18152960, 18367712, 15960071, 30567815, 16020773, 58547488, 23660263, 59138305, 23711776, 56516384, 33899427, 18762278, 6651397, 9727175, 34590402, 42262528, 46678469, 32161635, 12378086, 45053063]) WHEN 3 THEN o_orderkey@0 >= 1474818 AND o_orderkey@0 <= 56741287 AND o_orderkey@0 IN (SET) ([37828134, 53395493, 55807047, 18449123, 30702500, 36667107, 38643041, 21213895, 1474818, 4527553, 22040514, 8590657, 51155008, 22291718, 37023521, 51409728, 56741287, 53623108, 41274690, 31587747, 24378656, 54375559, 54915267, 7125602, 7456839]) WHEN 4 THEN o_orderkey@0 >= 2329187 AND o_orderkey@0 <= 53938401 AND o_orderkey@0 IN (SET) ([37874913, 50277955, 30864451, 10
805828, 21296295, 39949734, 51532000, 28916832, 53938401, 29805379, 31450375, 44143908, 2329187, 31601378, 42146816, 31855814, 16783360, 17272195, 47071686, 44800711, 12563331]) WHEN 5 THEN o_orderkey@0 >= 4259524 AND o_orderkey@0 <= 57743845 AND o_orderkey@0 IN (SET) ([37775877, 17927718, 57743845, 38926278, 21312615, 4259524, 28584225, 51063047, 29294434, 49619845, 52032003, 57173283, 49704768, 9615783, 24319555, 44331334, 12099683]) WHEN 6 THEN o_orderkey@0 >= 551136 AND o_orderkey@0 <= 55864096 AND o_orderkey@0 IN (SET) ([40376484, 12809282, 2662214, 28378659, 15373056, 15394692, 33140323, 53397509, 13393635, 55864096, 551136, 23129479, 20315522, 23245603, 30728833, 5489475, 39395047, 39455748, 48943904, 33868740, 28506404, 39732833, 34130272, 43438883, 53650310, 43906817, 1971680, 41662242, 52415075, 46147206, 5007490, 54765253, 24581062, 27027302, 47452993, 44890534, 12237830]) WHEN 7 THEN o_orderkey@0 >= 4478371 AND o_orderkey@0 <= 53972800 AND o_orderkey@0 IN (SET) ([2809290
2, 12906020, 33002339, 25332608, 18188388, 43328641, 48085603, 21140833, 4478371, 8574884, 6210853, 8991333, 6517825, 9436480, 49453025, 49519297, 49608135, 53972800, 24281478, 32054309, 44542087]) WHEN 8 THEN o_orderkey@0 >= 2942469 AND o_orderkey@0 <= 55722822 AND o_orderkey@0 IN (SET) ([27729440, 33026341, 2942469, 53101121, 33162084, 33274246, 42891712, 55722822, 13841255, 23338693, 10889601, 38397669, 21530176, 22408480, 37282215, 18869634, 6292038, 14891232, 31041542, 15036034, 31192034, 41396289, 52312869, 52566243, 32122243]) WHEN 9 THEN o_orderkey@0 >= 1263015 AND o_orderkey@0 <= 58441889 AND o_orderkey@0 IN (SET) ([32937312, 55229349, 2869152, 15572326, 57823175, 43207233, 22789733, 7887841, 45581186, 20676097, 58441889, 3342468, 3883783, 1263015, 4720454, 14392483, 29326886, 1742403, 57324993, 46098247]) WHEN 10 THEN o_orderkey@0 >= 29158 AND o_orderkey@0 <= 59993957 AND o_orderkey@0 IN (SET) ([27658564, 37707840, 12845285, 29158, 20038309, 53246051, 35542498, 43322471, 1
4046658, 47773829, 5296167, 16215716, 45889221, 38562980, 4134341, 4745607, 4806726, 36828227, 51253283, 14544192, 29122912, 59993957, 9304229, 31121184, 51841767, 41277440, 19546182, 32624836, 19851237]) WHEN 11 THEN o_orderkey@0 >= 983201 AND o_orderkey@0 <= 56952741 AND o_orderkey@0 IN (SET) ([28184576, 17668135, 17701571, 18302693, 30417318, 35893095, 47953029, 30775045, 16416640, 983201, 26465922, 34201984, 28800353, 6203745, 18927141, 51759617, 14875232, 56952741, 6581665, 19157927, 43980355, 31826535, 31932481, 19427463, 46685344, 32393895]) WHEN 12 THEN o_orderkey@0 >= 857959 AND o_orderkey@0 <= 58390470 AND o_orderkey@0 IN (SET) ([15157120, 32937731, 17929698, 50776837, 13683589, 13165024, 15955906, 8231942, 58390470, 857959, 4290656, 26471747, 43416326, 49461377, 31165796, 49671844, 17301668, 17361729, 17612003, 44723233, 44886083, 12353700, 27531430]) WHEN 13 THEN o_orderkey@0 >= 6882 AND o_orderkey@0 <= 55876839 AND o_orderkey@0 IN (SET) ([40449575, 6882, 5156581, 530920
67, 13185729, 13563296, 55876839, 22897223, 7810823, 565574, 47688132, 35922529, 10603495, 20693186, 10740227, 48300454, 48301091, 48395044, 38728164, 20971013, 4267751, 39353991, 48920836, 4702759, 4722021, 34216164, 37128899, 43609317, 43952871, 43997089, 29749060, 15104033, 42378407, 27247074, 20016800]) WHEN 14 THEN o_orderkey@0 >= 7062629 AND o_orderkey@0 <= 59930883 AND o_orderkey@0 IN (SET) ([37591043, 32699424, 32766721, 40685988, 30338976, 30356293, 45460769, 25859617, 35383045, 43245189, 14211942, 20324257, 58504644, 23794694, 56217669, 56240807, 48862754, 59313570, 28546531, 18923648, 59930883, 43423427, 31105221, 10906593, 9782273, 24518212, 44257860, 17366275, 7062629]) WHEN 15 THEN o_orderkey@0 >= 2232932 AND o_orderkey@0 <= 58982979 AND o_orderkey@0 IN (SET) ([52679876, 52993379, 25243237, 33414662, 33450849, 40876003, 58000519, 47886951, 3037414, 36327201, 5849444, 26011682, 58982979, 4439686, 8754087, 2232932, 52458145, 54188961, 11231077, 34472643, 31831749, 349376
04, 54849831, 32338533, 46834406, 7379172, 26977863]) WHEN 16 THEN o_orderkey@0 >= 2745894 AND o_orderkey@0 <= 55747171 AND o_orderkey@0 IN (SET) ([12701702, 2745894, 40705506, 50172704, 17812705, 25797412, 55747171, 10408580, 2995076, 3861123, 51031042, 51184261, 6082306, 49931207, 11484962, 42359809, 46794464, 47058404, 27034435, 32579840, 7549735, 27580742]) WHEN 17 THEN o_orderkey@0 >= 502886 AND o_orderkey@0 <= 54840071 AND o_orderkey@0 IN (SET) ([40462630, 22855844, 502886, 38363558, 20386147, 8319046, 30768103, 38465697, 3767271, 48526950, 22017989, 33928775, 36910982, 5984582, 26587169, 37004583, 6091649, 28911364, 22513536, 49739366, 9816576, 2199712, 44178691, 54185891, 31536773, 42290181, 54840071, 17457767, 7165026, 44769410]) WHEN 18 THEN o_orderkey@0 >= 1481925 AND o_orderkey@0 <= 58919845 AND o_orderkey@0 IN (SET) ([40356833, 2806245, 30332516, 42681408, 18200676, 20524164, 16424550, 10858721, 36420615, 58919845, 1481925, 39154146, 22098118, 39710883, 22253443, 267075
22, 51553093, 6515330, 53601824, 41068385, 43846177, 52469894, 31677313, 42021606, 11599171, 32148069, 46892804, 24979495]) WHEN 19 THEN o_orderkey@0 >= 735366 AND o_orderkey@0 <= 58684996 AND o_orderkey@0 IN (SET) ([5200102, 13837220, 57968195, 735366, 47880193, 35999840, 47969124, 10678725, 48307430, 967334, 58684996, 21135524, 33615872, 39702437, 34181762, 18871616, 6222658, 19217698, 49474597, 29731045, 49896961, 15071267, 34748294, 32305890, 19628135, 19960768]) WHEN 20 THEN o_orderkey@0 >= 1845057 AND o_orderkey@0 <= 59417473 AND o_orderkey@0 IN (SET) ([30218567, 2761378, 40714112, 42997059, 23062983, 8028004, 30615650, 38422560, 5746311, 38914978, 8433440, 51041954, 36866503, 59417473, 14614786, 37307554, 18965830, 51748135, 29089474, 9128386, 56968864, 19232581, 1845057, 41588929, 49864035, 11141606, 52426951, 34509573, 46757697, 12331750, 27458500]) WHEN 21 THEN o_orderkey@0 >= 8073922 AND o_orderkey@0 <= 59057792 AND o_orderkey@0 IN (SET) ([28309383, 55226209, 55385158, 25
109123, 8073922, 45897379, 58647078, 58673379, 59057792, 38656259, 23906277, 56497062, 22220514, 26458949, 34142818, 43491654, 40944001, 9506693, 57524647, 54409761, 24813703, 12285222]) WHEN 22 THEN o_orderkey@0 >= 4739650 AND o_orderkey@0 <= 59565889 AND o_orderkey@0 IN (SET) ([55084354, 50380160, 35098820, 55799200, 16089345, 20649506, 36168806, 23671233, 23813318, 23957318, 48881602, 4739650, 33836992, 28447970, 33954756, 14389831, 39837541, 26584295, 59565889, 30976483, 49847329, 9827201, 45968358, 46096647, 34551296, 34764101, 46226405, 6899299, 44232641, 19738853, 12314084]) WHEN 23 THEN o_orderkey@0 >= 1436544 AND o_orderkey@0 <= 56788359 AND o_orderkey@0 IN (SET) ([15191270, 28346247, 53066306, 7611718, 45231655, 35527463, 13971749, 10184165, 23166179, 16139072, 10612710, 1436544, 23861472, 8639938, 14577123, 56788359, 29109127, 19094241, 2096705, 52340710, 11120293, 52357185, 46777222, 11785570, 7235014, 27529377]) ELSE false END ] AND DynamicFilter [ empty ], metrics=[out
put_rows=624, elapsed_compute=3.44ms, output_bytes=24.6 KB, output_batches=154, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=154 total → 154 matched, row_groups_pruned_bloom_filter=154 total → 154 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_scanned=311.2 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=624, pushdown_rows_pruned=15.00 M, predicate_cache_inner_records=38.65 M, predicate_cache_records=2.68 K, bloom_filter_eval_time=1.52ms, metadata_load_time=2.92ms, page_index_eval_time=48ns, row_pushdown_eval_time=19.10s, statistics_eval_time=48ns, time_elapsed_opening=91.31ms, time_elapsed_processing=22.91s, time_elapsed_scanning_total=24.20s, time_elapsed_scanning_until_data=2.74s, output_rows_skew=
2.02%, scan_efficiency_ratio=45.95% (311.2 M/677.1 M)]
RepartitionExec: partitioning=Hash([l_orderkey@0], 24), input_partitions=24, metrics=[output_rows=4.37 K, elapsed_compute=231.88µs, output_bytes=4.5 MB, output_batches=24, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=19.32s, repartition_time=6.12ms, send_time=4.10ms]
DataSourceExec: file_groups={24 groups: [[home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:0..106115546], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:106115546..212231092], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:212231092..318346638], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:318346638..424462184], [home/tschwarzinger/bartgeier/polars-2.0-benchmark/data/tpch-10/lineitem.parquet:424462184..530577730], ...]}, projection=[l_orderkey, l_quantity], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 24 WHEN 0 THEN l_orderkey@0 >= 1544643 AND l_orderkey@0 <= 59237092 AND l_orderkey@0 IN (SET) ([49172291, 21057986, 28077922, 30008928, 48314274, 59237092, 56089923, 1544643, 15738658, 37531878, 20662370, 11028230, 40753383, 52826116, 53407847, 3043270, 57974274, 10442629, 37683008, 44883872, 8506019, 34343042, 5479
6358, 50839616, 46837987, 19730468, 19730208]) WHEN 1 THEN l_orderkey@0 >= 2366755 AND l_orderkey@0 <= 57137254 AND l_orderkey@0 IN (SET) ([41064486, 41325120, 13432994, 24690087, 57137254, 38093280, 31018979, 18353314, 46486080, 12251526, 40021697, 38488484, 22470437, 24923202, 51929955, 8075589, 39492870, 5832321, 52935685, 32991271, 2366755, 20714883, 21973952, 17016006, 16801991]) WHEN 2 THEN l_orderkey@0 >= 6651397 AND l_orderkey@0 <= 59138305 AND l_orderkey@0 IN (SET) ([15960071, 12378086, 42262528, 16020773, 18367712, 56516384, 6651397, 23711776, 9727175, 55342658, 30567815, 59138305, 32161635, 45053063, 58547488, 23660263, 34590402, 33899427, 18152960, 46678469, 18762278]) WHEN 3 THEN l_orderkey@0 >= 1474818 AND l_orderkey@0 <= 56741287 AND l_orderkey@0 IN (SET) ([54375559, 37023521, 7456839, 30702500, 24378656, 41274690, 51409728, 37828134, 53623108, 36667107, 8590657, 4527553, 7125602, 53395493, 56741287, 21213895, 22040514, 51155008, 55807047, 31587747, 22291718, 38643041
, 18449123, 54915267, 1474818]) WHEN 4 THEN l_orderkey@0 >= 2329187 AND l_orderkey@0 <= 53938401 AND l_orderkey@0 IN (SET) ([10805828, 21296295, 28916832, 2329187, 44800711, 50277955, 30864451, 31450375, 17272195, 16783360, 53938401, 37874913, 51532000, 31855814, 42146816, 47071686, 12563331, 31601378, 39949734, 44143908, 29805379]) WHEN 5 THEN l_orderkey@0 >= 4259524 AND l_orderkey@0 <= 57743845 AND l_orderkey@0 IN (SET) ([29294434, 37775877, 57173283, 21312615, 24319555, 49619845, 57743845, 38926278, 51063047, 52032003, 28584225, 44331334, 9615783, 49704768, 17927718, 12099683, 4259524]) WHEN 6 THEN l_orderkey@0 >= 551136 AND l_orderkey@0 <= 55864096 AND l_orderkey@0 IN (SET) ([23129479, 43906817, 41662242, 12237830, 39732833, 46147206, 24581062, 44890534, 52415075, 34130272, 33140323, 551136, 5007490, 15373056, 53397509, 43438883, 28506404, 39455748, 5489475, 55864096, 30728833, 40376484, 39395047, 23245603, 15394692, 20315522, 27027302, 13393635, 2662214, 12809282, 1971680, 2837
8659, 47452993, 53650310, 33868740, 54765253, 48943904]) WHEN 7 THEN l_orderkey@0 >= 4478371 AND l_orderkey@0 <= 53972800 AND l_orderkey@0 IN (SET) ([49453025, 32054309, 44542087, 43328641, 28092902, 9436480, 49519297, 49608135, 4478371, 18188388, 8574884, 53972800, 12906020, 25332608, 6517825, 33002339, 24281478, 6210853, 48085603, 21140833, 8991333]) WHEN 8 THEN l_orderkey@0 >= 2942469 AND l_orderkey@0 <= 55722822 AND l_orderkey@0 IN (SET) ([33274246, 18869634, 15036034, 38397669, 52312869, 52566243, 31192034, 53101121, 10889601, 13841255, 32122243, 41396289, 14891232, 2942469, 23338693, 33162084, 21530176, 33026341, 31041542, 27729440, 55722822, 37282215, 22408480, 42891712, 6292038]) WHEN 9 THEN l_orderkey@0 >= 1263015 AND l_orderkey@0 <= 58441889 AND l_orderkey@0 IN (SET) ([3883783, 29326886, 58441889, 15572326, 14392483, 7887841, 57823175, 3342468, 43207233, 55229349, 45581186, 32937312, 2869152, 1742403, 22789733, 20676097, 1263015, 57324993, 46098247, 4720454]) WHEN 10 THEN
l_orderkey@0 >= 29158 AND l_orderkey@0 <= 59993957 AND l_orderkey@0 IN (SET) ([29122912, 59993957, 14046658, 4134341, 38562980, 14544192, 4806726, 12845285, 20038309, 5296167, 53246051, 4745607, 31121184, 47773829, 32624836, 51841767, 9304229, 51253283, 29158, 37707840, 27658564, 43322471, 16215716, 36828227, 19851237, 35542498, 41277440, 45889221, 19546182]) WHEN 11 THEN l_orderkey@0 >= 983201 AND l_orderkey@0 <= 56952741 AND l_orderkey@0 IN (SET) ([34201984, 6581665, 18927141, 32393895, 56952741, 17701571, 35893095, 31826535, 31932481, 43980355, 30775045, 28184576, 983201, 46685344, 30417318, 26465922, 19427463, 18302693, 51759617, 16416640, 19157927, 47953029, 6203745, 17668135, 14875232, 28800353]) WHEN 12 THEN l_orderkey@0 >= 857959 AND l_orderkey@0 <= 58390470 AND l_orderkey@0 IN (SET) ([44723233, 17301668, 32937731, 13683589, 8231942, 50776837, 26471747, 15157120, 49461377, 44886083, 43416326, 857959, 17361729, 31165796, 12353700, 49671844, 17612003, 58390470, 13165024, 17929
698, 27531430, 15955906, 4290656]) WHEN 13 THEN l_orderkey@0 >= 6882 AND l_orderkey@0 <= 55876839 AND l_orderkey@0 IN (SET) ([47688132, 35922529, 42378407, 43952871, 43997089, 565574, 27247074, 22897223, 5156581, 48300454, 13563296, 55876839, 4702759, 29749060, 48395044, 4722021, 40449575, 48920836, 4267751, 48301091, 10603495, 34216164, 13185729, 20971013, 37128899, 20016800, 43609317, 53092067, 15104033, 6882, 10740227, 39353991, 7810823, 20693186, 38728164]) WHEN 14 THEN l_orderkey@0 >= 7062629 AND l_orderkey@0 <= 59930883 AND l_orderkey@0 IN (SET) ([56240807, 37591043, 43423427, 48862754, 44257860, 43245189, 23794694, 18923648, 30356293, 28546531, 59930883, 35383045, 7062629, 17366275, 40685988, 45460769, 20324257, 14211942, 24518212, 30338976, 9782273, 56217669, 32766721, 32699424, 10906593, 58504644, 59313570, 31105221, 25859617]) WHEN 15 THEN l_orderkey@0 >= 2232932 AND l_orderkey@0 <= 58982979 AND l_orderkey@0 IN (SET) ([52679876, 7379172, 3037414, 4439686, 5849444, 36327201
, 58000519, 52993379, 26977863, 11231077, 8754087, 54849831, 34937604, 46834406, 2232932, 33450849, 47886951, 26011682, 33414662, 58982979, 40876003, 54188961, 32338533, 52458145, 25243237, 31831749, 34472643]) WHEN 16 THEN l_orderkey@0 >= 2745894 AND l_orderkey@0 <= 55747171 AND l_orderkey@0 IN (SET) ([10408580, 2745894, 6082306, 47058404, 51031042, 7549735, 51184261, 40705506, 17812705, 55747171, 42359809, 25797412, 12701702, 3861123, 50172704, 32579840, 49931207, 2995076, 27034435, 27580742, 11484962, 46794464]) WHEN 17 THEN l_orderkey@0 >= 502886 AND l_orderkey@0 <= 54840071 AND l_orderkey@0 IN (SET) ([40462630, 44769410, 49739366, 9816576, 54185891, 38363558, 20386147, 48526950, 44178691, 7165026, 17457767, 8319046, 37004583, 36910982, 42290181, 22855844, 22513536, 31536773, 6091649, 22017989, 54840071, 502886, 3767271, 2199712, 38465697, 30768103, 28911364, 5984582, 33928775, 26587169]) WHEN 18 THEN l_orderkey@0 >= 1481925 AND l_orderkey@0 <= 58919845 AND l_orderkey@0 IN (SET)
([11599171, 26707522, 39154146, 31677313, 30332516, 20524164, 32148069, 6515330, 58919845, 22253443, 36420615, 42681408, 53601824, 43846177, 46892804, 39710883, 51553093, 16424550, 40356833, 1481925, 42021606, 10858721, 22098118, 18200676, 2806245, 41068385, 24979495, 52469894]) WHEN 19 THEN l_orderkey@0 >= 735366 AND l_orderkey@0 <= 58684996 AND l_orderkey@0 IN (SET) ([35999840, 39702437, 47969124, 10678725, 13837220, 18871616, 47880193, 5200102, 735366, 19628135, 19960768, 57968195, 58684996, 19217698, 967334, 34748294, 29731045, 49896961, 15071267, 32305890, 49474597, 6222658, 34181762, 33615872, 48307430, 21135524]) WHEN 20 THEN l_orderkey@0 >= 1845057 AND l_orderkey@0 <= 59417473 AND l_orderkey@0 IN (SET) ([38914978, 1845057, 18965830, 11141606, 29089474, 41588929, 12331750, 56968864, 37307554, 49864035, 5746311, 42997059, 38422560, 9128386, 19232581, 51041954, 46757697, 8028004, 2761378, 40714112, 23062983, 27458500, 34509573, 14614786, 30615650, 52426951, 8433440, 51748135,
59417473, 36866503, 30218567]) WHEN 21 THEN l_orderkey@0 >= 8073922 AND l_orderkey@0 <= 59057792 AND l_orderkey@0 IN (SET) ([55385158, 59057792, 38656259, 9506693, 22220514, 43491654, 23906277, 54409761, 55226209, 57524647, 45897379, 25109123, 8073922, 28309383, 58673379, 56497062, 58647078, 40944001, 24813703, 26458949, 34142818, 12285222]) WHEN 22 THEN l_orderkey@0 >= 4739650 AND l_orderkey@0 <= 59565889 AND l_orderkey@0 IN (SET) ([33836992, 44232641, 34551296, 34764101, 55084354, 23813318, 6899299, 39837541, 46226405, 19738853, 50380160, 59565889, 35098820, 20649506, 23671233, 55799200, 28447970, 36168806, 12314084, 48881602, 49847329, 14389831, 23957318, 45968358, 46096647, 16089345, 26584295, 30976483, 9827201, 33954756, 4739650]) WHEN 23 THEN l_orderkey@0 >= 1436544 AND l_orderkey@0 <= 56788359 AND l_orderkey@0 IN (SET) ([11785570, 19094241, 53066306, 52357185, 27529377, 8639938, 35527463, 7611718, 10612710, 28346247, 13971749, 23861472, 23166179, 52340710, 11120293, 46777222,
56788359, 16139072, 14577123, 15191270, 10184165, 1436544, 7235014, 45231655, 29109127, 2096705]) ELSE false END ], metrics=[output_rows=4.37 K, elapsed_compute=3.04ms, output_bytes=104.1 KB, output_batches=372, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, row_groups_pruned_bloom_filter=524 total → 524 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_scanned=168.9 M, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=4.37 K, pushdown_rows_pruned=59.98 M, predicate_cache_inner_records=59.18 M, predicate_cache_records=4.37 K, bloom_filter_eval_time=5.39µs, metadata_load_time=2.40ms, page_index_eval_time=48ns, row_pushdown_eval_time=16.61s, statistics_eval_time=48ns, time_elapsed_openi
ng=199.90ms, time_elapsed_processing=18.30s, time_elapsed_scanning_total=19.14s, time_elapsed_scanning_until_data=2.51s, output_rows_skew=2.36%, scan_efficiency_ratio=6.63% (168.9 M/2.55 B)]
```
I think this hides much of the join ordering problems. For example, the second `lineitems` scan only returns `4.37 K` elements. But the processing time in the scans increases by quite some time, eliminating most of the reads (e.g., `row_pushdown_eval_time=16.61s` in the last scan). It seems like DF can't prune any of the row groups: `files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, row_groups_pruned_bloom_filter=524 total → 524 matched,`. Not sure how to get this info in Polars.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6131123295
The `DynamicFilterPhysicalExpr` issues seems like same issue we've been tracking for a while: it depends on the selectivity of the join, and we have no mechanism to make them dynamic.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub) via github" <gi...@datafusion.apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6643687460
https://github.com/apache/datafusion/issues/27108 Reverting https://github.com/apache/datafusion/pull/25790 fixes the Q18 regression. Here is tpch sf100 without that change with filter pushdown on:
| query | polars | DataFusion 55.1.0 | main without 25790 | main / polars |
|---|---:|---:|---:|---:|
| q01 | 2.02 | 2.68 | 2.60 | 1.29x |
| q02 | 0.17 | 0.73 | 0.58 | 3.46x |
| q03 | 1.52 | 2.05 | 1.82 | 1.20x |
| q04 | 0.76 | 1.03 | 1.28 | 1.68x |
| q05 | 1.69 | 3.87 | 2.19 | 1.29x |
| q06 | 0.62 | 0.90 | 1.21 | 1.95x |
| q07 | 1.96 | 6.90 | 2.11 | 1.07x |
| q08 | 2.80 | 5.13 | 4.78 | 1.71x |
| q09 | 4.87 | 10.68 | 7.46 | 1.53x |
| q10 | 2.44 | 2.41 | 2.26 | 0.93x |
| q11 | 0.16 | 0.70 | 0.41 | 2.52x |
| q12 | 0.65 | 0.86 | 1.37 | 2.10x |
| q13 | 2.29 | 2.40 | 2.78 | 1.21x |
| q14 | 1.01 | 1.48 | 1.24 | 1.23x |
| q15 | 0.96 | 3.13 | 2.83 | 2.95x |
| q16 | 0.44 | 0.48 | 0.43 | 0.99x |
| q17 | 1.41 | 6.71 | 2.18 | 1.54x |
| q18 | 2.60 | 6.98 | 6.06 | 2.33x |
| q19 | 1.10 | 1.47 | 1.60 | 1.46x |
| q20 | 1.15 | 2.07 | 1.57 | 1.37x |
| q21 | 3.64 | 11.99 | 14.19 | 3.90x |
| q22 | 0.42 | 0.42 | 0.47 | 1.12x |
| **total** | **34.7** | **75.1** | **61.4** | **1.77x** |
Cut down the queries by ~14s :)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "rluvaton (via GitHub) via github" <gi...@datafusion.apache.org>.
rluvaton commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6719372446
I'm ready to crash the competition (respectfully 😉)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "comphead (via GitHub) via github" <gi...@datafusion.apache.org>.
comphead commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6744783772
### DataFusion vs Polars, TPC-H SF1000
This follows up on the DataFusion SF1000 numbers above. Polars ran on the same hardware against the same data.
**Setup**
- DataFusion `57.0.0-dev`, `main` at [`a616831`](https://github.com/apache/datafusion/commit/a616831122aa525ed843ee3c5e11f4803f557c98), `target_partitions = 32`, `memory_limit = 105G` (greedy pool)
- Polars `2.1.0` at [`7f902bd`](https://github.com/pola-rs/polars/commit/7f902bd3ffd27ff82d14278a09a295ff2f7aed92), streaming engine, `POLARS_TEMP_DIR=/data`, no explicit memory limit
- Hardware: k8s pod, amd64, 32 cores
- Data: TPC-H SF1000 Parquet, zstd, 512 MB blocks, partitioned, read from S3
- 3 iterations per query. The times below are mean seconds.
**Results**
| Query | DataFusion | Polars | DF / Polars |
|---|---:|---:|---:|
| Q01 | 46.17 | 26.53 | 1.74x |
| Q02 | 37.71 | 6.96 | 5.42x |
| Q03 | 31.25 | 23.54 | 1.33x |
| Q04 | 22.92 | 41.69 | 0.55x |
| Q05 | 53.85 | 34.42 | 1.56x |
| Q06 | 4.76 | 4.71 | 1.01x |
| Q07 | 61.13 | 38.12 | 1.60x |
| Q08 | 54.80 | 40.72 | 1.35x |
| Q09 | OOM | 73.52 | |
| Q10 | 37.20 | deadlock | |
| Q11 | 13.44 | 9.21 | 1.46x |
| Q12 | 14.86 | 24.57 | 0.60x |
| Q13 | 29.77 | 39.82 | 0.75x |
| Q14 | 4.01 | 3.23 | 1.24x |
| Q15 | 16.55 | 3.05 | 5.43x |
| Q16 | 11.52 | 9.18 | 1.25x |
| Q17 | 75.21 | 59.78 | 1.26x |
| Q18 | OOM | deadlock | |
| Q19 | 31.41 | 28.47 | 1.10x |
| Q20 | 24.35 | 10.06 | 2.42x |
| Q21 | OOM | deadlock | |
| Q22 | 22.63 | 18.68 | 1.21x |
Both engines finished 18 queries. Over those 18, DataFusion took 556.4 s and Polars took 422.7 s (1.32x). The geometric means are 23.9 s and 16.8 s (1.42x).
```mermaid
%%{init: {"themeVariables": {"xyChart": {"plotColorPalette": "#2a78d6, #8a8984"}}}}%%
xychart-beta
title "DataFusion time / Polars time, SF1000 (above 1 = Polars faster)"
x-axis ["Q01", "Q02", "Q03", "Q04", "Q05", "Q06", "Q07", "Q08", "Q11", "Q12", "Q13", "Q14", "Q15", "Q16", "Q17", "Q19", "Q20", "Q22"]
y-axis "DF / Polars" 0 --> 6
bar [1.74, 5.42, 1.33, 0.55, 1.56, 1.01, 1.60, 1.35, 1.46, 0.60, 0.75, 1.24, 5.43, 1.25, 1.26, 1.10, 2.42, 1.21]
line [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
```
Observations:
- DataFusion is faster on Q04, Q12 and Q13. It is about even on Q06.
- The largest gaps are Q02 and Q15 (about 5.4x) and Q20 (2.4x).
- DataFusion failed Q09, Q18 and Q21 with hash join build OOMs at 105G, as described above. Polars completed Q09 in 73.5 s.
- Polars deadlocked on Q10, Q18 and Q21 and reported no result for them.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6196175262
I had codex do an investigation on this, take it with a grain or two of salt:
`Q17 investigation completed: dynamic filters reject 99.90% of rows when Parquet row filtering is enabled; costly partitioned CASE evaluation and inflated cardinality estimates explain poor performance. SF10 benchmarks validated results, with shared build tables reducing median time from 657 to 276 ms. No Q17 fix was implemented; PR #26249 remains untested locally.`
[Q17.md](https://github.com/user-attachments/files/33502201/Q17.md)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6197907049
> did you use `DATAFUSION_OPTIMIZER_HASH_JOIN_INLIST_PUSHDOWN_MAX_DISTINCT_VALUES=0` for these measurements on the new branch? I think you need that for the other expression to trigger for smaller scale factors.
<p>Yes — tested with <code>DATAFUSION_OPTIMIZER_HASH_JOIN_INLIST_PUSHDOWN_MAX_DISTINCT_VALUES=0</code>, and confirmed that it activated the PR’s faster expression. However, the main on/off table in my previous answer used the default IN-list threshold.</p>
<p>For PR #26249, at SF10 with 24 partitions and Parquet row filtering enabled:</p>
Configuration | Median runtime | Filter expression
-- | -- | --
Join filters enabled, default IN-list threshold | 708.0 ms | 24-branch CASE
Join filters enabled, IN-list threshold = 0 | 332.1 ms | partitioned_hash_lookup(24)
Join filters disabled | 866.0 ms | None
The PR substantially helps when threshold zero activates its optimized path.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6195043288
@tschwarzinger
> This could be because the relevant lineitems of the parts are actually distributed over all data pages
I think this is a likely case. My current assumption (which could be wrong!) Is because we are joining on `l_partkey` and it's unordered across all pages we cannot prune properly.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6197475670
> How would folks feel about disabling hash join dynamic filters by default?
I think you've got a better overview what it takes to make them more performant, so these are just my 2c. I would lean towards keeping them enabled by default because switching back will likely trigger a regression somewhere that could be difficult to resolve. I think with a faster implementation of the multi partition `hash_lookup` expression would go a long way.
However, maybe rethinking the approach around `datafusion.optimizer.hash_join_inlist_pushdown_max_distinct_values` (or its default value) could help. Maybe we should only use `IN LIST` if the build side across all partitions is small enough such that copying all build sides into a single `IN LIST` expression is inexpensive (i.e., no hash table routing necessary).
I also think enabling it dynamically also makes sense, as computing the hashes and probing the table twice can incur overhead if we cannot save on decoding and aggregation (like in Q17). But I guess the cost model could be tricky to get right.
> as shown in https://github.com/apache/datafusion/issues/26169#issuecomment-6188699498
Sorry if I wasn't clear, my comment should say that enabling dynamic filters + filter pushdown with #26249 dramatically improves performance. `DATAFUSION_OPTIMIZER_HASH_JOIN_INLIST_PUSHDOWN_MAX_DISTINCT_VALUES` just disables the rule that replaces the `hash_lookup` expression with `IN LIST` which then causes the current code in #26249 to not use the faster implementation.
@Omega359 did you use `DATAFUSION_OPTIMIZER_HASH_JOIN_INLIST_PUSHDOWN_MAX_DISTINCT_VALUES=0` for these measurements? I think you need that for the other expression to trigger for smaller scale factors.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6088056606
Some other thoughts:
1. The first thing to do would be to get a full breakdown of the benchmark results and profile the queries where DataFusion is slower
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub) via github" <gi...@datafusion.apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6637560826
> 29\. [@devanbenz](https://github.com/devanbenz) do you still have the code for running the benchmarks and comparing it against the newest main? If you do, could you re-run it so we can re-evaluate priorities?
Yep, I have them on my personal laptop but at work right now so I'll take a peek later today.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6134798328
I have an experiment at #26249 where instead of a `CASE` statement to route probe rows to the hash tables of a partitioned hash join, it uses a specialized expression that routes by row index. `CASE` copies the batch's columns to isolate each branch's rows, this new expression only ever touches the hashes making sure we don't need to perform the copies. It appears to improve performance overall for most TPC-H queries, with Q17 showing a huge improvement:
- main: 5.57s
- #26249: 2.20s
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6112007731
Never mind, it *is* our standard benchmarks. So I guess like Andrew said more focus on TPC-H / TPC-DS.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "comphead (via GitHub) via github" <gi...@datafusion.apache.org>.
comphead commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6775100279
> THe first thing I recommend doing is get the plans for both engines and look at the join orders. If the join orders aren't the same, that is the first thing to look into
Created subtasks for slowest 4 queries, lets see if there is a low hanging fruits there.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "comphead (via GitHub) via github" <gi...@datafusion.apache.org>.
comphead commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6744781906
### DataFusion vs Polars, TPC-H SF1000
This follows up on the DataFusion SF1000 numbers above. Polars ran on the same hardware against the same data.
**Setup**
- DataFusion `57.0.0-dev`, `main` at [`a616831`](https://github.com/apache/datafusion/commit/a616831122aa525ed843ee3c5e11f4803f557c98), `target_partitions = 32`, `memory_limit = 105G` (greedy pool)
- Polars `2.1.0` at [`7f902bd`](https://github.com/pola-rs/polars/commit/7f902bd3ffd27ff82d14278a09a295ff2f7aed92), streaming engine, `POLARS_TEMP_DIR=/data`, no explicit memory limit
- Hardware: k8s pod, amd64, 32 cores
- Data: TPC-H SF1000 Parquet, zstd, 512 MB blocks, partitioned, read from S3
- 3 iterations per query. The times below are mean seconds.
**Results**
| Query | DataFusion | Polars | DF / Polars |
|---|---:|---:|---:|
| Q01 | 46.17 | 26.53 | 1.74x |
| Q02 | 37.71 | 6.96 | 5.42x |
| Q03 | 31.25 | 23.54 | 1.33x |
| Q04 | 22.92 | 41.69 | 0.55x |
| Q05 | 53.85 | 34.42 | 1.56x |
| Q06 | 4.76 | 4.71 | 1.01x |
| Q07 | 61.13 | 38.12 | 1.60x |
| Q08 | 54.80 | 40.72 | 1.35x |
| Q09 | OOM | 73.52 | |
| Q10 | 37.20 | deadlock | |
| Q11 | 13.44 | 9.21 | 1.46x |
| Q12 | 14.86 | 24.57 | 0.60x |
| Q13 | 29.77 | 39.82 | 0.75x |
| Q14 | 4.01 | 3.23 | 1.24x |
| Q15 | 16.55 | 3.05 | 5.43x |
| Q16 | 11.52 | 9.18 | 1.25x |
| Q17 | 75.21 | 59.78 | 1.26x |
| Q18 | OOM | deadlock | |
| Q19 | 31.41 | 28.47 | 1.10x |
| Q20 | 24.35 | 10.06 | 2.42x |
| Q21 | OOM | deadlock | |
| Q22 | 22.63 | 18.68 | 1.21x |
Both engines finished 18 queries. Over those 18, DataFusion took 556.4 s and Polars took 422.7 s (1.32x). The geometric means are 23.9 s and 16.8 s (1.42x).
```mermaid
%%{init: {"themeVariables": {"xyChart": {"plotColorPalette": "#2a78d6, #8a8984"}}}}%%
xychart-beta
title "DataFusion time / Polars time, SF1000 (above 1 = Polars faster)"
x-axis ["Q01", "Q02", "Q03", "Q04", "Q05", "Q06", "Q07", "Q08", "Q11", "Q12", "Q13", "Q14", "Q15", "Q16", "Q17", "Q19", "Q20", "Q22"]
y-axis "DF / Polars" 0 --> 6
bar [1.74, 5.42, 1.33, 0.55, 1.56, 1.01, 1.60, 1.35, 1.46, 0.60, 0.75, 1.24, 5.43, 1.25, 1.26, 1.10, 2.42, 1.21]
line [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
```
Observations:
- DataFusion is faster on Q04, Q12 and Q13. It is about even on Q06.
- The largest gaps are Q02 and Q15 (about 5.4x) and Q20 (2.4x).
- DataFusion failed Q09, Q18 and Q21 with hash join build OOMs at 105G, as described above. Polars completed Q09 in 73.5 s.
- Polars deadlocked on Q10, Q18 and Q21 and reported no result for them.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub) via github" <gi...@datafusion.apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6639409952
@tschwarzinger @adriangb As of right now there is a regression for Q18 on main it's timing out when I attempt to run it against a 60s timeout. @adriangb are you aware of any issues tracking this regression? I found https://github.com/apache/datafusion/issues/13188 and a few PRs associated with it such as https://github.com/apache/datafusion/pull/27092.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6111694072
Would be interesting to see whether some non-default options (e.g., filter pushdown https://github.com/apache/datafusion/issues/3463 ) can help. Perhaps I'll investigate a bit tomorrow.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6111933216
Should we start by adding their benchmarks to our suite, maybe even as part of the standard benchmark runs?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6097964702
Running the benchmark locally on an M3 macbook: https://github.com/pola-rs/polars-2.0-benchmark
<img width="672" height="370" alt="Image" src="https://github.com/user-attachments/assets/b112dba2-13b3-4e41-878c-9af3944e2c10" />
Raw data:
[datafusion_tpch_100.txt](https://github.com/user-attachments/files/33282780/datafusion_tpch_100.txt)
[polars_tpch_100.txt](https://github.com/user-attachments/files/33282779/polars_tpch_100.txt)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6196425496
How would folks feel about disabling hash join dynamic filters by default? I would like to work on making them cheaper to evaluate and dynamically deciding when they are worth evaluating or not, but until then it seems like they are hurting more than they are helping.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6197067356
| Build | Parquet row filtering | Join filters on | Join filters off | Effect |
|---|---|---:|---:|---|
| Baseline | Off | 897.1 ms | 857.8 ms | 4.4% faster |
| PR #26249 | Off | 897.8 ms | 849.6 ms | 5.4% faster |
| Baseline | On | 689.4 ms | 865.8 ms | 25.6% slower |
| PR #26249 | On | 708.0 ms | 866.0 ms | 22.3% slower |
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub) via github" <gi...@datafusion.apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6722961404
> I have set up 1TB TPCH sandbox for latest datafusion on `main` (`a616831`)
>
> - DataFusion `57.0.0-dev`, `main` at [`a616831`](https://github.com/apache/datafusion/commit/a616831122aa525ed843ee3c5e11f4803f557c98) (2026-11-06)
> - Hardware: k8s pod, amd64, 32 cores
> - Data: TPC-H SF1000 Parquet, zstd, 512 MB blocks, partitioned, read from S3
> - `target_partitions = 32`
> - `datafusion.runtime.memory_limit = 105G` (greedy pool)
> - `datafusion.runtime.temp_directory = /data`, `max_temp_directory_size = 900G`
> - `datafusion.runtime.metadata_cache_limit = 2G`, `list_files_cache_limit = 256M`, `file_statistics_cache_limit = 256M`
> - 3 iterations per query. The times below are in seconds.
>
> **Results**
>
> | Query | Mean | Min | Max | Status |
> |---|---:|---:|---:|---|
> | Q01 | 46.17 | 45.86 | 46.38 | ok |
> | Q02 | 37.71 | 35.83 | 41.20 | ok |
> | Q03 | 31.25 | 29.53 | 34.22 | ok |
> | Q04 | 22.92 | 22.58 | 23.18 | ok |
> | Q05 | 53.85 | 47.61 | 64.85 | ok |
> | Q06 | 4.76 | 4.63 | 4.84 | ok |
> | Q07 | 61.13 | 59.27 | 62.28 | ok |
> | Q08 | 54.80 | 53.02 | 56.15 | ok |
> | Q09 | | | | **failed (OOM)** |
> | Q10 | 37.20 | 36.27 | 38.02 | ok |
> | Q11 | 13.44 | 12.13 | 14.64 | ok |
> | Q12 | 14.86 | 13.94 | 15.80 | ok |
> | Q13 | 29.77 | 29.43 | 30.32 | ok |
> | Q14 | 4.01 | 3.50 | 4.57 | ok |
> | Q15 | 16.55 | 10.93 | 22.36 | ok |
> | Q16 | 11.52 | 10.22 | 13.70 | ok |
> | Q17 | 75.21 | 74.55 | 76.05 | ok |
> | Q18 | | | | **failed (OOM)** |
> | Q19 | 31.41 | 30.92 | 31.77 | ok |
> | Q20 | 24.35 | 23.23 | 25.99 | ok |
> | Q21 | | | | **failed (OOM)** |
> | Q22 | 22.63 | 20.17 | 24.50 | ok |
>
> 19 of 22 queries passed. The sum of their means is 593.6 s and the geometric mean is 24.4 s.
>
> **Failures**
>
> Q09, Q18 and Q21 failed on all 3 iterations with `Resources exhausted` while building hash join input. `HashJoinInput` reports `can spill: false`, so the 105G pool fills up with build-side reservations of similar size, one per partition.
>
> - **Q09**: `Failed to allocate additional 763.0 MB for HashJoinInput[24] with 763.0 MB already allocated for this reservation - 361.1 MB remain available for the total memory pool: greedy(used: 104.6 GB, pool_size: 105.0 GB)`. The top consumers are `HashJoinInput` partitions at 2.1 GB each.
> - **Q18**: `Failed to allocate additional 1440.0 KB for HashJoinInput[4] with 2.5 GB already allocated for this reservation - 247.2 KB remain available for the total memory pool: greedy(used: 105.0 GB, pool_size: 105.0 GB)`. The top consumers are `HashJoinInput` partitions at 2.5 GB each.
> - **Q21**: `Failed to allocate additional 4.7 GB for HashJoinInput[24] with 0.0 B already allocated for this reservation - 1591.6 MB remain available for the total memory pool: greedy(used: 103.4 GB, pool_size: 105.0 GB)`. The top consumers are `HashJoinInput` partitions at 6.5 GB each.
>
>
> I'll run polars soon, so we can track weak DF parts and define next steps
Is this with or without filter pushdown? Might be good to do a run with filter pushdown on as well if not 🤔
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub) via github" <gi...@datafusion.apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6628175536
@devanbenz do you still have the code for running the benchmarks and comparing it against the newest main? If you do, could you re-run it so we can re-evaluate priorities?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6113257799
Do we have an explain analyze for q18?
It would shock me if the problem wasn't with the join order that is selected
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6125269091
Taking a look at Q17 without `filter_pushdown`:
```
SELECT
sum(l_extendedprice) / 7.0 AS avg_yearly
FROM
lineitem,
part
WHERE
p_partkey = l_partkey
AND p_brand = 'Brand#23'
AND p_container = 'MED BOX'
AND l_quantity < (
SELECT
0.2 * avg(l_quantity)
FROM
lineitem
WHERE
l_partkey = p_partkey)
ProjectionExec: expr=[CAST(sum(lineitem.l_extendedprice)@0 AS Float64) / 7 as avg_yearly], metrics=[output_rows=1, elapsed_compute=2.29µs, output_bytes=8.0 B, output_batches=1, expr_0_eval_time=2.00µs]
AggregateExec: mode=Final, gby=[], aggr=[sum(lineitem.l_extendedprice)], metrics=[output_rows=1, elapsed_compute=5.25µs, output_bytes=16.0 B, output_batches=1, agg_expr_0_arguments_time=2.58µs, agg_expr_0_evaluate_time=83ns, agg_expr_0_merge_time=545ns, output_rows_skew=0%]
CoalescePartitionsExec, metrics=[output_rows=14, elapsed_compute=9.04µs, output_bytes=224.0 B, output_batches=14]
AggregateExec: mode=Partial, gby=[], aggr=[sum(lineitem.l_extendedprice)], metrics=[output_rows=14, elapsed_compute=40.58µs, output_bytes=224.0 B, output_batches=14, agg_expr_0_arguments_time=4.33µs, agg_expr_0_state_time=6.54µs, agg_expr_0_update_time=16.38µs, output_rows_skew=0%]
HashJoinExec: mode=Partitioned, join_type=LeftSemi, on=[(p_partkey@2, l_partkey@1)], filter=CAST(l_quantity@0 AS Decimal128(30, 15)) < Float64(0.2) * avg(lineitem.l_quantity)@1, projection=[l_extendedprice@1], metrics=[output_rows=53.84 K, elapsed_compute=3.99s, output_bytes=1792.0 KB, output_batches=14, build_mem_used=41.9 MB, array_map_created_count=0, build_input_batches=84, build_input_rows=601.0 K, input_batches=2.46 K, input_rows=20.05 M, build_time=3.92s, join_time=74.09ms, avg_fanout=3000.26% (601.0 K/20.03 K), output_rows_skew=0.06%, probe_hit_rate=0.1% (20.03 K/20.00 M)]
HashJoinExec: mode=Partitioned, join_type=Inner, on=[(p_partkey@0, l_partkey@0)], projection=[l_quantity@2, l_extendedprice@3, p_partkey@0], metrics=[output_rows=601.0 K, elapsed_compute=3.31s, output_bytes=26.2 MB, output_batches=84, build_mem_used=3.2 MB, array_map_created_count=0, build_input_batches=14, build_input_rows=20.03 K, input_batches=73.25 K, input_rows=600.0 M, build_time=9.89ms, join_time=3.31s, avg_fanout=100% (601.0 K/601.0 K), output_rows_skew=0.05%, probe_hit_rate=0.1% (601.0 K/600.0 M)]
RepartitionExec: partitioning=Hash([p_partkey@0], 14), input_partitions=14, metrics=[output_rows=20.03 K, elapsed_compute=37.39µs, output_bytes=896.0 KB, output_batches=14, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=274.15ms, repartition_time=772.50µs, send_time=48.87µs, output_rows_skew=0.06%]
FilterExec: p_brand@1 = Brand#23 AND p_container@2 = MED BOX, projection=[p_partkey@0], metrics=[output_rows=20.03 K, elapsed_compute=82.92ms, output_bytes=896.0 KB, output_batches=14, selectivity=0.1% (20.03 K/20.00 M)]
DataSourceExec: file_groups={14 groups: [[Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:0..49193775], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:49193775..98387550], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:98387550..147581325], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:147581325..196775100], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:196775100..245968875], ...]}, projection=[p_partkey, p_brand, p_container], file_type=parquet, predicate=p_brand@3 = Brand#23 AND p_container@6 = MED BOX, pruning_predicate=p_brand_null_count@2 != row_count@3 AND p_brand_min@0 <= Brand#23 AND Brand#23 <= p_brand_max@1 AND p_container_null_count@6 != row_count@3 AND p_container_min@4 <= MED BOX AND MED BOX <= p_container_max@5, required_guarantees=[p_brand in (Brand#23), p_container in (MED BOX)], metrics=[output_rows=20.00 M, el
apsed_compute=144.64ms, output_bytes=777.7 MB, output_batches=2.48 K, files_ranges_pruned_statistics=14 total → 14 matched, row_groups_pruned_statistics=191 total → 191 matched, page_index_pages_pruned=1.15 K total → 1.15 K matched, page_index_rows_pruned=20.00 M total → 20.00 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=656.8 MB, bytes_scanned=141.5 MB, file_open_errors=0, file_scan_errors=0, files_opened=14, files_processed=14, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=8.53µs, metadata_load_time=1.84ms, page_index_eval_time=2.02ms, row_pushdown_eval_time=42ns, statistics_eval_time=667.32µs, time_elapsed_opening=7.06ms, time_elapsed_processing=153.55ms, time_elapsed_scanning_total=266.43ms, time_elapsed_scanning_until_data=12.78ms, output_r
ows_skew=0.13%, scan_efficiency_ratio=21.55% (148.4 M/688.7 M)]
RepartitionExec: partitioning=Hash([l_partkey@0], 14), input_partitions=14, metrics=[output_rows=600.0 M, elapsed_compute=32.65ms, output_bytes=22.4 GB, output_batches=73.25 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=13.90s, repartition_time=3.17s, send_time=3.84s, output_rows_skew=0%]
DataSourceExec: file_groups={14 groups: [[Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:0..1938998887], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:1938998887..3877997774], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:3877997774..5816996661], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:5816996661..7755995548], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:7755995548..9694994435], ...]}, projection=[l_partkey, l_quantity, l_extendedprice], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 14 WHEN 0 THEN l_partkey@1 >= 4880 AND l_partkey@1 <= 19999654 AND hash_lookup WHEN 1 THEN l_partkey@1 >= 2409 AND l_partkey@1 <= 19978138 AND hash_lookup WHEN 2 THEN l_partkey@1 >= 12919 AND l_partkey@1 <= 19966033 AND hash_lookup WHEN 3 THEN l_partkey@1 >= 6079 AND l_partkey@1 <= 19988248 AND hash
_lookup WHEN 4 THEN l_partkey@1 >= 11200 AND l_partkey@1 <= 19983248 AND hash_lookup WHEN 5 THEN l_partkey@1 >= 21574 AND l_partkey@1 <= 19986826 AND hash_lookup WHEN 6 THEN l_partkey@1 >= 12554 AND l_partkey@1 <= 19994996 AND hash_lookup WHEN 7 THEN l_partkey@1 >= 10687 AND l_partkey@1 <= 19968087 AND hash_lookup WHEN 8 THEN l_partkey@1 >= 11256 AND l_partkey@1 <= 19999539 AND hash_lookup WHEN 9 THEN l_partkey@1 >= 26396 AND l_partkey@1 <= 19999987 AND hash_lookup WHEN 10 THEN l_partkey@1 >= 26889 AND l_partkey@1 <= 19942847 AND hash_lookup WHEN 11 THEN l_partkey@1 >= 2425 AND l_partkey@1 <= 19981418 AND hash_lookup WHEN 12 THEN l_partkey@1 >= 7682 AND l_partkey@1 <= 19973698 AND hash_lookup WHEN 13 THEN l_partkey@1 >= 20870 AND l_partkey@1 <= 19992676 AND hash_lookup ELSE false END ], dynamic_rg_pruning=eligible, pruning_predicate=l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 4880 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999654 AND l_partke
y_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1407/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2409 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19978138 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1456/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12919 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19966033 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1400/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 6079 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19988248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1465/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 11200 AND l_partkey_null_count@1 != row
_count@2 AND l_partkey_min@3 <= 19983248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1396/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 21574 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19986826 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1489/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12554 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19994996 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1422/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 10687 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19968087 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1397/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_p
artkey_max@0 >= 11256 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999539 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1444/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26396 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999987 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1473/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26889 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19942847 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1395/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2425 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19981418 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1460/1048576 buc
kets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 7682 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19973698 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1382/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 20870 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19992676 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1427/1048576 buckets) OR false, required_guarantees=[], metrics=[output_rows=600.0 M, elapsed_compute=10.43s, output_bytes=22.5 GB, output_batches=75.83 K, files_ranges_pruned_statistics=14 total → 14 matched, row_groups_pruned_statistics=5.23 K total → 5.23 K matched, page_index_pages_pruned=31.39 K total → 31.39 K matched, page_index_rows_pruned=600.0 M total → 600.0 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=25.3 GB, bytes_sc
anned=8.8 GB, file_open_errors=0, file_scan_errors=0, files_opened=14, files_processed=14, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=82.07µs, metadata_load_time=1.69ms, page_index_eval_time=20.66ms, row_pushdown_eval_time=42ns, statistics_eval_time=7.48ms, time_elapsed_opening=1.05s, time_elapsed_processing=11.44s, time_elapsed_scanning_total=23.30s, time_elapsed_scanning_until_data=28.56ms, output_rows_skew=0%, scan_efficiency_ratio=34.82% (9.45 B/27.15 B)]
ProjectionExec: expr=[CAST(0.2 * CAST(avg(lineitem.l_quantity)@1 AS Float64) AS Decimal128(30, 15)) as Float64(0.2) * avg(lineitem.l_quantity), l_partkey@0 as l_partkey], metrics=[output_rows=20.00 M, elapsed_compute=120.90ms, output_bytes=38.6 GB, output_batches=2.45 K, expr_0_eval_time=120.33ms, expr_1_eval_time=212.94µs]
AggregateExec:
mode=FinalPartitioned, gby=[l_partkey@0 as l_partkey], aggr=[avg(lineitem.l_quantity)],
metrics=[output_rows=20.00 M, elapsed_compute=46.66s, output_bytes=114.8 GB, output_batches=2.45 K,
spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, agg_expr_0_arguments_time=33.56ms,
agg_expr_0_evaluate_time=168.91ms, agg_expr_0_merge_time=11.39s, agg_expr_0_state_time=14ns,
aggregate_arguments_time=56.75ms, aggregation_time=11.40s, emitting_time=169.74ms,
time_calculating_group_ids=34.98s,
output_rows_skew=0%]
RepartitionExec: partitioning=Hash([l_partkey@0], 14), input_partitions=14, metrics=[output_rows=600.0 M, elapsed_compute=48.99ms, output_bytes=17.9 GB, output_batches=73.25 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=11.56s, repartition_time=2.84s, send_time=9.62s, output_rows_skew=0%]
AggregateExec: mode=Partial, gby=[l_partkey@0 as l_partkey], aggr=[avg(lineitem.l_quantity)], metrics=[output_rows=600.0 M, elapsed_compute=318.23ms, output_bytes=18.7 GB, output_batches=75.83 K, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, early_emit_count=0, skipped_aggregation_rows=598.5 M, agg_expr_0_arguments_time=15.03ms, agg_expr_0_convert_to_state_time=185.98ms, agg_expr_0_state_time=12.00µs, agg_expr_0_update_time=7.63ms, aggregate_arguments_time=22.69ms, aggregation_time=7.65ms, emitting_time=40.41µs, time_calculating_group_ids=70.96ms, output_rows_skew=0%, reduction_factor=99.75% (1.49 M/1.49 M)]
DataSourceExec: file_groups={14 groups: [[Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:0..1938998887], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:1938998887..3877997774], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:3877997774..5816996661], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:5816996661..7755995548], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:7755995548..9694994435], ...]}, projection=[l_partkey, l_quantity], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 14 WHEN 0 THEN l_partkey@1 >= 4880 AND l_partkey@1 <= 19999654 AND hash_lookup WHEN 1 THEN l_partkey@1 >= 2409 AND l_partkey@1 <= 19978138 AND hash_lookup WHEN 2 THEN l_partkey@1 >= 12919 AND l_partkey@1 <= 19966033 AND hash_lookup WHEN 3 THEN l_partkey@1 >= 6079 AND l_partkey@1 <= 19988248 AND hash_lookup WHEN
4 THEN l_partkey@1 >= 11200 AND l_partkey@1 <= 19983248 AND hash_lookup WHEN 5 THEN l_partkey@1 >= 21574 AND l_partkey@1 <= 19986826 AND hash_lookup WHEN 6 THEN l_partkey@1 >= 12554 AND l_partkey@1 <= 19994996 AND hash_lookup WHEN 7 THEN l_partkey@1 >= 10687 AND l_partkey@1 <= 19968087 AND hash_lookup WHEN 8 THEN l_partkey@1 >= 11256 AND l_partkey@1 <= 19999539 AND hash_lookup WHEN 9 THEN l_partkey@1 >= 26396 AND l_partkey@1 <= 19999987 AND hash_lookup WHEN 10 THEN l_partkey@1 >= 26889 AND l_partkey@1 <= 19942847 AND hash_lookup WHEN 11 THEN l_partkey@1 >= 2425 AND l_partkey@1 <= 19981418 AND hash_lookup WHEN 12 THEN l_partkey@1 >= 7682 AND l_partkey@1 <= 19973698 AND hash_lookup WHEN 13 THEN l_partkey@1 >= 20870 AND l_partkey@1 <= 19992676 AND hash_lookup ELSE false END ], dynamic_rg_pruning=eligible, pruning_predicate=l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 4880 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999654 AND l_partkey_null_count@
1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1407/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2409 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19978138 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1456/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12919 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19966033 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1400/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 6079 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19988248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1465/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 11200 AND l_partkey_null_count@1 != row_count@2 AND
l_partkey_min@3 <= 19983248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1396/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 21574 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19986826 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1489/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12554 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19994996 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1422/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 10687 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19968087 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1397/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0
>= 11256 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999539 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1444/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26396 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999987 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1473/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26889 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19942847 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1395/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2425 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19981418 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1460/1048576 buckets) OR l_pa
rtkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 7682 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19973698 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1382/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 20870 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19992676 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1427/1048576 buckets) OR false, required_guarantees=[], metrics=[output_rows=600.0 M, elapsed_compute=5.76s, output_bytes=13.6 GB, output_batches=75.83 K, files_ranges_pruned_statistics=14 total → 14 matched, row_groups_pruned_statistics=5.23 K total → 5.23 K matched, page_index_pages_pruned=31.39 K total → 31.39 K matched, page_index_rows_pruned=600.0 M total → 600.0 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=25.3 GB, bytes_scanned=4.8 GB,
file_open_errors=0, file_scan_errors=0, files_opened=14, files_processed=14, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=77.57µs, metadata_load_time=8.50ms, page_index_eval_time=21.07ms, row_pushdown_eval_time=42ns, statistics_eval_time=6.58ms, time_elapsed_opening=1.01s, time_elapsed_processing=6.77s, time_elapsed_scanning_total=63.89s, time_elapsed_scanning_until_data=20.93ms, output_rows_skew=0%, scan_efficiency_ratio=19.13% (5.19 B/27.15 B)]
```
`AggregateExec` is taking up most of the query compute time. `time_calculating_group_ids=34.98s` So looks like without the pushdown we need to group every part key for 600m rows. Looking at the joins, they discard most of it -- so for this plan, the join ordering seems fine?
After enabling pushdown, the `AggregateExec` takes very little cpu time. All the CPU time goes in to the `DynamicFilter` within the scan:
```
ProjectionExec: expr=[CAST(sum(lineitem.l_extendedprice)@0 AS Float64) / 7 as avg_yearly], metrics=[output_rows=1, elapsed_compute=6.83µs, output_bytes=8.0 B, output_batches=1, expr_0_eval_time=3.00µs]
AggregateExec: mode=Final, gby=[], aggr=[sum(lineitem.l_extendedprice)], metrics=[output_rows=1, elapsed_compute=9.83µs, output_bytes=16.0 B, output_batches=1, agg_expr_0_arguments_time=1.96µs, agg_expr_0_evaluate_time=42ns, agg_expr_0_merge_time=298ns, output_rows_skew=0%]
CoalescePartitionsExec, metrics=[output_rows=14, elapsed_compute=16.54µs, output_bytes=224.0 B, output_batches=14]
AggregateExec: mode=Partial, gby=[], aggr=[sum(lineitem.l_extendedprice)], metrics=[output_rows=14, elapsed_compute=35.04µs, output_bytes=224.0 B, output_batches=14, agg_expr_0_arguments_time=3.21µs, agg_expr_0_state_time=5.04µs, agg_expr_0_update_time=13.42µs, output_rows_skew=0%]
HashJoinExec: mode=Partitioned, join_type=LeftSemi, on=[(p_partkey@2, l_partkey@1)], filter=CAST(l_quantity@0 AS Decimal128(30, 15)) < Float64(0.2) * avg(lineitem.l_quantity)@1, projection=[l_extendedprice@1], metrics=[output_rows=53.84 K, elapsed_compute=44.26ms, output_bytes=1792.0 KB, output_batches=14, build_mem_used=41.9 MB, array_map_created_count=0, build_input_batches=84, build_input_rows=601.0 K, input_batches=28, input_rows=73.87 K, build_time=20.92ms, join_time=23.33ms, avg_fanout=3000.26% (601.0 K/20.03 K), output_rows_skew=0.06%, probe_hit_rate=100% (20.03 K/20.03 K)]
HashJoinExec: mode=Partitioned, join_type=Inner, on=[(p_partkey@0, l_partkey@0)], projection=[l_quantity@2, l_extendedprice@3, p_partkey@0], metrics=[output_rows=601.0 K, elapsed_compute=7.46ms, output_bytes=26.2 MB, output_batches=84, build_mem_used=3.2 MB, array_map_created_count=0, build_input_batches=14, build_input_rows=20.03 K, input_batches=84, input_rows=601.0 K, build_time=530.09µs, join_time=6.93ms, avg_fanout=100% (601.0 K/601.0 K), output_rows_skew=0.05%, probe_hit_rate=100% (601.0 K/601.0 K)]
RepartitionExec: partitioning=Hash([p_partkey@0], 14), input_partitions=14, metrics=[output_rows=20.03 K, elapsed_compute=26.61µs, output_bytes=896.0 KB, output_batches=14, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=313.42ms, repartition_time=560.08µs, send_time=547.64µs, output_rows_skew=0.06%]
DataSourceExec: file_groups={14 groups: [[Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:0..49193775], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:49193775..98387550], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:98387550..147581325], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:147581325..196775100], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/part.parquet:196775100..245968875], ...]}, projection=[p_partkey], file_type=parquet, predicate=p_brand@3 = Brand#23 AND p_container@6 = MED BOX, pruning_predicate=p_brand_null_count@2 != row_count@3 AND p_brand_min@0 <= Brand#23 AND Brand#23 <= p_brand_max@1 AND p_container_null_count@6 != row_count@3 AND p_container_min@4 <= MED BOX AND MED BOX <= p_container_max@5, required_guarantees=[p_brand in (Brand#23), p_container in (MED BOX)], metrics=[output_rows=20.03 K, elapsed_compute=213.99ms,
output_bytes=11.9 MB, output_batches=191, files_ranges_pruned_statistics=14 total → 14 matched, row_groups_pruned_statistics=191 total → 191 matched, page_index_pages_pruned=1.15 K total → 1.15 K matched, page_index_rows_pruned=20.00 M total → 20.00 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=656.8 MB, bytes_scanned=141.5 MB, file_open_errors=0, file_scan_errors=0, files_opened=14, files_processed=14, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=20.03 K, pushdown_rows_pruned=19.98 M, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=0, predicate_cache_records=0, bloom_filter_eval_time=8.74µs, metadata_load_time=4.41ms, page_index_eval_time=1.62ms, row_pushdown_eval_time=32.04ms, statistics_eval_time=2.54ms, time_elapsed_opening=20.90ms, time_elapsed_processing=239.15ms, time_elapsed_scanning_total=293.78ms, time_elapsed_scanning_until_data=28.32ms, output_rows_skew=0.17%,
scan_efficiency_ratio=21.54% (148.4 M/688.7 M)]
RepartitionExec: partitioning=Hash([l_partkey@0], 14), input_partitions=14, metrics=[output_rows=601.0 K, elapsed_compute=59.53µs, output_bytes=26.2 MB, output_batches=84, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=40.90s, repartition_time=18.44ms, send_time=24.44ms, output_rows_skew=0.05%]
DataSourceExec: file_groups={14 groups: [[Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:0..1938998887], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:1938998887..3877997774], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:3877997774..5816996661], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:5816996661..7755995548], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:7755995548..9694994435], ...]}, projection=[l_partkey, l_quantity, l_extendedprice], file_type=parquet, predicate=DynamicFilter [ CASE hash_repartition % 14 WHEN 0 THEN l_partkey@1 >= 4880 AND l_partkey@1 <= 19999654 AND hash_lookup WHEN 1 THEN l_partkey@1 >= 2409 AND l_partkey@1 <= 19978138 AND hash_lookup WHEN 2 THEN l_partkey@1 >= 12919 AND l_partkey@1 <= 19966033 AND hash_lookup WHEN 3 THEN l_partkey@1 >= 6079 AND l_partkey@1 <= 19988248 AND hash
_lookup WHEN 4 THEN l_partkey@1 >= 11200 AND l_partkey@1 <= 19983248 AND hash_lookup WHEN 5 THEN l_partkey@1 >= 21574 AND l_partkey@1 <= 19986826 AND hash_lookup WHEN 6 THEN l_partkey@1 >= 12554 AND l_partkey@1 <= 19994996 AND hash_lookup WHEN 7 THEN l_partkey@1 >= 10687 AND l_partkey@1 <= 19968087 AND hash_lookup WHEN 8 THEN l_partkey@1 >= 11256 AND l_partkey@1 <= 19999539 AND hash_lookup WHEN 9 THEN l_partkey@1 >= 26396 AND l_partkey@1 <= 19999987 AND hash_lookup WHEN 10 THEN l_partkey@1 >= 26889 AND l_partkey@1 <= 19942847 AND hash_lookup WHEN 11 THEN l_partkey@1 >= 2425 AND l_partkey@1 <= 19981418 AND hash_lookup WHEN 12 THEN l_partkey@1 >= 7682 AND l_partkey@1 <= 19973698 AND hash_lookup WHEN 13 THEN l_partkey@1 >= 20870 AND l_partkey@1 <= 19992676 AND hash_lookup ELSE false END ], dynamic_rg_pruning=eligible, pruning_predicate=l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 4880 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999654 AND l_partke
y_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1407/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2409 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19978138 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1456/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12919 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19966033 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1400/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 6079 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19988248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1465/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 11200 AND l_partkey_null_count@1 != row
_count@2 AND l_partkey_min@3 <= 19983248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1396/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 21574 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19986826 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1489/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12554 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19994996 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1422/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 10687 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19968087 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1397/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_p
artkey_max@0 >= 11256 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999539 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1444/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26396 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999987 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1473/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26889 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19942847 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1395/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2425 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19981418 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1460/1048576 buc
kets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 7682 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19973698 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1382/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 20870 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19992676 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1427/1048576 buckets) OR false, required_guarantees=[], metrics=[output_rows=601.0 K, elapsed_compute=37.61s, output_bytes=22.9 MB, output_batches=5.23 K, files_ranges_pruned_statistics=14 total → 14 matched, row_groups_pruned_statistics=5.23 K total → 5.23 K matched, page_index_pages_pruned=31.39 K total → 31.39 K matched, page_index_rows_pruned=600.0 M total → 600.0 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=25.3 GB, bytes_sca
nned=8.8 GB, file_open_errors=0, file_scan_errors=0, files_opened=14, files_processed=14, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=601.0 K, pushdown_rows_pruned=599.4 M, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=600.0 M, predicate_cache_records=601.0 K, bloom_filter_eval_time=81.94µs, metadata_load_time=277.65µs, page_index_eval_time=18.19ms, row_pushdown_eval_time=28.98s, statistics_eval_time=3.54ms, time_elapsed_opening=993.42ms, time_elapsed_processing=38.56s, time_elapsed_scanning_total=40.04s, time_elapsed_scanning_until_data=122.47ms, output_rows_skew=0%, scan_efficiency_ratio=34.82% (9.45 B/27.15 B)]
ProjectionExec: expr=[CAST(0.2 * CAST(avg(lineitem.l_quantity)@1 AS Float64) AS Decimal128(30, 15)) as Float64(0.2) * avg(lineitem.l_quantity), l_partkey@0 as l_partkey], metrics=[output_rows=20.03 K, elapsed_compute=249.33µs, output_bytes=537.0 KB, output_batches=14, expr_0_eval_time=242.16µs, expr_1_eval_time=1.00µs]
AggregateExec: mode=FinalPartitioned, gby=[l_partkey@0 as l_partkey], aggr=[avg(lineitem.l_quantity)], metrics=[output_rows=20.03 K, elapsed_compute=2.24ms, output_bytes=537.0 KB, output_batches=14, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, agg_expr_0_arguments_time=24.75µs, agg_expr_0_evaluate_time=253.00µs, agg_expr_0_merge_time=451.00µs, agg_expr_0_state_time=14ns, aggregate_arguments_time=37.71µs, aggregation_time=457.34µs, emitting_time=263.42µs, time_calculating_group_ids=1.42ms, output_rows_skew=0.06%]
RepartitionExec: partitioning=Hash([l_partkey@0], 14), input_partitions=14, metrics=[output_rows=248.1 K, elapsed_compute=53.02µs, output_bytes=10.5 MB, output_batches=42, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, fetch_time=34.71s, repartition_time=4.28ms, send_time=788.38µs, output_rows_skew=0.06%]
AggregateExec: mode=Partial, gby=[l_partkey@0 as l_partkey], aggr=[avg(lineitem.l_quantity)], metrics=[output_rows=248.1 K, elapsed_compute=49.26ms, output_bytes=39.1 MB, output_batches=42, spill_count=0, spilled_bytes=0.0 B, spilled_rows=0, early_emit_count=0, skipped_aggregation_rows=0, agg_expr_0_arguments_time=1.27ms, agg_expr_0_convert_to_state_time=14ns, agg_expr_0_state_time=22.75µs, agg_expr_0_update_time=10.61ms, aggregate_arguments_time=1.96ms, aggregation_time=11.20ms, emitting_time=43.42µs, time_calculating_group_ids=33.32ms, output_rows_skew=0%, reduction_factor=41.27% (248.1 K/601.0 K)]
DataSourceExec: file_groups={14 groups: [[Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:0..1938998887], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:1938998887..3877997774], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:3877997774..5816996661], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:5816996661..7755995548], [Users/devan/Documents/OSS/polars-2.0-benchmark/data/tpch-100/lineitem.parquet:7755995548..9694994435], ...]}, projection=[l_partkey, l_quantity], file_type=parquet,
predicate=DynamicFilter [ CASE hash_repartition % 14 WHEN 0 THEN l_partkey@1 >= 4880 AND l_partkey@1 <= 19999654 AND hash_lookup WHEN 1 THEN l_partkey@1 >= 2409 AND l_partkey@1 <= 19978138 AND hash_lookup WHEN 2 THEN l_partkey@1 >= 12919 AND l_partkey@1 <= 19966033 AND hash_lookup WHEN 3 THEN l_partkey@1 >= 6079 AND l_partkey@1 <= 19988248 AND hash_lookup WHEN 4 THEN l_partkey@1 >= 11200 AND l_partkey@1 <= 19983248 AND hash_lookup WHEN 5 THEN l_partkey@1 >= 21574 AND l_partkey@1 <= 19986826 AND hash_lookup WHEN 6 THEN l_partkey@1 >= 12554 AND l_partkey@1 <= 19994996 AND hash_lookup WHEN 7 THEN l_partkey@1 >= 10687 AND l_partkey@1 <= 19968087 AND hash_lookup WHEN 8 THEN l_partkey@1 >= 11256 AND l_partkey@1 <= 19999539 AND hash_lookup WHEN 9 THEN l_partkey@1 >= 26396 AND l_partkey@1 <= 19999987 AND hash_lookup WHEN 10 THEN l_partkey@1 >= 26889 AND l_partkey@1 <= 19942847 AND hash_lookup WHEN 11 THEN l_partkey@1 >= 2425 AND l_partkey@1 <= 19981418 AND hash_lookup
WHEN 12 THEN l_partkey@1 >= 7682 AND l_partkey@1 <= 19973698 AND hash_lookup WHEN 13 THEN l_partkey@1 >= 20870 AND l_partkey@1 <= 19992676 AND hash_lookup ELSE false END ], dynamic_rg_pruning=eligible, pruning_predicate=l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 4880 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999654 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1407/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2409 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19978138 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1456/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12919 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19966033 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1400/1048576 bu
ckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 6079 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19988248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1465/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 11200 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19983248 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1396/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 21574 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19986826 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1489/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 12554 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19994996 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BI
TMAP(l_partkey_min@3, l_partkey_max@0, 1422/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 10687 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19968087 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1397/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 11256 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999539 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1444/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26396 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19999987 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1473/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 26889 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19942847 AND
l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1395/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 2425 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19981418 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1460/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 7682 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19973698 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1382/1048576 buckets) OR l_partkey_null_count@1 != row_count@2 AND l_partkey_max@0 >= 20870 AND l_partkey_null_count@1 != row_count@2 AND l_partkey_min@3 <= 19992676 AND l_partkey_null_count@1 != row_count@2 AND KEY_RANGE_BITMAP(l_partkey_min@3, l_partkey_max@0, 1427/1048576 buckets) OR false, required_guarantees=[], metrics=[output_rows=601.0 K, elapsed_compute=32.08s, output
_bytes=13.8 MB, output_batches=5.23 K, files_ranges_pruned_statistics=14 total → 14 matched, row_groups_pruned_statistics=5.23 K total → 5.23 K matched, page_index_pages_pruned=31.39 K total → 31.39 K matched, page_index_rows_pruned=600.0 M total → 600.0 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=25.3 GB, bytes_scanned=4.8 GB, file_open_errors=0, file_scan_errors=0, files_opened=14, files_processed=14, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=601.0 K, pushdown_rows_pruned=599.4 M, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=600.0 M, predicate_cache_records=601.0 K, bloom_filter_eval_time=77.24µs, metadata_load_time=360.15µs, page_index_eval_time=17.48ms, row_pushdown_eval_time=27.24s, statistics_eval_time=818.03µs, time_elapsed_opening=957.84ms, time_elapsed_processing=33.03s, time_elapsed_scanning_total=33.75s, time_elapsed_scanning_until_data=106.55ms, outp
ut_rows_skew=0%, scan_efficiency_ratio=19.13% (5.19 B/27.15 B)]
```
Enabling pushdown shaves off a bit of time.
- pushdown off: 6.71s
- pushdown on: 5.57s
- Polars: 1.42s
`PhysicalExpr::evaluate` taking up most of the flamegraph.
<img width="1703" height="190" alt="Image" src="https://github.com/user-attachments/assets/2ab8afae-3947-4a8e-b8a9-128b955c2c92" />
`filter_array` memory movement:
<img width="1728" height="202" alt="Image" src="https://github.com/user-attachments/assets/207fbd70-62e3-4869-abee-204b55e449d0" />
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "tschwarzinger (via GitHub)" <gi...@apache.org>.
tschwarzinger commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6188246071
For me, when running `DATAFUSION_EXECUTION_PARQUET_PUSHDOWN_FILTERS=true cargo run --profile release-nonlto --bin dfbench -- tpch --debug --query 17 --format parquet --iterations 1 --path ./benchmarks/data/tpch_sf10/`
The last `DataSourceExec` on line item has huge dynamic filters and the following metrics:
```
metrics=[output_rows=61.38 K, elapsed_compute=14.46s, output_bytes=1438.7 KB, output_batches=524, files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, page_index_pages_pruned=3.14 K total → 3.14 K matched, page_index_rows_pruned=59.99 M total → 59.99 M matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, bytes_processed=1907.0 MB, bytes_scanned=353.6 MB, file_open_errors=0, file_scan_errors=0, files_opened=24, files_processed=24, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=61.38 K, pushdown_rows_pruned=59.92 M, row_groups_pruned_dynamic_filter=0, predicate_cache_inner_records=59.99 M, predicate_cache_records=61.38 K, bloom_filter_eval_time=40.21µs, metadata_load_time=829.87µs, page_index_eval_time=7.45ms, row_pushdown_eval_time=11.37s, statistics_eval_time=283.26ms, time_elapsed_opening=1.51s, time_elapsed_processing=16.22s, time_elapsed_scanning_total=15.
67s, time_elapsed_scanning_until_data=1.45s, output_rows_skew=0.04%, scan_efficiency_ratio=18.54% (370.7 M/2.00 B)]
```
Interesting things. The full output is in the attached file (the dynamic filters are quite huge).:
- `output_rows=61.38 K, elapsed_compute=14.46s`, evaluating the filters works but takes a long time. This is what https://github.com/apache/datafusion/pull/26249 is trying to improve upon.
- Nothing can be pruned, as in the sketch that @alamb provided: files_ranges_pruned_statistics=24 total → 24 matched, row_groups_pruned_statistics=524 total → 524 matched, page_index_pages_pruned=3.14 K total → 3.14 K matched`. This could be because the relevant lineitems of the parts are actually distributed over all data pages or because the `CASE hash_repartition % 24 WHEN ...` type of expressions that the multi-partition hash join produces is not suited for pruning. duckdb also seems to read the entire table `in: 6.00 M out: 5,986,865 `.
- We don't have the `DELIM_SCAN`
I think the actual difference comes from i) high dynamic filter evaluation time and ii) possibly the approach that duckdb takes with the `DELIM_SCAN`. As far as I understand it, the delim scan reuses the *part ids* after filtering to join on the the line items. There it just has to build and probe a hash table with 2K keys instead of the 61.38 K hash table that we have in the top of the join tree which already joined the lineitems with the parts. In theory, the hash table look ups should be done in constant time but maybe the larger tables have significant effect on cache misses etc. such that the performance could be a different. One would have to measure though.
[output.log](https://github.com/user-attachments/files/33483082/output.log)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "comphead (via GitHub) via github" <gi...@datafusion.apache.org>.
comphead commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6695009907
Related https://github.com/apache/datafusion/issues/19815
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "comphead (via GitHub) via github" <gi...@datafusion.apache.org>.
comphead commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6695792391
### DataFusion TPC-H SF1000 on `main` (`a616831`)
**Setup**
- DataFusion `57.0.0-dev`, `main` at [`a616831`](https://github.com/apache/datafusion/commit/a616831122aa525ed843ee3c5e11f4803f557c98) (2026-11-06)
- Hardware: k8s pod, amd64, 32 cores
- Data: TPC-H SF1000 Parquet, zstd, 512 MB blocks, partitioned, read from S3
- `target_partitions = 32`
- `datafusion.runtime.memory_limit = 105G` (greedy pool)
- `datafusion.runtime.temp_directory = /data`, `max_temp_directory_size = 900G`
- `datafusion.runtime.metadata_cache_limit = 2G`, `list_files_cache_limit = 256M`, `file_statistics_cache_limit = 256M`
- 3 iterations per query. The times below are in seconds.
This commit already includes the revert of #25790 (#27145), so the Q18 failure below is not the #27108 regression.
**Results**
| Query | Mean | Min | Max | Status |
|---|---:|---:|---:|---|
| Q01 | 46.17 | 45.86 | 46.38 | ok |
| Q02 | 37.71 | 35.83 | 41.20 | ok |
| Q03 | 31.25 | 29.53 | 34.22 | ok |
| Q04 | 22.92 | 22.58 | 23.18 | ok |
| Q05 | 53.85 | 47.61 | 64.85 | ok |
| Q06 | 4.76 | 4.63 | 4.84 | ok |
| Q07 | 61.13 | 59.27 | 62.28 | ok |
| Q08 | 54.80 | 53.02 | 56.15 | ok |
| Q09 | | | | **failed (OOM)** |
| Q10 | 37.20 | 36.27 | 38.02 | ok |
| Q11 | 13.44 | 12.13 | 14.64 | ok |
| Q12 | 14.86 | 13.94 | 15.80 | ok |
| Q13 | 29.77 | 29.43 | 30.32 | ok |
| Q14 | 4.01 | 3.50 | 4.57 | ok |
| Q15 | 16.55 | 10.93 | 22.36 | ok |
| Q16 | 11.52 | 10.22 | 13.70 | ok |
| Q17 | 75.21 | 74.55 | 76.05 | ok |
| Q18 | | | | **failed (OOM)** |
| Q19 | 31.41 | 30.92 | 31.77 | ok |
| Q20 | 24.35 | 23.23 | 25.99 | ok |
| Q21 | | | | **failed (OOM)** |
| Q22 | 22.63 | 20.17 | 24.50 | ok |
19 of 22 queries passed. The sum of their means is 593.6 s and the geometric mean is 24.4 s.
**Failures**
Q09, Q18 and Q21 failed on all 3 iterations with `Resources exhausted` while building hash join input. `HashJoinInput` reports `can spill: false`, so the 105G pool fills up with build-side reservations of similar size, one per partition.
- **Q09**: `Failed to allocate additional 763.0 MB for HashJoinInput[24] with 763.0 MB already allocated for this reservation - 361.1 MB remain available for the total memory pool: greedy(used: 104.6 GB, pool_size: 105.0 GB)`. The top consumers are `HashJoinInput` partitions at 2.1 GB each.
- **Q18**: `Failed to allocate additional 1440.0 KB for HashJoinInput[4] with 2.5 GB already allocated for this reservation - 247.2 KB remain available for the total memory pool: greedy(used: 105.0 GB, pool_size: 105.0 GB)`. The top consumers are `HashJoinInput` partitions at 2.5 GB each.
- **Q21**: `Failed to allocate additional 4.7 GB for HashJoinInput[24] with 0.0 B already allocated for this reservation - 1591.6 MB remain available for the total memory pool: greedy(used: 103.4 GB, pool_size: 105.0 GB)`. The top consumers are `HashJoinInput` partitions at 6.5 GB each.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "comphead (via GitHub) via github" <gi...@datafusion.apache.org>.
comphead commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6744990263
we prob can start query by query and:
- check logical optimized plans, if Polars does something more smart in planning
- if plans are the same physical execution to identify parts that slower
They look like utilize better intra/inter parallelism, that causes some queries spontaneously deadlocking on 1TB benchmark, I'll start this week with TPCH Q01 to see if this approach makes sense
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6750864536
THe first thing I recommend doing is get the plans for both engines and look at the join orders. If the join orders aren't the same, that is the first thing to look into
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Improve Performance (Polars 2.0 Call to Action) [datafusion]
Posted by "devanbenz (via GitHub)" <gi...@apache.org>.
devanbenz commented on issue #26169:
URL: https://github.com/apache/datafusion/issues/26169#issuecomment-6115196804
> It would shock me if the problem wasn't with the join order that is selected or that our HashJoin algorithm could be much better
@alamb After running the main branch of datafusion against polars I do see slight performance improvements, specifically Q18 looks a lot better.
Biggest gaps:
- q17: 4.7x (6.71s vs 1.42s)
- q02: 4.3x (0.73s vs 0.17s)
- q11: 4.2x (0.70s vs 0.17s)
- q07: 3.5x (6.90s vs 1.95s)
- q21: 3.3x (11.99s vs 3.62s)
- q15: 3.2x (3.13s vs 0.98s)
- q18: 2.7x (6.98s vs 2.59s)
Closest:
- q22: 0.96x (DF faster, 0.42s vs 0.43s)
- q10: 0.98x (DF faster, 2.41s vs 2.47s)
- q13: 0.99x (2.40s vs 2.41s)
- q16: 1.1x (0.49s vs 0.44s)
- q12: 1.3x (0.87s vs 0.65s)
- q01: 1.3x (2.68s vs 2.00s)
Total is 2.15x (75.1s vs 34.9s)
In my previous comment I was running the exact benchmark the polars team published, which was 54.0.0.
The flamegraphs I have attached above are all based on main FYI.
Here's some explain analyzes for q17,q02,q11 since those were all ~4x slower than polars.
[datafusion_q02.txt](https://github.com/user-attachments/files/33315609/datafusion_q02.txt)
[datafusion_q11.txt](https://github.com/user-attachments/files/33315608/datafusion_q11.txt)
[datafusion_q17.txt](https://github.com/user-attachments/files/33315610/datafusion_q17.txt)
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org