Skip to content

TPCH q1 with no predicates is 2x slower than duckdb #18411

New issue
New issue
Open
Bug
Open
TPCH q1 with no predicates is 2x slower than duckdb#18411
Bug
Labels
bugSomething isn't working

Description

@alamb
alamb
opened on Oct 31, 2025

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

Activity

  1. alamb
    added
    bugSomething isn't working
    on Oct 31, 2025
  2. alamb
    added the
    Bug
    issue type on Oct 31, 2025

  3. alamb commented on Oct 31, 2025

    @alamb
    alamb
    on Oct 31, 2025
    ContributorAuthor

    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

  4. alamb commented on Oct 31, 2025

    @alamb
    alamb
    on Oct 31, 2025
    ContributorAuthor

    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;"
    Image

    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

  5. alamb commented on Oct 31, 2025

    @alamb
    alamb
    on Oct 31, 2025
    ContributorAuthor

    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
  6. alamb
    changed the title [-]Report that TPCH q1 is 3x slower than duckdb[/-] [+]TPCH q1 is 2x slower than duckdb[/+] on Oct 31, 2025
  7. alamb
    changed the title [-]TPCH q1 is 2x slower than duckdb[/-] [+]TPCH q1 with no predicates is 2x slower than duckdb[/+] on Oct 31, 2025

  8. Dandandan commented on Oct 31, 2025

    @Dandandan
    Dandandan
    on Oct 31, 2025
    Contributor

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

  9. camuel commented on Oct 31, 2025

    @camuel
    camuel
    on Oct 31, 2025

    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
    Image

    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

  10. alamb commented on Nov 1, 2025

    @alamb
    alamb
    on Nov 1, 2025
    ContributorAuthor

    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'

  11. Omega359 commented on Nov 1, 2025

    @Omega359
    Omega359
    on Nov 1, 2025
    Contributor

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

  12. Omega359 commented on Nov 3, 2025

    @Omega359
    Omega359
    on Nov 3, 2025
    Contributor
    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

  13. Omega359 commented on Nov 3, 2025

    @Omega359
    Omega359
    on Nov 3, 2025
    Contributor

    This is the samply output I see on my machine:

    Image

  14. camuel commented on Nov 4, 2025

    @camuel
    camuel
    on Nov 4, 2025 via email

  15. alamb commented on Nov 5, 2025

    @alamb
    alamb
    on Nov 5, 2025
    ContributorAuthor

    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,

    🤔 figuring out how to avoid re-hashing the same short values over and over again (StringView can be thought of as a type of dictionary encoding) is a very interesting possibility

  16. 27 remaining items

  17. github-merge-queue
    added 2 commits that reference this issue on Dec 20, 2025

    Optimize muti-column grouping with StringView/ByteView (option 2) - 2…

    8cc8c11

    Optimize hashing for StringView and ByteView (15-70% faster) (#19374)

    6fa9c1a

  18. Dandandan commented on Dec 20, 2025

    @Dandandan
    Dandandan
    on Dec 20, 2025
    Contributor

    For small number of groups we can also use a faster hash map that is optimized for small number of keys

    Perhaps column statistics could be used to show the range of values is small, and switch to direct indexing instead (i.e. even faster than a hash table).

  19. alamb commented on Dec 20, 2025

    @alamb
    alamb
    on Dec 20, 2025
    ContributorAuthor

    For small number of groups we can also use a faster hash map that is optimized for small number of keys

    Perhaps column statistics could be used to show the range of values is small, and switch to direct indexing instead (i.e. even faster than a hash table).

    If we know the range is small we could do that. I just don't think we have any statistics that can tell for sure that the range will be small.

  20. pepijnve commented on Dec 20, 2025

    @pepijnve
    pepijnve
    on Dec 20, 2025
    Contributor

    If we know the range is small we could do that. I just don't think we have any statistics that can tell for sure that the range will be small.

    I agree with that. Unfortunately min/max statistics don't tell you anything about string length.

    That's why I was asking about char(1) initially which does provide the information we're looking for. In an ideal world this type hint would be carried through all the way to the grouping code where it could be used to make this choice.

  21. Dandandan commented on Dec 20, 2025

    @Dandandan
    Dandandan
    on Dec 20, 2025
    Contributor

    If we know the range is small we could do that. I just don't think we have any statistics that can tell for sure that the range will be small.

    I agree with that. Unfortunately min/max statistics don't tell you anything about string length.

    That's why I was asking about char(1) initially which does provide the information we're looking for. In an ideal world this type hint would be carried through all the way to the grouping code where it could be used to make this choice.

    Yes agreed.

  22. alamb commented on Dec 20, 2025

    @alamb
    alamb
    on Dec 20, 2025
    ContributorAuthor

    Yeah, we could potentially do that

    Special casing (just) Char(1) smells to me like an overly TPCH specific benchmaxxing strategy though. Optimizing for small strings (like < 12 bytes) seems much more widely useful

    However, I don't actually have any data to back up my feeling that char(1) (or knowing char(1)) isn't common in practice

  23. pepijnve commented on Dec 20, 2025

    @pepijnve
    pepijnve
    on Dec 20, 2025
    Contributor

    Yep, that’s fair. There’s not much else available as signal at the moment, is there? Would an is_short_strings test that loops over offsets or view lengths be sufficiently fast to still get a performance gain?

  24. alamb commented on Dec 20, 2025

    @alamb
    alamb
    on Dec 20, 2025
    ContributorAuthor

    Yep, that’s fair. There’s not much else available as signal at the moment, is there? Would an is_short_strings test that loops over offsets or view lengths be sufficiently fast to still get a performance gain?

    yeah, I don't know -- we would probably have to hack it up and see if we can make it fast enough

  25. rluvaton commented on Dec 20, 2025

    @rluvaton
    rluvaton
    on Dec 20, 2025
    Member

    I'm thinking for a while to use different hasher that might be faster

    Like https://github.com/ogxd/gxhash

  26. rluvaton commented on Dec 20, 2025

    @rluvaton
    rluvaton
    on Dec 20, 2025
    Member

    Once we have data about the cardinality of the columns we can reorder them for highest cardinality first and with my pr to skip already all unique (but this won't help this case as there are only few output rows)

  27. alamb commented on Dec 21, 2025

    @alamb
    alamb
    on Dec 21, 2025
    ContributorAuthor

    I'm thinking for a while to use different hasher that might be faster

    I think we have the benchmarks in place now (cargo bench --bench with_hashes) to explore a new hasher

  28. Dandandan commented on Jan 3, 2026

    @Dandandan
    Dandandan
    on Jan 3, 2026
    Contributor

    Looking at the the query and profiling the full q1 query, I think there is a interesting optimization we can do for AVG.

    Quite a bit of time is spent in the (sum/avg/count) accumulators:

    Image 
    select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
        l_shipdate <= date '1998-09-02'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

    Zooming in on the avg:

        avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order

    Those accumulators will all (redundantly) compute the count.

    We should be able to rewrite it to use a shared count (and thus faster accumulators / less memory):

        sum(l_quantity) / count_order as avg_qty,
    sum(l_extendedprice) / count_order as avg_price,
    sum(l_discount) / count_order as avg_disc,
    count_order

    Next, sum(l_quantity) and sum(l_extendedprice) are already computed, so the CSE can simplify it to only create it once!

  29. Dandandan commented on Jan 3, 2026

    @Dandandan
    Dandandan
    on Jan 3, 2026
    Contributor

    I found also some opportunity to optimize Nullstate and accumuluators:
    #19625

  30. Dandandan commented on Jan 3, 2026

    @Dandandan
    Dandandan
    on Jan 3, 2026
    Contributor

    #19624

    This is a poc of the avg to sum conversion.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    Bug

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions