Improve Performance (Polars 2.0 Call to Action)

@ritchie46

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:

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 here:

Additional context

No response

Willingness to contribute

None

@Dandandan

Some related work

@Dandandan 's work on better JoinOrdering here: feat: cost-based join order enumeration #24456

Some other thoughts:

The first thing to do would be to get a full breakdown of the benchmark results and profile the queries where DataFusion is slower

Running the benchmark locally on an M3 macbook: https://github.com/pola-rs/polars-2.0-benchmark

Raw data:

datafusion_tpch_100.txt
polars_tpch_100.txt

The slowest DataFusion query is q18.

Slowest single run: 18.171 s (run 4/5)
Best of 5: 16.790 s (run 2/5)
Peak memory: 20.34 GiB (run 5/5)

DataFusion faster:

q16: 0.374 s vs 0.421 s

Biggest gaps (DataFusion ÷ Polars):

q18: 6.7x (16.79 s vs 2.52 s)
q17: 4.6x (6.44 s vs 1.41 s)
q02: 4.3x (0.72 s vs 0.17 s)
q11: 4.2x (0.70 s vs 0.16 s)
q07: 3.6x (7.01 s vs 1.93 s)

Closest (Polars still ahead):

q13: 1.02x (2.36 s vs 2.30 s)
q10: 1.02x (2.50 s vs 2.44 s)
q22: 1.07x (0.45 s vs 0.42 s)

Here are flamegraphs for q18, q17, q11, q07, and q02:

flamegraphs.zip

