Coalesce batches inside hash join, reuse indices buffer #18972

Dandandan · 2025-11-27T21:37:24Z

Which issue does this PR close?

Closes Integrate BatchCoalescer into HashJoinExec and remove from CoalesceBatches optimization rule #18781

Rationale for this change

Improves the plan / plan readability for queries with joins.

Seems to improve perf as well a bit for more challenging joins: (TPC-H SF=10, in-memory)

│ QQuery 21    │              7948.54 ms │    6340.74 ms │ +1.25x faster │
│ QQuery 22    │                58.30 ms │      49.86 ms │ +1.17x faster │

What changes are included in this PR?

Are these changes tested?

Are there any user-facing changes?

Dandandan · 2025-11-28T07:33:23Z

@alamb let's fire some benchmarks?
I mostly see improvement on SF=10 in memory on the standard set of benchmarks

datafusion/core/tests/fuzz_cases/join_fuzz.rs

datafusion/physical-plan/src/joins/hash_join/stream.rs

datafusion/physical-optimizer/src/coalesce_batches.rs

alamb · 2025-11-29T11:55:42Z

🤖 ./gh_compare_branch.sh Benchmark Script Running
Linux aal-dev 6.14.0-1018-gcp #19~24.04.1-Ubuntu SMP Wed Sep 24 23:23:09 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Comparing refactor_join2 (60bdf21) to 02a7181 diff using: tpch10
Results will be posted here when complete

alamb · 2025-11-29T12:01:25Z

@alamb let's fire some benchmarks? I mostly see improvement on SF=10 in memory on the standard set of benchmarks

I started some. It would be really nice to find some way for others to run their own -- I don't think it is great I am often the bottleneck.

Maybe I'll see if I can get an LLM to write me a script to do that...

alamb · 2025-11-30T14:23:52Z

run benchmarks

alamb · 2025-11-30T14:23:56Z

🤖 ./gh_compare_branch.sh Benchmark Script Running
Linux aal-dev 6.14.0-1018-gcp #19~24.04.1-Ubuntu SMP Wed Sep 24 23:23:09 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Comparing refactor_join2 (60bdf21) to 02a7181 diff using: tpch_mem clickbench_partitioned clickbench_extended
Results will be posted here when complete

alamb · 2025-12-01T19:01:44Z

🤖 ./gh_compare_branch.sh Benchmark Script Running
Linux aal-dev 6.14.0-1018-gcp #19~24.04.1-Ubuntu SMP Wed Sep 24 23:23:09 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Comparing refactor_join2 (60bdf21) to 02a7181 diff using: tpch10
Results will be posted here when complete

alamb · 2025-12-01T19:35:06Z

🤖: Benchmark completed

Details

Comparing HEAD and refactor_join2
--------------------
Benchmark tpch_sf10.json
--------------------
┏━━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ Query        ┃ HEAD ┃ refactor_join2 ┃       Change ┃
┡━━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━┩
│ QQuery 1     │ FAIL │           FAIL │ incomparable │
│ QQuery 2     │ FAIL │           FAIL │ incomparable │
│ QQuery 3     │ FAIL │           FAIL │ incomparable │
│ QQuery 4     │ FAIL │           FAIL │ incomparable │
│ QQuery 5     │ FAIL │           FAIL │ incomparable │
│ QQuery 6     │ FAIL │           FAIL │ incomparable │
│ QQuery 7     │ FAIL │           FAIL │ incomparable │
│ QQuery 8     │ FAIL │           FAIL │ incomparable │
│ QQuery 9     │ FAIL │           FAIL │ incomparable │
│ QQuery 10    │ FAIL │           FAIL │ incomparable │
│ QQuery 11    │ FAIL │           FAIL │ incomparable │
│ QQuery 12    │ FAIL │           FAIL │ incomparable │
│ QQuery 13    │ FAIL │           FAIL │ incomparable │
│ QQuery 14    │ FAIL │           FAIL │ incomparable │
│ QQuery 15    │ FAIL │           FAIL │ incomparable │
│ QQuery 16    │ FAIL │           FAIL │ incomparable │
│ QQuery 17    │ FAIL │           FAIL │ incomparable │
│ QQuery 18    │ FAIL │           FAIL │ incomparable │
│ QQuery 19    │ FAIL │           FAIL │ incomparable │
│ QQuery 20    │ FAIL │           FAIL │ incomparable │
│ QQuery 21    │ FAIL │           FAIL │ incomparable │
│ QQuery 22    │ FAIL │           FAIL │ incomparable │
└──────────────┴──────┴────────────────┴──────────────┘
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┓
┃ Benchmark Summary             ┃        ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━┩
│ Total Time (HEAD)             │ 0.00ms │
│ Total Time (refactor_join2)   │ 0.00ms │
│ Average Time (HEAD)           │ 0.00ms │
│ Average Time (refactor_join2) │ 0.00ms │
│ Queries Faster                │      0 │
│ Queries Slower                │      0 │
│ Queries with No Change        │      0 │
│ Queries with Failure          │     22 │
└───────────────────────────────┴────────┘

