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/01/02 13:02:36 UTC
[I] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
alamb opened a new issue, #13983:
URL: https://github.com/apache/datafusion/issues/13983
### Is your feature request related to a problem or challenge?
- Like https://github.com/apache/datafusion/issues/13099
- Related to #13334
### Describe the solution you'd like
Now that DataFusion 44.0.0 is released, It would be great to update ClickBench https://benchmark.clickhouse.com/ with the newest version
ClickBench is a benchmark heavy on filtering and aggregation that we have used as an optimization target for the last several releases.
### Describe alternatives you've considered
- Here is the PR that @pmcgleenon made for DataFusion 43.0.0: https://github.com/ClickHouse/ClickBench/pull/251
### Additional context
I am especially interested to see the improvements after the vectorized comparison from @Rachelint @jayzhan211 @Dandandan and others in DataFusion aggregate code
- https://github.com/apache/datafusion/pull/12996
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "Rachelint (via GitHub)" <gi...@apache.org>.
Rachelint commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2614035802
Here is the flamegraph for pushdown, 36+% time costs in decompression.
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2613378310
> [@alamb](https://github.com/alamb) Excited to see further optmization about `late materialization`, it is really an important feature as I think ! I tried to use it in `HoraeDB` last year, and found the same problem mentioned in [#6921](https://github.com/apache/datafusion/pull/6921) and it is frustrated...
>
> I will profile again with setting `datafusion.execution.parquet.pushdown_filters = true;`, and see what optimizations we can do in `datafusion`.
Thanks @Rachelint
For this case I believe the core change needs to happen in the Parquet reader. The background as I understand it is described here
- https://github.com/apache/arrow-rs/issues/5523
@XiangpengHao has a prototype in the following PR
- https://github.com/apache/arrow-rs/pull/6921
A good next step would be to measure how much faster DataFusion is with that PR -- the previous measurements we had a few other optimizations mixed in.
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2609242828
Someone pointed out to me the other day that DataFusion 43 is no longer on top of the [ClickBench Parquet Leaderboard](https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQiI6ZmFsc2UsIkFsbG95REIgKHR1bmVkKSI6ZmFsc2UsIkF0aGVuYSAocGFydGl0aW9uZWQpIjpmYWxzZSwiQXRoZW5hIChzaW5nbGUpIjpmYWxzZSwiQXVyb3JhIGZvciBNeVNRTCI6ZmFsc2UsIkF1cm9yYSBmb3IgUG9zdGdyZVNRTCI6ZmFsc2UsIkJ5Q29uaXR5IjpmYWxzZSwiQnl0ZUhvdXNlIjpmYWxzZSwiY2hEQiAoRGF0YUZyYW1lKSI6ZmFsc2UsImNoREIgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6dHJ1ZSwiY2hEQiI6ZmFsc2UsIkNpdHVzIjpmYWxzZSwiQ2xpY2tIb3VzZSBDbG91ZCAoYXdzKSI6ZmFsc2UsIkNsaWNrSG91c2UgQ2xvdWQgKGF6dXJlKSI6ZmFsc2UsIkNsaWNrSG91c2UgQ2xvdWQgKGdjcCkiOmZhbHNlLCJDbGlja0hvdXNlIChkYXRhIGxha2UsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKGRhdGEgbGFrZSwgc2luZ2xlKSI6ZmFsc2UsIkNsaWNrSG91c2UgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJDbGlja0hvdXNlICh3ZWIpIjpmYWxzZSwiQ2xpY2tIb3VzZSI6ZmFsc2UsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHR1bmVkLCBtZW
1vcnkpIjpmYWxzZSwiQ2xvdWRiZXJyeSI6ZmFsc2UsIkNyYXRlREIiOmZhbHNlLCJDcnVuY2h5IEJyaWRnZSBmb3IgQW5hbHl0aWNzIChQYXJxdWV0KSI6ZmFsc2UsIkRhdGFiZW5kIjpmYWxzZSwiRGF0YUZ1c2lvbiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjp0cnVlLCJEYXRhRnVzaW9uIChQYXJxdWV0LCBzaW5nbGUpIjpmYWxzZSwiQXBhY2hlIERvcmlzIjpmYWxzZSwiRHJpbGwiOmZhbHNlLCJEcnVpZCI6ZmFsc2UsIkR1Y2tEQiAoRGF0YUZyYW1lKSI6ZmFsc2UsIkR1Y2tEQiAobWVtb3J5KSI6ZmFsc2UsIkR1Y2tEQiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjpmYWxzZSwiRHVja0RCIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCI6ZmFsc2UsIkVsYXN0aWNzZWFyY2ggKHR1bmVkKSI6ZmFsc2UsIkdsYXJlREIiOmZhbHNlLCJHcmVlbnBsdW0iOmZhbHNlLCJIZWF2eUFJIjpmYWxzZSwiSHlkcmEiOmZhbHNlLCJIeXBlciAoUGFycXVldCkiOnRydWUsIkh5cGVyIjpmYWxzZSwiSW5mb2JyaWdodCI6ZmFsc2UsIktpbmV0aWNhIjpmYWxzZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6ZmFsc2UsIk1hcmlhREIiOmZhbHNlLCJNb25ldERCIjpmYWxzZSwiTW9uZ29EQiI6ZmFsc2UsIk1vdGhlckR1Y2siOmZhbHNlLCJNeVNRTCAoTXlJU0FNKSI6ZmFsc2UsIk15U1FMIjpmYWxzZSwiT2N0b1NRTCI6ZmFsc2UsIk9wdGVyeXgiOmZhbHNlLCJPeGxhIjpmYWxzZSwiUGFuZGFzIChEYXRhRnJhbWUpIjpmYWxzZSwiUGFyYWRlREIgKFB
hcnF1ZXQsIHBhcnRpdGlvbmVkKSI6dHJ1ZSwiUGFyYWRlREIgKFBhcnF1ZXQsIHNpbmdsZSkiOmZhbHNlLCJwZ19kdWNrZGIgKE1vdGhlckR1Y2sgZW5hYmxlZCkiOmZhbHNlLCJwZ19kdWNrZGIiOmZhbHNlLCJQb3N0Z3JlU1FMIHdpdGggcGdfbW9vbmNha2UiOmZhbHNlLCJQaW5vdCI6ZmFsc2UsIlBvbGFycyAoRGF0YUZyYW1lKSI6ZmFsc2UsIlBvbGFycyAoUGFycXVldCkiOmZhbHNlLCJQb3N0Z3JlU1FMICh0dW5lZCkiOmZhbHNlLCJQb3N0Z3JlU1FMIjpmYWxzZSwiUXVlc3REQiI6ZmFsc2UsIlJlZHNoaWZ0IjpmYWxzZSwiU2VsZWN0REIiOmZhbHNlLCJTaW5nbGVTdG9yZSI6ZmFsc2UsIlNub3dmbGFrZSI6ZmFsc2UsIlNwYXJrIjpmYWxzZSwiU1FMaXRlIjpmYWxzZSwiU3RhclJvY2tzIjpmYWxzZSwiVGFibGVzcGFjZSI6ZmFsc2UsIlRlbWJvIE9MQVAgKGNvbHVtbmFyKSI6ZmFsc2UsIlRpbWVzY2FsZSBDbG91ZCI6ZmFsc2UsIlRpbWVzY2FsZURCIChubyBjb2x1bW5zdG9yZSkiOmZhbHNlLCJUaW1lc2NhbGVEQiI6ZmFsc2UsIlRpbnliaXJkIChGcmVlIFRyaWFsKSI6ZmFsc2UsIlVtYnJhIjpmYWxzZSwiVmljdG9yaWFMb2dzIjpmYWxzZX0sInR5cGUiOnsiQyI6dHJ1ZSwiY29sdW1uLW9yaWVudGVkIjp0cnVlLCJQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsIm1hbmFnZWQiOnRydWUsImdjcCI6dHJ1ZSwic3RhdGVsZXNzIjp0cnVlLCJKYXZhIjp0cnVlLCJDKysiOnRydWUsIk15U1FMIGNvbXBhdGli
bGUiOnRydWUsInJvdy1vcmllbnRlZCI6dHJ1ZSwiQ2xpY2tIb3VzZSBkZXJpdmF0aXZlIjp0cnVlLCJlbWJlZGRlZCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwiZGF0YWZyYW1lIjp0cnVlLCJhd3MiOnRydWUsImF6dXJlIjp0cnVlLCJhbmFseXRpY2FsIjp0cnVlLCJSdXN0Ijp0cnVlLCJzZWFyY2giOnRydWUsImRvY3VtZW50Ijp0cnVlLCJHbyI6dHJ1ZSwic29tZXdoYXQgUG9zdGdyZVNRTCBjb21wYXRpYmxlIjp0cnVlLCJEYXRhRnJhbWUiOnRydWUsInBhcnF1ZXQiOnRydWUsInRpbWUtc2VyaWVzIjp0cnVlfSwibWFjaGluZSI6eyIxNiB2Q1BVIDEyOEdCIjpmYWxzZSwiOCB2Q1BVIDY0R0IiOmZhbHNlLCJzZXJ2ZXJsZXNzIjpmYWxzZSwiMTZhY3UiOmZhbHNlLCJjNmEuNHhsYXJnZSwgNTAwZ2IgZ3AyIjp0cnVlLCJMIjpmYWxzZSwiTSI6ZmFsc2UsIlMiOmZhbHNlLCJYUyI6ZmFsc2UsImM2YS5tZXRhbCwgNTAwZ2IgZ3AyIjpmYWxzZSwiMTJHaUIiOmZhbHNlLCI4R2lCIjpmYWxzZSwiMTIwR2lCIjpmYWxzZSwiMTZHaUIiOmZhbHNlLCIyMzZHaUIiOmZhbHNlLCIzMkdpQiI6ZmFsc2UsIjY0R2lCIjpmYWxzZSwiYzVuLjR4bGFyZ2UsIDUwMGdiIGdwMiI6ZmFsc2UsIkFuYWx5dGljcy0yNTZHQiAoNjQgdkNvcmVzLCAyNTYgR0IpIjpmYWxzZSwiYzUuNHhsYXJnZSwgNTAwZ2IgZ3AyIjpmYWxzZSwiYzZhLjR4bGFyZ2UsIDE1MDBnYiBncDIiOmZhbHNlLCJjbG91ZCI6ZmFsc2UsImRjMi44eGxhcmdlIjpmYWxzZ
SwicmEzLjE2eGxhcmdlIjpmYWxzZSwicmEzLjR4bGFyZ2UiOmZhbHNlLCJyYTMueGxwbHVzIjpmYWxzZSwiUzIiOmZhbHNlLCJTMjQiOmZhbHNlLCIyWEwiOmZhbHNlLCIzWEwiOmZhbHNlLCI0WEwiOmZhbHNlLCJYTCI6ZmFsc2UsIkwxIC0gMTZDUFUgMzJHQiI6ZmFsc2UsImM2YS40eGxhcmdlLCA1MDBnYiBncDMiOmZhbHNlLCIxNiB2Q1BVIDY0R0IiOmZhbHNlLCI0IHZDUFUgMTZHQiI6ZmFsc2UsIjggdkNQVSAzMkdCIjpmYWxzZX0sImNsdXN0ZXJfc2l6ZSI6eyIxIjp0cnVlLCIyIjp0cnVlLCIzIjp0cnVlLCI0Ijp0cnVlLCI4Ijp0cnVlLCIxNiI6dHJ1ZSwiMzIiOnRydWUsIjY0Ijp0cnVlLCIxMjgiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsInVuZGVmaW5lZCI6dHJ1ZX0sIm1ldHJpYyI6ImhvdCIsInF1ZXJpZXMiOlt0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlXX0=)
(actually it was one of the people who has spent substntial time optimizing Hyper...)
Thus I think it is that much more valuable to get some DataFusion 44 numbers on the board
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #13983: Update ClickBench benchmarks with DataFusion `44.0.0`
URL: https://github.com/apache/datafusion/issues/13983
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2611999149
> I think Q8, Q16~18, Q35 can be closer to `hyper` in 44.0, they are improved in [#12996](https://github.com/apache/datafusion/pull/12996) And Q35 can be even much faster when [#13617](https://github.com/apache/datafusion/pull/13617) is merged (unfortunately, it can just be released in 46.0 for my long delay recently...)
>
> But Q23 is unbelievalbely fast in hyper... I think we may need to profile and think how can we improve it.
I agree -- in case anyone else wants to see hyper reported 5x faster than DataFusion and 6x faster than DuckDB
<img width="1096" alt="Image" src="https://github.com/user-attachments/assets/f52e7d8d-73b7-4805-93b3-12162467dd8a" />
I think this is Q23
https://github.com/apache/datafusion/blob/11b7b5c215012231e5768fc5be3445c0254d0169/benchmarks/queries/clickbench/queries.sql#L22
```sql
SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
```
Profiling it like this:
```shell
$ datafusion-cli -c "SELECT \"SearchPhrase\", MIN(\"URL\"), MIN(\"Title\"), COUNT(*) AS c, COUNT(DISTINCT \"UserID\") FROM hits_partitioned WHERE \"Title\" LIKE '%Google%' AND \"URL\" NOT LIKE '%.google.%' AND \"SearchPhrase\" <> '' GROUP BY \"SearchPhrase\" ORDER BY c DESC LIMIT 10;"
```
26% of the time goes to snappy decompression and 40% of the time to utf8 validation:
<img width="1728" alt="Image" src="https://github.com/user-attachments/assets/60abeb01-6dcd-47d5-9c0c-ca7b46c82007" />
Here is the full [flamegraph.svg](https://github.com/user-attachments/assets/00438b49-5348-4c2f-94c4-d8867fbdc502)
So by my calculations the snappy decompression time alone in DataFusion (0.26 * 10.28s = 2.6s) takes longer than the hyper reported time of 1.8s 😕
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2643707312
Thanks @pmcgleenon ! I am closing this one down.
It turns out we have just released datafusion-45 this morning so we are ready for the run whenever you are !
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2625318298
> hi [@alamb](https://github.com/alamb) I could pick this up towards the end of the week / early next week, does that work?
Yes of course! Thank you so much!
> by the way last time I checked it (a few weeks back) I noticed that this [change in ClickBench](https://github.com/ClickHouse/ClickBench/commit/0d89d4f5306c1cc75ebb10779f2c1e67873ca9fe) has introduced a regression. [One of the Clickbench queries](https://github.com/ClickHouse/ClickBench/blob/main/datafusion/queries.sql#L29C1-L29C233) fails when using `read -r query` instead of `read query` in the `run.sh` script
Thanks for checking -- I was not aware of that change
> the clickbench `run.sh` script reports the result `[null, null, null]` for that specific query with both single and partitioned
>
> Do you think this a Datafusion bug?
I am not sure to be honest -- I think we would need to run the query and see what the results are now 🤔
I will put this on my list of things to look at
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2609285860
I also filed a ticket to track running clickbench on DataFusion 45 once that is released in a few weeks
- https://github.com/apache/datafusion/issues/14246
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612188517
> [@alamb](https://github.com/alamb) 🤔 Q23 seems to be `SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10` ?
🤔 you are right indeed 🤦 -- sorry about that (I went the wrong direction)
https://github.com/apache/datafusion/blob/11b7b5c215012231e5768fc5be3445c0254d0169/benchmarks/queries/clickbench/queries.sql#L24
I will profile that and report back
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "Rachelint (via GitHub)" <gi...@apache.org>.
Rachelint commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612095944
@alamb query seems to be `SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10` ?
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "pmcgleenon (via GitHub)" <gi...@apache.org>.
pmcgleenon commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2642514092
hi folks, the Clickbench PR to update the results for `44.0.0` has been merged. Thanks @alamb for helping figure out the Q28 problems with the regex expression!
Looking forward to see further improved results with the `45.0.0` release!! 🚀
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2616146141
BTW @pmcgleenon -- is there any chance you might have time to run the ClickBench benchmarks for DataFusion 44? If not no worries, we'll find someone else
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "pmcgleenon (via GitHub)" <gi...@apache.org>.
pmcgleenon commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2636181803
I've re-run this again following the [discussion on the ClickBench PR](https://github.com/ClickHouse/ClickBench/pull/301#discussion_r1941921542)
Q28 needed an update, since the regex was not working correctly
```
Query 28: SELECT REGEXP_REPLACE(Referer, '^https?://(?:www\.)?([^/]+)/.*$', '\1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM hits WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
```
After that change, the Q28 performance for datafusion `44.0.0` has regressed compared to `43.0.0`. Based on the discussion over on ClickBench I think is expected since the Q28 results for all previous datafusion releases are invalid.
<img width="1801" alt="Image" src="https://github.com/user-attachments/assets/f8a7a408-b276-4c62-bf4b-a53d83152207" />
@Dandandan @alamb I've attached the full clickbench here if you want to take a look
[clickbench-latest 2.html.zip](https://github.com/user-attachments/files/18669693/clickbench-latest.2.html.zip)
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612225847
It seems it could also benefit from some further utf8 validation speed up, filed it here https://github.com/apache/arrow-rs/issues/7014
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "pmcgleenon (via GitHub)" <gi...@apache.org>.
pmcgleenon commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2620232232
hi @alamb I could pick this up towards the end of the week / early next week, does that work?
by the way last time I checked it (a few weeks back) I noticed that this [change in ClickBench](https://github.com/ClickHouse/ClickBench/commit/0d89d4f5306c1cc75ebb10779f2c1e67873ca9fe) has introduced a regression. [One of the Clickbench queries](https://github.com/ClickHouse/ClickBench/blob/main/datafusion/queries.sql#L29C1-L29C233) fails when using `read -r query` instead of `read query` in the `run.sh` script
the clickbench `run.sh` script reports the result `[null, null, null]` for that specific query with both single and partitioned
Do you think this a Datafusion bug?
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612198384
And in this case enabling predicate pushdown results in a 2x speedup
```sql
set datafusion.execution.parquet.pushdown_filters = false;
SELECT * FROM hits_partitioned WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10;
```
Elapsed 4.108 seconds.
Elapsed 5.430 seconds.
Elapsed 4.659 seconds.
```sql
set datafusion.execution.parquet.pushdown_filters = true;
SELECT * FROM hits_partitioned WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10;
```
Elapsed 2.415 seconds.
Elapsed 2.070 seconds.
Elapsed 2.279 seconds.
Here is the flamegraph for no pushdown:
It would be cool to test with @XiangpengHao 's change to the parquet decoder here:
- https://github.com/apache/arrow-rs/pull/6921
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2633737624
> I will file a separate ticket for looking into them.
- Filed https://github.com/apache/datafusion/issues/14481 in case anyone is interested in taking a look
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "Rachelint (via GitHub)" <gi...@apache.org>.
Rachelint commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2611683278
I think Q8, Q16~18, Q35 can be closer to `hyper` in 44.0, they are improved in #12996
And Q35 can be even faster when #13617 is merged (unfortunately, it can just be released in 46.0 for my long delay recently...)
But Q23 in hyper is unbelievalbely fast in hyper... I think we may need to profile and think how can we improve it.
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612064902
> Q23 might be improved if it can utilize filter pushdown? I think a >5x improvement might come from that.
Running without filter pushdown (the default)
```sql
set datafusion.execution.parquet.pushdown_filters = false;
SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits_partitioned WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
```
I get:
Elapsed 2.232 seconds.
Elapsed 2.252 seconds.
Elapsed 2.236 seconds.
When I enabled filter pushdown it goes 15% faster.
```sql
set datafusion.execution.parquet.pushdown_filters = true;
SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits_partitioned WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
```
I get:
Elapsed 1.981 seconds.
Elapsed 1.953 seconds.
Elapsed 1.966 seconds.
Still not 5x though 🤔
Though it gives me new motivation tohelp @XiangpengHao get the pushdown improvements over the line in
https://github.com/apache/arrow-rs/pull/6921
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2633683712
> If this looks ok, I can create a PR on clickbench to update the results
First of all, thank you so much @pmcgleenon -- this is super helpful as always 🙏
I looked at the results and they are inline with my expectations. Specifically most of the major improvements mirror the improvement made by @jayzhan211 https://github.com/apache/datafusion/pull/12996 (they are similar improvements as reported in https://github.com/apache/datafusion/pull/12996#issuecomment-2457985415)
There are a few queries that appear to have gotten slower (q36 and q37)
https://github.com/apache/datafusion/blob/0d9f845a243ed90abe472e89b84a3cb5a8e681c2/benchmarks/queries/clickbench/queries.sql#L37-L38
I will file a separate ticket for looking into them.
But from my perspective these results are ready to go 🚀
(BTW I expect DataFusion `45.0.0` to be available in the next few days -- see https://github.com/apache/datafusion/issues/14008 -- and it has yet more improvements!)
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "pmcgleenon (via GitHub)" <gi...@apache.org>.
pmcgleenon commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2634016553
PR raised to update Clickbench results for datafusion `44.0.0` https://github.com/ClickHouse/ClickBench/pull/301
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "pmcgleenon (via GitHub)" <gi...@apache.org>.
pmcgleenon commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2632053433
hi @Dandandan @alamb here are the results I've got. Looks like `44.0.0` has registered improvements compared with `43.0.0`
<img width="1784" alt="Image" src="https://github.com/user-attachments/assets/7e047a92-feea-4496-b50a-1e8835939c6f" />
Attached is the updated latest Clickbench web page if you want to compare datafusion with the other DBs
[clickbench-latest.html.zip](https://github.com/user-attachments/files/18648048/clickbench-latest.html.zip)
If this looks ok, I can create a PR on clickbench to update the results
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "Rachelint (via GitHub)" <gi...@apache.org>.
Rachelint commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612300838
@alamb Excited to see further optmization about`late materialization`, it is really an important feature as I think !
I tried to use it in `HoraeDB` last year, and found the same problem mentioned in #6921 and it is frustrated...
I will profile again with setting `datafusion.execution.parquet.pushdown_filters = true;`, and see what optimizations we can do in `datafusion`.
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612001363
If we wanted to juice our numbers we could turn off ut8 validation too but I feel like that would be cheating (as most/many systems would never run without validtion on)
--
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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2612039339
Q23 might be improved if it can utilize filter pushdown? I think a >5x improvement might come from 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] Update ClickBench benchmarks with DataFusion `44.0.0` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13983:
URL: https://github.com/apache/datafusion/issues/13983#issuecomment-2648602300
Discussion about making more improvements:
- https://the-asf.slack.com/archives/C04RJ0C85UZ/p1739204225620989
--
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