Slowdown in ClickBench Q36-Q37 between DataFusion 43.0.0 and 44.0.0

[alamb]

Is your feature request related to a problem or challenge?

@pmcgleenon ran ClickBench on DataFusion 44 ❤

• Update ClickBench benchmarks with DataFusion 44.0.0 #13983 (comment)

Here are the results of ClickBench across several DataFusion versions:
clickbench-latest.html.zip

Q36 and Q37 look like they got slower

Describe the solution you'd like

Investigate (and hopefully restore) the performance in Q36 and Q37

Here are the queries (note the queries are numbered starting at 0 but the line numbers start at 1):

datafusion/benchmarks/queries/clickbench/queries.sql

Lines 37 to 38 in 0d9f845

	SELECT "URL", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate"::INT::DATE >= '2013-07-01' AND "EventDate"::INT::DATE <= '2013-07-31' AND "DontCountHits" = 0 AND "IsRefresh" = 0 AND "URL" <> '' GROUP BY "URL" ORDER BY PageViews DESC LIMIT 10;
	SELECT "Title", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate"::INT::DATE >= '2013-07-01' AND "EventDate"::INT::DATE <= '2013-07-31' AND "DontCountHits" = 0 AND "IsRefresh" = 0 AND "Title" <> '' GROUP BY "Title" ORDER BY PageViews DESC LIMIT 10;

Describe alternatives you've considered

No response

Additional context

No response

[alamb]

I got flamegraphs from them using https://github.com/flamegraph-rs/flamegraph

Q36

./datafusion-cli-44 -c "SELECT \"URL\", COUNT(*) AS PageViews FROM 'hits.parquet' WHERE \"CounterID\" = 62 AND \"EventDate\"::INT::DATE >= '2013-07-01' AND \"EventDate\"::INT::DATE <= '2013-07-31' AND \"DontCountHits\" = 0 AND \"IsRefresh\" = 0 AND \"URL\" <> '' GROUP BY \"URL\" ORDER BY PageViews DESC LIMIT 10; "

And made flamegraphs with

sudo flamegraph -- ./datafusion-cli-43 -c "SELECT \"URL\", COUNT(*) AS PageViews FROM 'hits.parquet' WHERE \"CounterID\" = 62 AND \"EventDate\"::INT::DATE >= '2013-07-01' AND \"EventDate\"::INT::DATE <= '2013-07-31' AND \"DontCountHits\" = 0 AND \"IsRefresh\" = 0 AND \"URL\" <> '' GROUP BY \"URL\" ORDER BY PageViews DESC LIMIT 10;

Here is DataFusion 43:

Here is DataFusion 44:

A largre amount of the time is spent decoding ParquetMetadata

[alamb]

Also when I tried in DataFusion 45 (pre-release) the speed seems to have gotten better again...

[alamb]

Given how much time is spent decoding ParquetMetadata, maybe it would be good to add some sort of small built in cache for parquet metadata 🤔 I think @Ted-Jiang made hooks to do this a long time ago but we don't have anything in by default