Dandandan · 2025-12-02T06:15:50Z

run benchmark tpch_mem

alamb · 2025-12-02T13:17:28Z

🤖 ./gh_compare_branch.sh Benchmark Script Running
Linux aal-dev 6.14.0-1018-gcp #19~24.04.1-Ubuntu SMP Wed Sep 24 23:23:09 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Comparing refactor_join2 (6bd9059) to cab28a6 diff using: tpch_mem
Results will be posted here when complete

alamb · 2025-12-02T13:51:00Z

🤖: Benchmark completed

Details

Comparing HEAD and refactor_join2
--------------------
Benchmark tpch_mem_sf1.json
--------------------
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ Query        ┃      HEAD ┃ refactor_join2 ┃        Change ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ QQuery 1     │ 140.77 ms │      133.05 ms │ +1.06x faster │
│ QQuery 2     │  29.08 ms │       27.05 ms │ +1.08x faster │
│ QQuery 3     │  37.91 ms │       38.56 ms │     no change │
│ QQuery 4     │  28.71 ms │       29.86 ms │     no change │
│ QQuery 5     │  89.24 ms │       91.30 ms │     no change │
│ QQuery 6     │  25.65 ms │       20.08 ms │ +1.28x faster │
│ QQuery 7     │ 231.33 ms │      235.99 ms │     no change │
│ QQuery 8     │  32.75 ms │       34.41 ms │  1.05x slower │
│ QQuery 9     │ 108.83 ms │      104.69 ms │     no change │
│ QQuery 10    │  63.81 ms │       64.99 ms │     no change │
│ QQuery 11    │  18.04 ms │       17.02 ms │ +1.06x faster │
│ QQuery 12    │  50.56 ms │       51.74 ms │     no change │
│ QQuery 13    │  48.63 ms │       51.10 ms │  1.05x slower │
│ QQuery 14    │  14.19 ms │       14.55 ms │     no change │
│ QQuery 15    │  25.55 ms │       25.85 ms │     no change │
│ QQuery 16    │  25.21 ms │       25.73 ms │     no change │
│ QQuery 17    │ 157.36 ms │      158.38 ms │     no change │
│ QQuery 18    │ 284.22 ms │      288.72 ms │     no change │
│ QQuery 19    │  37.24 ms │       37.34 ms │     no change │
│ QQuery 20    │  51.23 ms │       51.34 ms │     no change │
│ QQuery 21    │ 336.79 ms │      317.17 ms │ +1.06x faster │
│ QQuery 22    │  17.38 ms │       18.36 ms │  1.06x slower │
└──────────────┴───────────┴────────────────┴───────────────┘
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ Benchmark Summary             ┃           ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━┩
│ Total Time (HEAD)             │ 1854.48ms │
│ Total Time (refactor_join2)   │ 1837.30ms │
│ Average Time (HEAD)           │   84.29ms │
│ Average Time (refactor_join2) │   83.51ms │
│ Queries Faster                │         5 │
│ Queries Slower                │         3 │
│ Queries with No Change        │        14 │
│ Queries with Failure          │         0 │
└───────────────────────────────┴───────────┘

