Analyze current ClickBench performance with DataFusion 52 #20601

Activity

1. alamb

   added the

   Feature

   issue type on Feb 27, 2026
2. alamb

   added

   enhancementNew feature or requestNew feature or request

   on Feb 27, 2026
3. alamb

   self-assigned this

   on Feb 27, 2026
4. alamb commented on Feb 27, 2026

   

   alamb

   on Feb 27, 2026

   ContributorAuthor

   More actions

   I compared using duckdb / ClickHouse and DataFusion

   First question -- do the results substantially vary across machine type?

   Conclusion: no, the relative results are basically the same (DF is about 1.5x slower per the benchmarks)

   I will therefore use a c6a.2xlarge to continue experimenting

   c8g.4xlarge (16 vCPU, 32 GB RAM, ARM/Graviton)

   Leaderboard Link

   

   c6a.4xlarge (16 vCPU, 32 GB RAM, Intel)

   Leaderboard Link

   

   c6a.2xlarge (8 vCPU, 16 GB RAM)

   Leaderboard Link

   

   c6a.xlarge (4 vCPU, 8 GB RAM)

   DataFusion Did not finish several queries

   
5. alamb commented on Feb 27, 2026

   

   alamb

   on Feb 27, 2026

   ContributorAuthor

   More actions

   It tuns out those results are for DataFusion 47.0.0

   I got sucked into a several hour oddsey fixing the benchmarks scripts in

   • Update DataFusion instructions / Enable swap on small machines ClickHouse/ClickBench#804

   But I think it is mostly ready now
6. alamb commented on Feb 28, 2026

   

   alamb

   on Feb 28, 2026

   Last edited by alamb

   ContributorAuthor

   More actions

   Next I will plan to run the numbers for c6.2xlarge in 52.0.0 and 53.0.0 (main) and post the results
7. alamb commented on Mar 1, 2026

   

   alamb

   on Mar 1, 2026

   ContributorAuthor

   More actions

   I ran the numbers on a c6a.2xlarge; TLDR is they are looking good compared to DataFusion 47. Full results here: result.zip

   Roughly 30% faster than with 47.

   

   I will now analyze compared to latest DuckDB numbers

   ❤️React with ❤️2Reacted by feniljain and Samyak Sarnayak
