You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2025/10/31 16:22:20 UTC

[I] Report that TPCH q1 is 3x slower than duckdb [datafusion]

alamb opened a new issue, #18411:
URL: https://github.com/apache/datafusion/issues/18411

   ### Describe the bug
   
   Breaking out from https://github.com/apache/datafusion/issues/18341#issuecomment-3466350575 from @2010YOUY01 
   
   There is a discord discussion for slow tpch q1 https://discord.com/channels/885562378132000778/1290751484807352412/1432863136612089959 from @camuel
   
   ```sql
   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):
   ```sql
   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_


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3497666584

   Is it currently possible to dynamically switch between `Utf8` and `Utf8View`? I was thinking that for the very short strings like the ones in `l_returnflag` and `l_linestatus` it's actually counterproductive to use `Utf8View`. Those short string will all get inlined and end up using 16 bytes per entry instead of the ~5 (offset + 1 data byte) you would use per entry in `Utf8`. Additionally there's the conditional for each entry to check if it's inline or not.
   
   The only problem I see is that it's hard to know up front which of the two you options (view or not) to choose based on the parquet metadata. On a per row group basis you can decide this since the dictionary is entirely available in one page so you can figure out the maximum string length for the row group. But if that choice needs to be made for the entire file it's not so simple. Here too `ENUM` could be a possible heuristic here, but I'm not sure how often that actually gets used in practice.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3497729701

   > Wouldn't this be even more the case if using ListingTable with many files?
   
   Indeed.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3660706100

   FWIW if anyone is interested, I have been playing around with making this query faster and made some non trivial progress
   - https://github.com/apache/datafusion/pull/19344
   
   However, it is still not quite as fast (though it is much closer)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677789347

   > > 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.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3707073395

   Looking at the the query and profiling, I think there is a nice optimization we can do for optimizing `AVG` that we don't yet do.
   
   Quite a bit of time is spent in the (sum/avg/count) accumulators:
   
   <img width="1570" height="144" alt="Image" src="https://github.com/user-attachments/assets/3d6ffdd5-0414-4355-8397-f527226c3b5d" />
   
   
   ```
   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):
   
   ```
       sum(l_quantity) / count_order,
       sum(l_extendedprice) / count_order,
       sum(l_discount) as avg_disc,
       count_order
   ```
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3666735206

   > > Is there a mechanism in DataFusion already to carry the necessary meta information from the table all the way to the aggregation? The Arrow physical types alone aren't sufficiently rich to model that.
   > 
   > I wonder if using an actual DictionaryArray<Int8, Utf8> (with a Int8 index) would get us most of the performance win here πŸ€”
   
   Not sure this would gain much, ideally `UInt8Array` would be used as physical type, maybe supported by an extension type?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3491095422

   Another thing: for `StringViewArray ` could also be implemented faster for values with length <= 12, by hashing the inlined value rather than the &str. Now it always does the latter, but this is slower than hashing primitives.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "camuel (via GitHub)" <gi...@apache.org>.
camuel commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3475260492

   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
   ```
   <img width="1623" height="290" alt="Image" src="https://github.com/user-attachments/assets/de198800-dc6c-48a8-9317-176ef71f70d6" />
   
   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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3707191762

   I found also some opportunity to optimize `Nullstate` and accumuluators:
   https://github.com/apache/datafusion/pull/19625


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3675986962

   (or make the parquet reader faster)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677773015

   > 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.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3678826618

   > 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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3490378796

   > 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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3481216403

   ```
   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
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665233281

   You are totally right of course - we shouldn't make optimizations that are only useful for some tpc-h query and not in the wild.
   Doing the optimization in general for short strings is super useful (and work for all short string / byte views).
   
   > Is there a mechanism in DataFusion already to carry the necessary meta information from the table all the way to the aggregation? The Arrow physical types alone aren't sufficiently rich to model that.
   
   Although I think that this might be useful as well, there is information in the table schema (i.e. certain fields are `char(1)`) that helps making the parquet read or aggregations on those fields faster and consume less memory than processing it as a variable-width utf8 field.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677856399

   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?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677762179

   > > 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.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3675893106

   Update: When I tested locally with a combination of the following two PRS:
   - https://github.com/apache/datafusion/pull/19413
   - https://github.com/apache/datafusion/pull/19374
   
   DataFusion (will be 52) is at 883ms
   ```shell
   hyperfine --warmup 3 " ./datafusion-cli-the-works   -c \"select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;\" "
   
   Benchmark 1:  ./datafusion-cli-the-works   -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
     Time (mean Β± Οƒ):     897.6 ms Β±  17.7 ms    [User: 10386.4 ms, System: 642.5 ms]
     Range (min … max):   883.7 ms … 942.0 ms    10 runs
   ```
   
   DuckDB is at 808ms
   ```
   hyperfine --warmup 3 "duckdb -c \"select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;\" "
   
   Benchmark 1: duckdb -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
     Time (mean Β± Οƒ):     794.6 ms Β±   6.7 ms    [User: 9802.8 ms, System: 470.0 ms]
     Range (min … max):   787.3 ms … 808.1 ms    10 runs
   ```
   
   I think to get it much faster we would have to really optimize for short strings, likely similar to to @rluvaton 's suggestion:
   - For small number of groups we can also use a faster hash map that is optimized for small number of keys
   
   In this case, I think we could keep a hash table of `u128s` (entirely inlined short strings) and then fall back to using a hash table w/ buffers if/when we saw long strings
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3664019285

   From discord @pepijnve : `l_returnflag and l_linestatus are both char(1)`
   
   I think this is an interasting obervation as well - `l_returnflag` and `l_linestatus` could:
   * be packed into a single 16-bit value
   * use single column aggregation instead of the slower multi column aggregation
   * and (if beneficial) use direct indexing (only 65K values) instead of hashing/lookup table.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665182575

   > Thinking about it, I think also Parquet decoding could be made more efficient from this info as well (into primitive byte array instead of utf8 view, no utf8 validation needed).
   
   I 100% agree that if the goal was to write an executor that handled TPCH queries and took advantage of the patterns in the data (ahem benchmaxxing) we could do much better.
   
   However my goal is to build a system that is useful for real world queries, so I try and focus on optimizations that are applicable to real world queries. 
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3497725100

   > The only problem I see is that it's hard to know up front which of the two you options (view or not) to choose based on the parquet metadata. On a per row group basis you can decide this since the dictionary is entirely available in one page so you can figure out the maximum string length for the row group. But if that choice needs to be made for the entire file it's not so simple. Here too `ENUM` could be a possible heuristic here, but I'm not sure how often that actually gets used in practice.
   
   Wouldn't this be even more the case if using ListingTable with many files> 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Report that TPCH q1 is 3x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3474037162

   BTW DuckDB is also 2x faster with the tPCH SF=100 dataset (which is much faster to generate)
   
   Create dataset:
   ```shell
   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
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Report that TPCH q1 is 3x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3473978781

   Here are some sampling results:
   
   Profile with samply:
   ```shell
   samply record -- datafusion-cli -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
   ```
   
   <img width="1421" height="1001" alt="Image" src="https://github.com/user-attachments/assets/e96ff99f-02ea-4cbe-8e7a-202e239d9ed7" />
   
   Looks to me like there are some obvious room for improvement in the code: https://github.com/apache/datafusion/blob/057583d5c02fb6b555bdd64b87cf596daa05d70f/datafusion/physical-plan/src/aggregates/group_values/multi_group_by/bytes_view.rs#L225-L237
   
   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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3476138986

   > 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 more)
   
   ```shell
   RUSTFLAGS='-C target-cpu=native'
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665163754

   > > I think this is an interasting obervation as well - l_returnflag and l_linestatus could...
   > 
   > Is there a mechanism in DataFusion already to carry the necessary meta information from the table all the way to the aggregation? The Arrow physical types alone aren't sufficiently rich to model that.
   
   At this point, I think no.
   
   Thinking about it, I think also Parquet decoding could be made more efficient from this info as well (into primitive byte array instead of utf8 view, no utf8 validation needed).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3671693650

   > Does char(1) must be single byte? What about UTF-8 escape sequence?
   
   SQL leaves the character repertoire undefined. In Rust you probably want to assume Unicode, so one Unicode scalar. That means 1 to 4 bytes.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "rluvaton (via GitHub)" <gi...@apache.org>.
rluvaton commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3671662944

   For small number of groups we can also use a faster hash map that is optimized for small number of keys
   
   And once we go beyond that, we can fallback


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3481826157

   This is the samply output I see on my machine: 
   
   <img width="3388" height="382" alt="Image" src="https://github.com/user-attachments/assets/b9ffc9e7-fc43-4d7b-96d8-25d7331cd723" />


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3503726327

   > The decoder maintained a hash table of strings and single instanced everything and memoized hash values.
   
   This is basically how the existing ByteViewGroupBy thing works (it is basically interning the string values into a (new) output array)
   
   There is no global string interning, however, so when we see strings coming in from arrow arrays, they aren't already interned and we don't have a pre-existing hash
   
   However, it is likely we compute a hash for the same string many times (e.g. they will be computed for repartitioning and then again in the group by itself)
   
   We discussed something similar here
   - https://github.com/apache/datafusion/issues/12596


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3497705454

   > πŸ€” 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
   
   @alamb in the GIS data format codecs I wrote long ago we solved this by interning strings. The decoder maintained a hash table of strings and single instanced everything. I haven't read it in detail yet, but the DuckDB related thesis that was linked to on Discord (https://homepages.cwi.nl/~boncz/msc/2025-OmidAfroozeh.pdf) seems to be using similar techniques. Unfortunately I don't see how you would be able to use this while sticking to the Arrow memory layouts.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665195440

   That being said, one idea I had about optimizing the case of "all short strings" (aka all strings that fit in 12 bytes or less views) I do think we could have the group values implementation special case short strings
   1. If all strings in the input array were short (no data buffers) stored them as a HashSet(u128) (aka stored the values directly)
   
   If a new batch arrived that had longer strings, then we would have to fallback to the current implementation that stored data buffers. 
   
   
   It would certainly help this query and I could definitely see it being helpful for real queries on short string columns πŸ€” 
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677843883

   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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "camuel (via GitHub)" <gi...@apache.org>.
camuel commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3483867131

   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)
   > <https://github.com/apache/datafusion/issues/18411#issuecomment-3481826157>
   >
   > 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
   > <https://github.com/apache/datafusion/issues/18411#issuecomment-3481826157>,
   > or unsubscribe
   > <https://github.com/notifications/unsubscribe-auth/AADGCJH3OZW5VZVCAPJDFJL326C47AVCNFSM6AAAAACKZKEJA6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTIOBRHAZDMMJVG4>
   > .
   > You are receiving this because you were mentioned.Message ID:
   > ***@***.***>
   >
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3476676942

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665050576

   > I think this is an interasting obervation as well - l_returnflag and l_linestatus could...
   
   Is there a mechanism in DataFusion already to carry the necessary meta information from the table all the way to the aggregation? The Arrow physical types alone aren't sufficiently rich to model that.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "rluvaton (via GitHub)" <gi...@apache.org>.
rluvaton commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677917009

   I'm thinking for a while to use different hasher that might be faster
   
   Like https://github.com/ogxd/gxhash


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677872704

   > 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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3707224218

   https://github.com/apache/datafusion/pull/19624
   
   This is a poc of the avg to sum conversion.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Report that TPCH q1 is 3x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3473952514

   I can reproduce some speed difference locally with the tpchgen dataset:
   
   Make dataset with https://github.com/clflushopt/tpchgen-rs
   
   ```shell
   tpchgen-cli --tables=lineitem --format=parquet --scale-factor 1000
   ```
   Dataset is 246GB:
   ```shell
   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
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3474209505

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665264646

   Fully agree as well that any work should not be done in purely to boost the benchmark results. Single character 'enums' are a pretty common construct though. The suggestion is a bit self serving actually πŸ˜„. Our internal data format used to use single character enums as well. I've changed the schema to use `uint8` instead to be able to benefit from more efficient processing. It's an easy performance win, but it comes at the cost of making the data and the queries less human readable because I've lost the mnemonics provided by the characters.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3665303955

   > Is there a mechanism in DataFusion already to carry the necessary meta information from the table all the way to the aggregation? The Arrow physical types alone aren't sufficiently rich to model that.
   
   
   I wonder if using an actual DictionaryArray<Int8, Utf8> (with a Int8 index) would get us most of the performance win here πŸ€” 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "rluvaton (via GitHub)" <gi...@apache.org>.
