TPCH q1 with no predicates is 2x slower than duckdb

[alamb]

Describe the bug

Breaking out from #18341 (comment) from @2010YOUY01

There is a discord discussion for slow tpch q1 https://discord.com/channels/885562378132000778/1290751484807352412/1432863136612089959 from @camuel

select
    l_returnflag,
    l_linestatus,
    count(*) as count_order
from
    lineitem
group by
    l_returnflag,
    l_linestatus;

DuckDB is run by creating views over parquet with this command and also threads are set to 12 (which matches actual cores on my AMD64 machine):

CREATE VIEW lineitem AS SELECT * FROM read_parquet('...repos/datafusion/sf1000/lineitem/*.parquet');

DataFusion is run by running this command:

cargo run --profile release --bin tpch -- benchmark datafusion --path ./sf1000 --partitions 12 --format parquet --query 1 --iterations=1 --memory-limit 1G --debug --batch-size 8192 --prefer_hash_join true --mem-pool-type fair

gist with logs: https://gist.github.com/camuel/67b4424205b81f06d657ea093ddbfe3c

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[alamb]

I can reproduce some speed difference locally with the tpchgen dataset:

Make dataset with https://github.com/clflushopt/tpchgen-rs

tpchgen-cli --tables=lineitem --format=parquet --scale-factor 1000

Dataset is 246GB:

andrewlamb@Andrews-MacBook-Pro-3:~/Downloads$ du -s -h lineitem.parquet
246G	lineitem.parquet

datafusion-cli

time datafusion-cli -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
DataFusion CLI v50.3.0
+--------------+--------------+-------------+
| l_returnflag | l_linestatus | count_order |
+--------------+--------------+-------------+
| N            | F            | 38657144    |
| N            | O            | 2999975927  |
| A            | F            | 1480681438  |
| R            | F            | 1480675200  |
+--------------+--------------+-------------+
4 row(s) fetched.
Elapsed 12.473 seconds.


real	0m12.711s
user	2m53.323s
sys	0m5.760s

Duckdb completes it almost 2x faster:

andrewlamb@Andrews-MacBook-Pro-3:~/Downloads$ time duckdb -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
100% ▕██████████████████████████████████████▏ (00:00:06.37 elapsed)
┌──────────────┬──────────────┬─────────────┐
│ l_returnflag │ l_linestatus │ count_order │
│   varchar    │   varchar    │    int64    │
├──────────────┼──────────────┼─────────────┤
│ R            │ F            │  1480675200 │
│ N            │ F            │    38657144 │
│ A            │ F            │  1480681438 │
│ N            │ O            │  2999975927 │
└──────────────┴──────────────┴─────────────┘

real	0m7.220s
user	1m34.961s
sys	0m4.062s

[alamb]

Here are some sampling results:

Profile with samply:

samply record -- datafusion-cli -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"

Looks to me like there are some obvious room for improvement in the code:

datafusion/datafusion/physical-plan/src/aggregates/group_values/multi_group_by/bytes_view.rs

Lines 225 to 237 in 057583d

	// Check if nulls equal firstly
	let exist_null = self.nulls.is_null(lhs_row);
	let input_null = array.is_null(rhs_row);
	if let Some(result) = nulls_equal_to(exist_null, input_null) {
	return result;
	}
	// Otherwise, we need to check their values
	let exist_view = self.views[lhs_row];
	let exist_view_len = exist_view as u32;
	let input_view = array.views()[rhs_row];
	let input_view_len = input_view as u32;

1. Special case when there are no null values to skip the null check (there are no nulls in tpch data)
2. skip bounds check when accessing view value

There are probably other ways to make this faster too

[alamb]

BTW DuckDB is also 2x faster with the tPCH SF=100 dataset (which is much faster to generate)

Create dataset:

nice tpchgen-cli --tables=lineitem --format=parquet --scale-factor 100

andrewlamb@Andrews-MacBook-Pro-3:~/Downloads$ time duckdb -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
┌──────────────┬──────────────┬─────────────┐
│ l_returnflag │ l_linestatus │ count_order │
│   varchar    │   varchar    │    int64    │
├──────────────┼──────────────┼─────────────┤
│ R            │ F            │   148067261 │
│ N            │ O            │   300058170 │
│ N            │ F            │     3864590 │
│ A            │ F            │   148047881 │
└──────────────┴──────────────┴─────────────┘

real	0m0.910s
user	0m9.005s
sys	0m0.666s