8. alamb commented on Mar 2, 2026

   

   alamb

   on Mar 2, 2026

   ContributorAuthor

   More actions

   I am now confident I have a reasonable ClickBench testing setup and can reproduce the

   If you want to check it out, here is the result: result-53.zip (unzip and open index.html)

   Some findings:

   DataFusion 47 results are reproducible:

   

   DataFusion vs DuckDB

   Here is what the results for DataFusion 53 will look like compared to DuckDB:

   

   The most obvious problem is Q23, our old friend

   datafusion/benchmarks/queries/clickbench/queries/q23.sql

   Line 4 in a5f490e

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

   

   This will be resolved by turning on filter pushdown (w/ dynamic filters

   • Enable parquet filter pushdown (filter_pushdown) by default #3463

   The next slowest is Q29

   datafusion/benchmarks/queries/clickbench/queries/q29.sql

   Line 4 in a5f490e

   SELECT SUM("ResolutionWidth"), SUM("ResolutionWidth" + 1), SUM("ResolutionWidth" + 2), SUM("ResolutionWidth" + 3), SUM("ResolutionWidth" + 4), SUM("ResolutionWidth" + 5), SUM("ResolutionWidth" + 6), SUM("ResolutionWidth" + 7), SUM("ResolutionWidth" + 8), SUM("ResolutionWidth" + 9), SUM("ResolutionWidth" + 10), SUM("ResolutionWidth" + 11), SUM("ResolutionWidth" + 12), SUM("ResolutionWidth" + 13), SUM("ResolutionWidth" + 14), SUM("ResolutionWidth" + 15), SUM("ResolutionWidth" + 16), SUM("ResolutionWidth" + 17), SUM("ResolutionWidth" + 18), SUM("ResolutionWidth" + 19), SUM("ResolutionWidth" + 20), SUM("ResolutionWidth" + 21), SUM("ResolutionWidth" + 22), SUM("ResolutionWidth" + 23), SUM("ResolutionWidth" + 24), SUM("ResolutionWidth" + 25), SUM("ResolutionWidth" + 26), SUM("ResolutionWidth" + 27), SUM("ResolutionWidth" + 28), SUM("ResolutionWidth" + 29), SUM("ResolutionWidth" + 30), SUM("ResolutionWidth" + 31), SUM("ResolutionWidth" + 32), SUM("ResolutionWidth" + 33), SUM("ResolutionWidth" + 34), SUM("ResolutionWidth" + 35), SUM("ResolutionWidth" + 36), SUM("ResolutionWidth" + 37), SUM("ResolutionWidth" + 38), SUM("ResolutionWidth" + 39), SUM("ResolutionWidth" + 40), SUM("ResolutionWidth" + 41), SUM("ResolutionWidth" + 42), SUM("ResolutionWidth" + 43), SUM("ResolutionWidth" + 44), SUM("ResolutionWidth" + 45), SUM("ResolutionWidth" + 46), SUM("ResolutionWidth" + 47), SUM("ResolutionWidth" + 48), SUM("ResolutionWidth" + 49), SUM("ResolutionWidth" + 50), SUM("ResolutionWidth" + 51), SUM("ResolutionWidth" + 52), SUM("ResolutionWidth" + 53), SUM("ResolutionWidth" + 54), SUM("ResolutionWidth" + 55), SUM("ResolutionWidth" + 56), SUM("ResolutionWidth" + 57), SUM("ResolutionWidth" + 58), SUM("ResolutionWidth" + 59), SUM("ResolutionWidth" + 60), SUM("ResolutionWidth" + 61), SUM("ResolutionWidth" + 62), SUM("ResolutionWidth" + 63), SUM("ResolutionWidth" + 64), SUM("ResolutionWidth" + 65), SUM("ResolutionWidth" + 66), SUM("ResolutionWidth" + 67), SUM("ResolutionWidth" + 68), SUM("ResolutionWidth" + 69), SUM("ResolutionWidth" + 70), SUM("ResolutionWidth" + 71), SUM("ResolutionWidth" + 72), SUM("ResolutionWidth" + 73), SUM("ResolutionWidth" + 74), SUM("ResolutionWidth" + 75), SUM("ResolutionWidth" + 76), SUM("ResolutionWidth" + 77), SUM("ResolutionWidth" + 78), SUM("ResolutionWidth" + 79), SUM("ResolutionWidth" + 80), SUM("ResolutionWidth" + 81), SUM("ResolutionWidth" + 82), SUM("ResolutionWidth" + 83), SUM("ResolutionWidth" + 84), SUM("ResolutionWidth" + 85), SUM("ResolutionWidth" + 86), SUM("ResolutionWidth" + 87), SUM("ResolutionWidth" + 88), SUM("ResolutionWidth" + 89) FROM hits;

   

   Where I think we will just have to hold our nose and implement the ClickBench special optimization 💩

   • Make Clickbench Q29 5x faster for datafusion by extracting SUM(..) clauses #15524

   I am not going to spend a lot more time reviewing results until we get morsel driven scan from @Dandandan over the line

   ❤️React with ❤️2Reacted by Samyak Sarnayak and feniljain

   🚀React with 🚀2Reacted by Samyak Sarnayak and feniljain
9. alamb commented on Mar 2, 2026

   

   alamb

   on Mar 2, 2026

   ContributorAuthor

   More actions

   I am claiming analysis is complete and will mvoe on to improvements
10. alamb

    closed this as completedon Mar 2, 2026
11. Dandandan commented on Mar 3, 2026

    

    Dandandan

    on Mar 3, 2026

    Last edited by Dandandan

    Contributor

    More actions

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

    For this one we could do it probably faster by

    SELECT * FROM hits WHERE (EventTime in (SELECT EventTime FROM hits WHERE "URL" LIKE '%google%' ORDER BY "EventTime" LIMIT 10) AND "URL" LIKE '%google%' ORDER BY "EventTime" LIMIT 10;
    
12. alamb commented on Mar 10, 2026

    

    alamb

    on Mar 10, 2026

    ContributorAuthor

    More actions

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

    For this one we could do it probably faster by

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

    That is effectively the rewrite that DuckDB does for this query (it rewrites it into a join)

    👍React with 👍1Reacted by Daniël Heres