Dandandan · 2025-12-03T13:38:09Z

@alamb @martin-g
this is ready

Looks like tpc-h mem SF=1 also shows a small improvement for query 21, but smaller than SF=10

Omega359 · 2025-12-03T19:57:53Z

@alamb let's fire some benchmarks? I mostly see improvement on SF=10 in memory on the standard set of benchmarks

I started some. It would be really nice to find some way for others to run their own -- I don't think it is great I am often the bottleneck.

Maybe I'll see if I can get an LLM to write me a script to do that...

You GH repo is public. I copied your scripts locally and slightly modified them for my env but I'm sure anyone should be able to do it.

alamb · 2025-12-03T20:05:13Z

@alamb let's fire some benchmarks? I mostly see improvement on SF=10 in memory on the standard set of benchmarks

I started some. It would be really nice to find some way for others to run their own -- I don't think it is great I am often the bottleneck.
Maybe I'll see if I can get an LLM to write me a script to do that...

You GH repo is public. I copied your scripts locally and slightly modified them for my env but I'm sure anyone should be able to do it.

Indeed!

I also indulged in some additional scripting to launch things from PRs -- #18115 (comment)

I'll maybe add some additional well known contributors too

alamb · 2025-12-03T20:10:50Z

show benchmark queue

alamb · 2025-12-03T20:10:57Z