andrewlamb@Andrews-MacBook-Pro-3:~/Downloads$ time datafusion-cli -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
DataFusion CLI v50.3.0
+--------------+--------------+-------------+
| l_returnflag | l_linestatus | count_order |
+--------------+--------------+-------------+
| N            | O            | 300058170   |
| R            | F            | 148067261   |
| A            | F            | 148047881   |
| N            | F            | 3864590     |
+--------------+--------------+-------------+
4 row(s) fetched.
Elapsed 1.291 seconds.


real	0m1.359s
user	0m16.846s
sys	0m0.708s

[Dandandan]

For optimizing create_hashes, we could maybe look into creating specialized code for creating hashes for 2 or 3 columns.

[camuel]

somehow on my amd64 desktop duckdb is similar but DataFusion is much worse. See below:

cam@cam-System-Product-Name:~/Embucket/repos/datafusion$ time datafusion-cli -c "select l_returnflag,l_linestatus, count(*) as count_order from 'sf1000/lineitem/lineitem*.parquet' group by l_returnflag, l_linestatus;"
DataFusion CLI v50.0.0
+--------------+--------------+-------------+
| l_returnflag | l_linestatus | count_order |
+--------------+--------------+-------------+
| R            | F            | 1480675200  |
| N            | O            | 2999975927  |
| A            | F            | 1480681438  |
| N            | F            | 38657144    |
+--------------+--------------+-------------+
4 row(s) fetched. 
Elapsed 20.319 seconds.


real	0m20.379s
user	3m46.062s
sys	0m1.353s

given it is a higher percentage of a longer time, just that memcpy takes more then twice longer on my machine than on Andrew's when DuckDB somehow is roughly the same. So perhaps I have another amd64 related issue on my machine on top of the mentioned above inefficiency

[alamb]

So perhaps I have another amd64 related issue on my machine on top of the mentioned above inefficiency

One thing to potentially try is compiling datafusion-cli with this (which I think allows it to use avx-512 and other more recent instructions)

RUSTFLAGS='-C target-cpu=native'

[Omega359]

I'm running on a AMD Ryzen AI 9 HX 370 so I'll try and reproduce this as well.

[Omega359]

time duckdb -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
100% ▕██████████████████████████████████████▏ (00:00:09.37 elapsed)     
┌──────────────┬──────────────┬─────────────┐
│ l_returnflag │ l_linestatus │ count_order │
│   varchar    │   varchar    │    int64    │
├──────────────┼──────────────┼─────────────┤
│ R            │ F            │  1480675200 │
│ N            │ F            │    38657144 │
│ A            │ F            │  1480681438 │
│ N            │ O            │  2999975927 │
└──────────────┴──────────────┴─────────────┘

real	0m10.657s
user	1m38.246s
sys	0m16.266s

time datafusion-cli -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
DataFusion CLI v50.3.0
+--------------+--------------+-------------+
| l_returnflag | l_linestatus | count_order |
+--------------+--------------+-------------+
| N            | O            | 2999975927  |
| A            | F            | 1480681438  |
| R            | F            | 1480675200  |
| N            | F            | 38657144    |
+--------------+--------------+-------------+
4 row(s) fetched. 
Elapsed 15.924 seconds.


real	0m16.128s
user	5m19.545s
sys	0m17.042s

[Omega359]

This is the samply output I see on my machine:

[camuel]

Thank you. I see that DataFusion is faster on your end and DuckDB is slower so the difference is completely off. Somehow on my end I have almost the same factor on amd and on macbook which is about x3. Perhaps it has something to do with the way parquets are generated. I've tried to regenerate SF1000 with the gentpch-rs defaults and it produces single parquet files for each table and while DuckDB performance hasn't changed much, DataFusion performance got worse by factor of x1.36 so the difference got even bigger Tried to build with native flag as Andrew Lamb suggested above performance got improved only by less than 10% I think somehow DuckDB is slow on your endb and not DataFusion being slow on my end. If we just focus on DataFusion performance, it is easy to see, that for low cardinality grouping with two dict encoded columns it is spending too much time on hashing and on memcmp, it is two single character columns with 4 unique character combinations, so many opportunities for optimization. I am sure neither DuckDB nor DataFusion exploit all of it, but DuckDB somehow exploits more.

…

On Mon, Nov 3, 2025, 10:01 AM Bruce Ritchie ***@***.***> wrote: *Omega359* left a comment (apache/datafusion#18411) <#18411 (comment)> This is the samply output I see on my machine: image.png (view on web) <https://github.com/user-attachments/assets/b9ffc9e7-fc43-4d7b-96d8-25d7331cd723> — Reply to this email directly, view it on GitHub <#18411 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADGCJH3OZW5VZVCAPJDFJL326C47AVCNFSM6AAAAACKZKEJA6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTIOBRHAZDMMJVG4> . You are receiving this because you were mentioned.Message ID: ***@***.***>