rluvaton commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677918283

   Once we have data about the cardinality of the columns we can reorder them for highest cardinality first


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "geoffreyclaude (via GitHub)" <gi...@apache.org>.
geoffreyclaude commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3669423776

   These optims sound very similar to what I proposed in https://github.com/apache/datafusion/issues/19241 for `IN LIST` actually!
   
   Maybe we could work on a common solution? I'll try to open a PR soon, I have very nice bench results but the code is a bit scary looking for now.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3490975739

   > πŸ€” 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
   
   For values with length <= 12 (which is the case in Q1 since the columns involved are both `char(1)`)  views lose their dictionary advantage since each entry will be an inline value. I think you would have to solve this at the source and make them actual dictionary encoded values instead (if that's not already the case).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3491018618

   @alamb `tpchgen-cli` currently uses `StringViewArray` for those columns. Would it be worth experimenting with actual dictionary encoding instead for `l_returnflag` and `l_linestatus` given their low cardinality?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "rluvaton (via GitHub)" <gi...@apache.org>.
rluvaton commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3671633257

   > From discord @pepijnve : `l_returnflag and l_linestatus are both char(1)`
   > 
   > I think this is an interesting obervation as well - `l_returnflag` and `l_linestatus` could:
   > * be packed into a single 16-bit value
   
   Does `char(1)` must be single byte? What about UTF-8?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3677617324

   > 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?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3661074237

   With the various tricks in https://github.com/apache/datafusion/pull/19344
   DataFusion now goes 8.473s -- not quite as fast as DuckDB but pretty close (0m7.220s)
   
   ```
   time ~/Software/datafusion2/target/profiling/datafusion-cli  -c "select l_returnflag,l_linestatus, count(*) as count_order from 'lineitem.parquet' group by l_returnflag, l_linestatus;"
   DataFusion CLI v51.0.0
   +--------------+--------------+-------------+
   | l_returnflag | l_linestatus | count_order |
   +--------------+--------------+-------------+
   | R            | F            | 1480675200  |
   | A            | F            | 1480681438  |
   | N            | F            | 38657144    |
   | N            | O            | 2999975927  |
   +--------------+--------------+-------------+
   4 row(s) fetched.
   Elapsed 8.194 seconds.
   
   
   real	0m8.473s
   user	1m40.730s
   sys	0m6.544s
   ```
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] TPCH q1 with no predicates is 2x slower than duckdb [datafusion]

Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #18411:
URL: https://github.com/apache/datafusion/issues/18411#issuecomment-3659234056

   > > πŸ€” 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
   > 
   > [@alamb](https://github.com/alamb) in the GIS data format codecs I worked on long ago we solved this by interning strings. The decoder maintained a hash table of strings and single instanced everything and memoized hash values. I haven't read it in detail yet, but the DuckDB related thesis that [@camuel](https://github.com/camuel) linked to on Discord (https://homepages.cwi.nl/~boncz/msc/2025-OmidAfroozeh.pdf) seems to be using similar techniques. Unfortunately I don't see how you would be able to use this while sticking to the Arrow memory layouts.
   
   This could be done at the plan level perhaps? Keep the hash of the column around and share / use it in other operators


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org