🤖 Hi @alamb, you asked to view the benchmark queue (#18972 (comment)).

Job	User	Benchmarks	Comment
`18875.sh`	unknown	tpch	`unknown`
`18766_3608566553.sh`	alamb	default	`https://github.com/apache/datafusion/pull/18766#issuecomment-3608566553`

Dandandan · 2025-12-04T08:04:51Z

@alamb let's fire some benchmarks? I mostly see improvement on SF=10 in memory on the standard set of benchmarks

I started some. It would be really nice to find some way for others to run their own -- I don't think it is great I am often the bottleneck.
Maybe I'll see if I can get an LLM to write me a script to do that...

You GH repo is public. I copied your scripts locally and slightly modified them for my env but I'm sure anyone should be able to do it.

Yes the benchmarks can be run (and I usually do as part of PRs), but it is always nice to run on different environments to verify / compare. Nice to automate this stuff 🚀

Dandandan · 2025-12-05T09:01:27Z

@milenkovicm perhaps you have time for review?

milenkovicm · 2025-12-05T09:32:28Z

will try over weekend if not too late

milenkovicm

@Dandandan this change makes sense to me, having said that, I'm not an expert in HJ :) I have put few minor comments, hopefully they make sense

datafusion/physical-optimizer/src/coalesce_batches.rs

datafusion/physical-plan/src/joins/join_hash_map.rs

datafusion/physical-plan/src/joins/hash_join/stream.rs

Co-authored-by: Marko Milenković <milenkovicm@users.noreply.github.com>

Dandandan · 2025-12-08T07:41:38Z

Thanks for the review @milenkovicm

alamb · 2025-12-08T11:55:04Z

Love it!

alamb · 2025-12-08T11:55:57Z

datafusion/core/tests/sql/explain_analyze.rs

-        CoalesceBatchesExec: target_batch_size=4096
-          RepartitionExec: partitioning=Hash([c2@0], 9000), input_partitions=1
-            DataSourceExec: file_groups={1 group: [[ARROW_TEST_DATA/csv/aggregate_test_100.csv]]}, projection=[c1@0 as c2], file_type=csv, has_header=true
+    HashJoinExec: mode=Partitioned, join_type=Inner, on=[(c1@0, c2@0)], projection=[c1@0]


Dandandan added 3 commits November 26, 2025 20:10

Refactor join macro to function

0595d63

Minor opt

246f7dc

Move coalesce batches to hash join

150977d

github-actions bot added optimizer Optimizer rules sqllogictest SQL Logic Tests (.slt) physical-plan Changes to the physical-plan crate labels Nov 27, 2025

Merge branch 'main' into refactor_join2

61c71ed

Dandandan changed the title ~~Refactor join2~~ Coalesce batches inside hash join Nov 27, 2025

Move coalesce batches to hash join

df11504

github-actions bot added the core Core DataFusion crate label Nov 27, 2025

Dandandan added 6 commits November 27, 2025 22:51

Fix tests

ac5f408

Clippy

f31c00e

Clippy

a35584e

tests

5c0f87d

tests

1c57500

Reusing buffers

1f1940e

Dandandan changed the title ~~Coalesce batches inside hash join~~ Coalesce batches inside hash join, reuse indices buffer Nov 28, 2025

test

447a913

Dandandan mentioned this pull request Nov 28, 2025

TPC-DS query 72 slow on modest (10) scale factors #17494

Open

Dandandan mentioned this pull request Nov 28, 2025

Integrate BatchCoalescer into HashJoinExec and remove from CoalesceBatches optimization rule #18781

Closed

martin-g reviewed Nov 28, 2025

View reviewed changes

datafusion/core/tests/fuzz_cases/join_fuzz.rs Outdated Show resolved Hide resolved

datafusion/physical-plan/src/joins/hash_join/stream.rs Outdated Show resolved Hide resolved

Dandandan added 3 commits November 28, 2025 12:30

Feedback

a4f8ad7

Add back comment

5d69519

Fix tests

60bdf21

Dandandan commented Nov 28, 2025

View reviewed changes

datafusion/physical-optimizer/src/coalesce_batches.rs Show resolved Hide resolved

Merge branch 'main' into refactor_join2

39e5e8f

Merge branch 'main' into refactor_join2

6bd9059

Dandandan added 4 commits December 3, 2025 10:04

Merge branch 'main' into refactor_join2

5b5d106

wip

e7cdba2

undo

0fe08ee

clippy

e933747

milenkovicm mentioned this pull request Dec 4, 2025

Tracking: Explicitly add CoalesceBatches physical rule apache/datafusion-ballista#1346

Closed

milenkovicm approved these changes Dec 6, 2025

View reviewed changes

Dandandan and others added 3 commits December 8, 2025 07:31

Update datafusion/physical-plan/src/joins/hash_join/stream.rs

b8e533e

Co-authored-by: Marko Milenković <milenkovicm@users.noreply.github.com>

Comment

5839d85

Comment

1f53c0c

Dandandan added this pull request to the merge queue Dec 8, 2025

Merged via the queue into apache:main with commit 215cdff Dec 8, 2025
31 checks passed

alamb reviewed Dec 8, 2025

View reviewed changes

Coalesce batches inside hash join, reuse indices buffer #18972

Coalesce batches inside hash join, reuse indices buffer #18972

Uh oh!

Conversation

Dandandan commented Nov 27, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Which issue does this PR close?

Rationale for this change

What changes are included in this PR?

Are these changes tested?

Are there any user-facing changes?

Uh oh!

Dandandan commented Nov 28, 2025

Uh oh!

Uh oh!

Uh oh!

Uh oh!

alamb commented Nov 29, 2025

Uh oh!

alamb commented Nov 29, 2025

Uh oh!

alamb commented Nov 30, 2025

Uh oh!

alamb commented Nov 30, 2025

Uh oh!

alamb commented Dec 1, 2025

Uh oh!

alamb commented Dec 1, 2025

Uh oh!

Dandandan commented Dec 2, 2025

Uh oh!

alamb commented Dec 2, 2025

Uh oh!

alamb commented Dec 2, 2025

Uh oh!

Dandandan commented Dec 3, 2025

Uh oh!

Omega359 commented Dec 3, 2025

Uh oh!

alamb commented Dec 3, 2025

Uh oh!

alamb commented Dec 3, 2025

Uh oh!

alamb commented Dec 3, 2025

Uh oh!

Dandandan commented Dec 4, 2025

Uh oh!

Dandandan commented Dec 5, 2025

Uh oh!

milenkovicm commented Dec 5, 2025

Uh oh!

milenkovicm left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Dandandan commented Dec 8, 2025

Uh oh!

alamb commented Dec 8, 2025

Uh oh!

alamb Dec 8, 2025

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Dandandan commented Nov 27, 2025 •

edited

Loading