Update ClickBench benchmarks with DataFusion 50.0.0

[alamb]

Is your feature request related to a problem or challenge?

Follow on to

• Update ClickBench benchmarks with DataFusion 49.0.0 #16643
• Update ClickBench benchmarks with next DataFusion  #14587

Requires

• Release DataFusion 50.0.0 (Aug/Sep 2025) #16799

Describe the solution you'd like

Now that DataFusion 500.0 is released, It would be great to update ClickBench https://benchmark.clickhouse.com/ with the newest version and highlight and improvement

ClickBench is a benchmark heavy on filtering and aggregation that we have used as an optimization target for the last several releases.

Describe alternatives you've considered

No response

Additional context

No response

[pmcgleenon]

Now that there are multiple instance types for datafusion, I've created some automation to run the clickbench tests. If anyone would like to try it out, I would be interested in your feedback!

Originally the ClickBench datafusion tests only used 1 instance type: c6a.4xlarge
Recently the ClickHouse team added new instance types into the results for datafusion c6a.xlarge, c6a.2xlarge and c8g.4xlarge

The instance type c6a.xlarge only has 8GB RAM (the clickbench dataset is 15GB) and there are a number of issues with it, including

• datafusion compilation fails on this instance. We can workaround this by using brew install
• OOM errors happens during test execution, with results reported as null for several tests
• the machine becomes unresponsive, with ssh and shell not working during test execution

I see two options here:

1. report results for the c6a.xlarge and use the brew install workaround to get around the compilation issues. In this case some of the results will be null. I didn't see a way to specify a particular datafusion version with brew install, so it will always pick up the latest version (currently 50.3.0)
2. remove c6a.xlarge from the results until datafusion becomes functional on the 8GB RAM machine. We would have the option to compile datafusion with target-cpu=native to squeeze out some more performance

I think we should avoid reporting results for c6a.xlarge until the issues are resolved, particularly since there is such a negative impact on the server when running the tests

@alamb @Dandandan and everyone else - interested in your opinion on the way forward here

[alamb]

First of all, thank you @pmcgleenon for looking into this. I keep dreaming about the next generation of ClickBench numbers but haven't had a chance to comment recently

I think we should avoid reporting results for c6a.xlarge until the issues are resolved, particularly since there is such a negative impact on the server when running the tests

@alamb @Dandandan and everyone else - interested in your opinion on the way forward here

I think this makes sense to me. I will file a ticket about the issue too (and hopefully try and reproduce)

[alamb]

I will file a ticket about the issue too (and hopefully try and reproduce)

I filed some tickets:

• datafusion-cli requires more than 8GB to build #18471
• datafusion-cli fails to run ClickBench queries with 8GB of RAM #18473

[alamb]

@pmcgleenon do you think you will be able to run ClickBench on DataFusion 50 (for the instances where it works) any time soon?

[alamb]

(people are asking me about what DataFusion's performance looks like)

[pmcgleenon]

@alamb I'm hoping to run this over the next couple of days, I'll post any initial results here so you can take a look

[alamb]

@alamb I'm hoping to run this over the next couple of days, I'll post any initial results here so you can take a look

THANK YOU 🙏

[pmcgleenon]

@alamb here are the initial results. report.zip

I've added them to the Clickbench html page so they can be compared with the previous version "DataFusion-old". version 47 looks to be the previous version tested. I needed to Select Metric "Hot Run" to get the hot run results

• For partitioned, the results are similar to the previous version for c8g.4xlarge and c6a.x4large
• For single, v50 looks faster overall

Q23 looks a lot slower on c6a.2xlarge compared to the other instances for both single and partitioned files. I didn't analyse it but it seems like it could be hitting the memory limit on the 16GB RAM machine and works better on the higher spec machines

Let me know how these look, if anything looks weird shout out and I can rerun it. If it seems ok I can create a PR for the clickbench repository

[adriangb]

Am I reading this correctly?

This would suggest mostly regressions, including in Q23 which is very surprising to me

[alamb]

This would suggest mostly regressions, including in Q23 which is very surprising to me

Yes indeed this is also surprising to me

Q23 is

SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY "EventTime" LIMIT 10;

Namely the query that benefits the most from the new dynamic filter pushdown

In my unscientific experiments locally (Mac M3) I see q23 going significantly faster on 50.3.0 than 47.0.0:

time ~/Software/datafusion-cli/datafusion-cli-47.0.0 -c "SELECT * FROM hits WHERE \"URL\" LIKE '%google%' ORDER BY \"EventTime\" LIMIT 10;"

0m5.377s
0m4.507s
0m5.144s

time ~/Software/datafusion-cli/datafusion-cli-50.3.0 -c "SELECT * FROM hits WHERE \"URL\" LIKE '%google%' ORDER BY \"EventTime\" LIMIT 10;"

0m5.016s
0m3.652s
0m3.927s

[adriangb]

Yeah I would expect it to be 10-15% faster on a multi-core machine without filter pushdown, congruent with the blog post:

[pmcgleenon]

did you select Metric = Hot Run? The default for metric is Combined, which I think is a mixture of Hot run + cold run.

For partitioned parquet files on c6a.4xlarge the overall result looks the same as the previous version

Q23 is faster in v50, along with Q24 and Q26

I ran Q23 a few times on a c6a.4xlarge instance and got these results

# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.190
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.106
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.310
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.076

The grep Elapsed is taking the two Elapsed times from the output and sed -n 2p takes the second from the CLI output

datafusion-cli -f create.sql q23.sql |grep Elapsed
Elapsed 0.042 seconds.
Elapsed 9.186 seconds.

If you are seeing different results on the ClickBench AWS instance types I'm happy to re-run any of these

[pmcgleenon]

quick update on this, after enabling the configuration mentioned in the blog there is an improvement for Q23 from 9s to 0.8

export DATAFUSION_EXECUTION_PARQUET_REORDER_FILTERS='true';
export DATAFUSION_OPTIMIZER_ENABLE_DYNAMIC_FILTER_PUSHDOWN='true';
export DATAFUSION_EXECUTION_PARQUET_PUSHDOWN_FILTERS='true';

# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.801
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.798
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.814
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.819

Is this configuration we'd want to enable by default?