Analyze current ClickBench performance with DataFusion 52

[alamb]

Is your feature request related to a problem or challenge?

• Part of [EPIC] Make DataFusion the top of the ClickBench Parquet leaderboard #18489

As we work towards optimizing more for ClickBench we need to know what the current status is. We have our local bench.sh based benchmarking script but that doesn't perfectly mirror the actual results on https://benchmark.clickhouse.com

Describe the solution you'd like

I would like a clear analysis of where we are as well as perhaps some analysis of how well various optimziations are currently working

Describe alternatives you've considered

No response

Additional context

No response

[alamb]

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

[alamb]

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

[alamb]

Next I will plan to run the numbers for c6.2xlarge in 52.0.0 and 53.0.0 (main) and post the results

[alamb]

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

[alamb]

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

[alamb]

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

[Dandandan]

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;

[alamb]

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)