Would be interesting to see whether some non-default options (e.g., filter pushdown #3463 ) can help. Perhaps I'll investigate a bit tomorrow.

Should we start by adding their benchmarks to our suite, maybe even as part of the standard benchmark runs?

Never mind, it is our standard benchmarks. So I guess like Andrew said more focus on TPC-H / TPC-DS.

Do we have an explain analyze for q18?

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

Yeah I looked at it today and IMO the join order was the major issue. I don't have the explain plan available now but I can provide it tomorrow if no one beats me to it.

I wanted to check whether there are some "lower" hanging fruits for improvements.

Ok I've started my PC before saying something wrong.

Query:

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):

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):

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:

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_groups=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 THEN 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 <= 1499980 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, 52935685, 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, 47071686, 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) ([48085603, 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, 12845285, 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, 48395044, 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, 34937604, 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, 32148069, 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, 55226209, 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=[output_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 THEN 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 17 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=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=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:

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_groups=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 THEN 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 <= 1499980 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, 38093280, 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, 10805828, 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) ([28092902, 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, 14046658, 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, 53092067, 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, 34937604, 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, 26707522, 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, 25109123, 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=[output_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, 54796358, 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, 28378659, 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, 17929698, 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_opening=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 part of the join ordering problems. For example, the second lineitems scan only returns 4.37 K elements, thus avoiding large intermediate join results. Still, join order is still a problem. Furthermore, the processing time in the scans increases by quite some time, eliminating most of the benefits of the join improvements (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.

@alamb

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
datafusion_q11.txt
datafusion_q17.txt

Common Subplan Elinimation also seems to make a difference in some queries where the lineitem table is scanned multiple times: #8777

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, elapsed_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_rows_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_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_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_scanned=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_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=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_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_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_scanned=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 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_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, output_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

DynamicFilterPhysicalExpr::evaluate taking up most of the flamegraph.

filter_array memory movement:

Could be a problem with the shape of the predicate that gets built for DynamicFilterPhysicalExpr since it looks like the THEN cases are taking up most of the computation time:

datafusion/datafusion/physical-expr/src/expressions/case.rs

Lines 877 to 884 in 6663e43

    
           // Filter the remainder batch based on the 'when' value 
        
           // This results in a batch containing only the rows that need to be evaluated 
        
           // for the current branch 
        
           // Still no need to call `prep_null_mask_filter` since `create_filter` will already do 
        
           // this unconditionally. 
        
           let then_filter = create_filter(&when_value, true); 
        
           let then_batch = filter_record_batch(&remainder_batch, &then_filter)?; 
        
           let then_rows = filter_array(&remainder_rows, &then_filter)?;

@tschwarzinger

@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.

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

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 us on standard benchmarks even if they are beneficial to the right workloads.

IMO to match other systems we'll have to support late materialization. These hash join dynamic filter issues are one of the obstacles standing in the way of it: turning on filter pushdown makes queries slower because they are evaluated, as shown in #26169 (comment) and #26169 (comment)

Build	IN-list threshold	Parquet row filtering	Join filters enabled	Join filters disabled	Effect of disabling
Baseline	Default	Off	920.3 ms	852.5 ms	7.4% faster
PR #26249	Default	Off	895.3 ms	881.2 ms	1.6% faster
PR #26249	0	Off	869.8 ms	856.4 ms	1.5% faster
Baseline	Default	On	720.9 ms	854.2 ms	18.5% slower
PR #26249	Default	On	716.9 ms	857.3 ms	19.6% slower
PR #26249	0	On	326.6 ms	850.3 ms	2.60× slower

@Omega359

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.

EDIT: ofc if we cannot get it to work, we can switch the default. But it'd suggest investing a bit into this approach before switching the defaults.

as shown in #26169 (comment)

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 on the new branch? I think you need that for the other expression to trigger for smaller scale factors.

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.

Yes — tested with DATAFUSION_OPTIMIZER_HASH_JOIN_INLIST_PUSHDOWN_MAX_DISTINCT_VALUES=0, and confirmed that it activated the PR’s faster expression. However, the main on/off table in my previous comment used the default IN-list threshold.

For PR #26249, at SF10 with 24 partitions and Parquet row filtering enabled:

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.

perf: replace the routed hash join dynamic filter with one global filter

feat(perf): compute routing and lookup hashes once (apache#26249)

Btw we've merged #26249!

@devanbenz

@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?

29. @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.

@tschwarzinger

@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 #13188 and a few PRs associated with it such as #27092.

EDIT: I tested against the commit right before #26249: 198ab43
Q18 took approximately ~220s on my MBP. So this was regressed prior to my changes. I can create an issue.

Regression issue: #27108 -- Reverting #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 :)

Navigation Menu

Repository navigation

Improve Performance (Polars 2.0 Call to Action) #26169

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Willingness to contribute

5 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	// Filter the remainder batch based on the 'when' value
	// This results in a batch containing only the rows that need to be evaluated
	// for the current branch
	// Still no need to call `prep_null_mask_filter` since `create_filter` will already do
	// this unconditionally.
	let then_filter = create_filter(&when_value, true);
	let then_batch = filter_record_batch(&remainder_batch, &then_filter)?;
	let then_rows = filter_array(&remainder_rows, &then_filter)?;

Improve Performance (Polars 2.0 Call to Action) #26169

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Willingness to contribute

Activity

alamb commented on Oct 9, 2026

alamb commented on Oct 9, 2026

devanbenz commented on Oct 10, 2026

devanbenz commented on Oct 10, 2026

tschwarzinger commented on Oct 11, 2026

adriangb commented on Oct 11, 2026

adriangb commented on Oct 11, 2026

alamb commented on Oct 11, 2026

tschwarzinger commented on Oct 11, 2026

tschwarzinger commented on Oct 11, 2026

devanbenz commented on Oct 11, 2026

tschwarzinger commented on Oct 12, 2026

devanbenz commented on Oct 12, 2026

5 remaining items

devanbenz commented on Oct 15, 2026

Omega359 commented on Oct 15, 2026

adriangb commented on Oct 15, 2026

Omega359 commented on Oct 15, 2026

tschwarzinger commented on Oct 15, 2026

Omega359 commented on Oct 15, 2026

adriangb commented on Nov 4, 2026

tschwarzinger commented on Nov 4, 2026

devanbenz commented on Nov 4, 2026

devanbenz commented on Nov 4, 2026

devanbenz commented on Nov 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions