alamb opened a new issue, #15949: URL: https://github.com/apache/datafusion/issues/15949
### Is your feature request related to a problem or challenge? - Part of https://github.com/apache/datafusion/issues/15458 In https://github.com/apache/datafusion/pull/15748, @shruti2522 is working on a `GroupsAccumulator` for `AVG` durations, which should improve the performance significantly. However, we have no benchmarks that show the `GroupsAccumulator` is going faster as there appears to be no benchmark coverage of AVG(duration) ### Describe the solution you'd like I would like a benchmark we can run that covers `AVG(duration)` You can make a duration by subtracting timestamps ```sql > select arrow_typeof(now() - '2024-01-02'::timestamp); +------------------------------------------+ | arrow_typeof(now() - Utf8("2024-01-02")) | +------------------------------------------+ | Duration(Nanosecond) | +------------------------------------------+ 1 row(s) fetched. Elapsed 0.028 seconds. ### Describe alternatives you've considered I recommend adding a "Clickbench" extended test, similarly how @Rachelint did in - https://github.com/apache/datafusion/pull/15936 The "extended" clickbench queries are described here: https://github.com/apache/datafusion/tree/main/benchmarks/queries/clickbench#extended-queries And we could do some sort of average on timestamps, The business question might be something like "average time since the start and end of the month for different combination of user agents, os and regions" to see if some of the combinations had different time patterns Perhaps a query like this: ```sql SELECT "RegionID", "UserAgent", "OS", AVG(to_timestamp("EventTime") - '2013-07-01T20:00:00'::timestamp) as a_start, AVG(to_timestamp("EventTime") - '2013-07-01T20:00:00'::timestamp) as a_end FROM 'hits_partitioned' GROUP BY "RegionID", "UserAgent", "OS" ORDER BY a_start, a_end DESC; +----------+-----------+-----+------------------------------------------------+------------------------------------------------+ | RegionID | UserAgent | OS | a_start | a_end | +----------+-----------+-----+------------------------------------------------+------------------------------------------------+ | 31090 | 3 | 44 | -20 days -6 hours -18 mins -27.181355464 secs | -20 days -6 hours -18 mins -27.181355464 secs | | 84213 | 7 | 44 | -18 days -21 hours -48 mins -6.036609637 secs | -18 days -21 hours -48 mins -6.036609637 secs | | 31090 | 32 | 159 | -17 days -13 hours -35 mins -8.314443507 secs | -17 days -13 hours -35 mins -8.314443507 secs | | 101 | 32 | 159 | -17 days -10 hours -6 mins -38.999603522 secs | -17 days -10 hours -6 mins -38.999603522 secs | | 184 | 5 | 77 | -16 days -20 hours -43 mins -7.548815552 secs | -16 days -20 hours -43 mins -7.548815552 secs | | 8389 | 2 | 2 | -16 days -14 hours -23 mins -37.047327426 secs | -16 days -14 hours -23 mins -37.047327426 secs | | 15748 | 5 | 44 | -16 days -9 hours -38 mins -52.619671823 secs | -16 days -9 hours -38 mins -52.619671823 secs | | 220 | 88 | 44 | -16 days -5 hours -32 mins -17.133638324 secs | -16 days -5 hours -32 mins -17.133638324 secs | | 84213 | 29 | 44 | -16 days -4 hours -39 mins -53.722202896 secs | -16 days -4 hours -39 mins -53.722202896 secs | | 135 | 88 | 2 | -16 days -3 hours -35 mins -20.175963897 secs | -16 days -3 hours -35 mins -20.175963897 secs | | 166 | 5 | 126 | -16 days -3 hours -28 mins -4.114895438 secs | -16 days -3 hours -28 mins -4.114895438 secs | | 14496 | 2 | 44 | -16 days -2 hours -33 mins -24.030979246 secs | -16 days -2 hours -33 mins -24.030979246 secs | | 9723 | 3 | 44 | -15 days -22 hours -25 mins -10.918350813 secs | -15 days -22 hours -25 mins -10.918350813 secs | | 30367 | 3 | 44 | -15 days -17 hours -1 mins -36.068866689 secs | -15 days -17 hours -1 mins -36.068866689 secs | | 616 | 7 | 44 | -15 days -11 hours -54 mins -44.193933287 secs | -15 days -11 hours -54 mins -44.193933287 secs | | 548 | 3 | 2 | -15 days -9 hours -38 mins -36.282580625 secs | -15 days -9 hours -38 mins -36.282580625 secs | | 169 | 3 | 9 | -15 days -9 hours -34 mins -16.609244836 secs | -15 days -9 hours -34 mins -16.609244836 secs | | 30367 | 5 | 2 | -15 days -9 hours -25 mins -47.249590242 secs | -15 days -9 hours -25 mins -47.249590242 secs | | 548 | 2 | 2 | -15 days -6 hours -50 mins -58.724296054 secs | -15 days -6 hours -50 mins -58.724296054 secs | | 12178 | 5 | 2 | -15 days -6 hours -10 mins -15.636589162 secs | -15 days -6 hours -10 mins -15.636589162 secs | | 16308 | 3 | 2 | -15 days -5 hours -4 mins -5.694121528 secs | -15 days -5 hours -4 mins -5.694121528 secs | | 8264 | 3 | 2 | -15 days -5 hours -2 mins -30.789153859 secs | -15 days -5 hours -2 mins -30.789153859 secs | | 10997 | 2 | 44 | -15 days -3 hours -24 mins -10.717868021 secs | -15 days -3 hours -24 mins -10.717868021 secs | | 169 | 88 | 126 | -15 days -3 hours -1 mins -52.667383961 secs | -15 days -3 hours -1 mins -52.667383961 secs | | 27466 | 2 | 44 | -15 days -2 hours -21 mins -8.239206896 secs | -15 days -2 hours -21 mins -8.239206896 secs | | 51 | 2 | 126 | -15 days -1 hours -30 mins -47.897427363 secs | -15 days -1 hours -30 mins -47.897427363 secs | | 26297 | 5 | 2 | -14 days -18 hours -52 mins -40.572961901 secs | -14 days -18 hours -52 mins -40.572961901 secs | | 51 | 82 | 74 | -14 days -17 hours -31 mins -6.159516906 secs | -14 days -17 hours -31 mins -6.159516906 secs | | 226 | 7 | 126 | -14 days -17 hours -30 mins -12.258864200 secs | -14 days -17 hours -30 mins -12.258864200 secs | | 15332 | 3 | 2 | -14 days -13 hours -45 mins -21.384163359 secs | -14 days -13 hours -45 mins -21.384163359 secs | | 712 | 3 | 44 | -14 days -12 hours -27 mins -58.241500454 secs | -14 days -12 hours -27 mins -58.241500454 secs | | 24187 | 2 | 44 | -14 days -12 hours -5 mins -43.206212570 secs | -14 days -12 hours -5 mins -43.206212570 secs | | 169 | 56 | 44 | -14 days -9 hours -48 mins -26.366752235 secs | -14 days -9 hours -48 mins -26.366752235 secs | | 12 | 88 | 2 | -14 days -6 hours -2 mins -31.949143066 secs | -14 days -6 hours -2 mins -31.949143066 secs | | 14966 | 3 | 44 | -14 days -2 hours -51 mins -56.613919863 secs | -14 days -2 hours -51 mins -56.613919863 secs | | 53 | 88 | 44 | -13 days -23 hours -42 mins -29.324289608 secs | -13 days -23 hours -42 mins -29.324289608 secs | | 247 | 88 | 2 | -13 days -23 hours -34 mins -24.710721929 secs | -13 days -23 hours -34 mins -24.710721929 secs | | 54 | 5 | 74 | -13 days -21 hours -56 mins -5.209142987 secs | -13 days -21 hours -56 mins -5.209142987 secs | | 9955 | 5 | 44 | -13 days -19 hours -51 mins -10.431498276 secs | -13 days -19 hours -51 mins -10.431498276 secs | | 34 | 3 | 126 | -13 days -19 hours -27 mins -45.587965629 secs | -13 days -19 hours -27 mins -45.587965629 secs | | . | | . | | . | +----------+-----------+-----+------------------------------------------------+------------------------------------------------+ 138826 row(s) fetched. (First 40 displayed. Use --maxrows to adjust) Elapsed 0.507 seconds. ``` ### 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-unsubscr...@datafusion.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
