Add Extended Clickbench benchmark for avg(duration)

[alamb]

Is your feature request related to a problem or challenge?

• Part of Support AVG for durations #15458

In #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

> 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

• Add extended query for checking improvement for blocked groups optimization #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:

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

[shruti2522]

take

[alamb]

BTW on my machine, the query above runs on main in

Elapsed 0.478 seconds.
Elapsed 0.473 seconds.

And using #15748 it runs in

Elapsed 0.306 seconds.
Elapsed 0.309 seconds.

very nice 👏

[shruti2522]

BTW on my machine, the query above runs on main in
very nice 👏

Awesome! I’m running the benchmark on my side too—will add the commit soon 🚀

[alamb]

@logan-keede perhaps this is a ticket you have interest in helping finish (and then we can get #15748 over the line)

[logan-keede]

SELECT a.*, b.avg_response_time from (SELECT
  "OS",
  AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as avg_latency 
FROM
  'data/hits_partitioned'
GROUP BY
  "OS"
ORDER BY
avg_latency DESC limit 10) as a INNER JOIN (SELECT
  "OS",
  AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming")) as avg_response_time 
FROM
  'data/hits_partitioned'
GROUP BY
  "OS"
ORDER BY
avg_response_time DESC limit 10) as b ON a."OS"=b."OS";

With business question: What OS are in top 10 worst performers in terms of both average response time and avg latency?
Results on my PC:

+-----+------------------------------------------+------------------------------------------+
| OS  | avg_latency                              | avg_response_time                        |
+-----+------------------------------------------+------------------------------------------+
| 103 | 0 days 0 hours 13 mins 47.509466437 secs | 0 days 0 hours 13 mins 47.509466437 secs |
| 49  | 0 days 0 hours 13 mins 42.811185779 secs | 0 days 0 hours 13 mins 9.107811258 secs  |
| 74  | 0 days 0 hours 13 mins 47.184892702 secs | 0 days 0 hours 12 mins 51.287431013 secs |
| 77  | 0 days 0 hours 11 mins 26.796840642 secs | 0 days 0 hours 9 mins 47.668864464 secs  |
| 112 | 0 days 0 hours 8 mins 32.958042116 secs  | 0 days 0 hours 3 mins 19.018355160 secs  |
| 39  | 0 days 0 hours 3 mins 1.060289083 secs   | 0 days 0 hours 2 mins 11.248827183 secs  |
| 70  | 0 days 0 hours 8 mins 8.089117199 secs   | 0 days 0 hours 1 mins 31.113089802 secs  |
+-----+------------------------------------------+------------------------------------------+
7 row(s) fetched. 
Elapsed 19.330 seconds.

or if we want to avoid subqueries or joins:-

SELECT
  "OS",
  AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming")) as avg_response_time,AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as avg_latency 
FROM
  'data/hits_partitioned'
GROUP BY
  "OS"
ORDER BY
avg_latency DESC limit 10

@alamb what do you think about this?

[alamb]

or if we want to avoid subqueries or joins:-

I think we should avoid the joins. The second query looks good ❤

[logan-keede]

or if we want to avoid subqueries or joins:-

I think we should avoid the joins. The second query looks good ❤

Alright, I will make a PR shortly.

[alamb]

I noticed the above query has only 91 groups:

DataFusion CLI v47.0.0
> SELECT
  "OS",
  AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming")) as avg_response_time,AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as avg_latency
FROM
  'data/hits_partitioned'
GROUP BY
  "OS"
ORDER BY
avg_latency DESC;
+-----+-------------------------------------------+------------------------------------------+
| OS  | avg_response_time                         | avg_latency                              |
+-----+-------------------------------------------+------------------------------------------+
| 103 | 0 days 0 hours 13 mins 47.509466437 secs  | 0 days 0 hours 13 mins 47.509466437 secs |
| 74  | 0 days 0 hours 12 mins 51.287431013 secs  | 0 days 0 hours 13 mins 47.184892702 secs |
| 49  | 0 days 0 hours 13 mins 9.107811258 secs   | 0 days 0 hours 13 mins 42.811185779 secs |
| 77  | 0 days 0 hours 9 mins 47.668864464 secs   | 0 days 0 hours 11 mins 26.796840642 secs |
| 112 | 0 days 0 hours 3 mins 19.018355160 secs   | 0 days 0 hours 8 mins 32.958042116 secs  |
| 70  | 0 days 0 hours 1 mins 31.113089802 secs   | 0 days 0 hours 8 mins 8.089117199 secs   |
| 59  | 0 days 0 hours 0 mins -59.687500000 secs  | 0 days 0 hours 4 mins 44.562500000 secs  |
| 101 | 0 days 0 hours 0 mins 27.485986881 secs   | 0 days 0 hours 4 mins 24.220234545 secs  |
| 12  | 0 days 0 hours 0 mins 0.915553578 secs    | 0 days 0 hours 3 mins 21.616990075 secs  |
| 39  | 0 days 0 hours 2 mins 11.248827183 secs   | 0 days 0 hours 3 mins 1.060289083 secs   |
| 46  | 0 days 0 hours 2 mins 51.602040816 secs   | 0 days 0 hours 2 mins 51.602040816 secs  |
| 104 | 0 days 0 hours -1 mins -41.261186264 secs | 0 days 0 hours 2 mins 7.463059313 secs   |
| 45  | 0 days 0 hours 0 mins 31.872238129 secs   | 0 days 0 hours 1 mins 37.324755565 secs  |
| 7   | 0 days 0 hours -2 mins -38.098571146 secs | 0 days 0 hours 1 mins 35.758543491 secs  |
| 126 | 0 days 0 hours -2 mins -25.507751501 secs | 0 days 0 hours 1 mins 32.933894412 secs  |
| 33  | 0 days 0 hours -2 mins -11.200000000 secs | 0 days 0 hours 1 mins 31.200000000 secs  |
| 9   | 0 days 0 hours -3 mins -53.293441768 secs | 0 days 0 hours 1 mins 28.609912908 secs  |
| 11  | 0 days 0 hours -3 mins -48.205086798 secs | 0 days 0 hours 1 mins 23.773112636 secs  |
| 44  | 0 days 0 hours -2 mins -30.470355300 secs | 0 days 0 hours 1 mins 22.076928357 secs  |
| 1   | 0 days 0 hours -2 mins -22.108601400 secs | 0 days 0 hours 1 mins 16.524448796 secs  |
| 2   | 0 days 0 hours -2 mins -10.731476126 secs | 0 days 0 hours 1 mins 16.255794248 secs  |
| 37  | 0 days 0 hours -1 mins -4.520792848 secs  | 0 days 0 hours 1 mins 7.226195102 secs   |
| 116 | 0 days 0 hours 0 mins 32.117647058 secs   | 0 days 0 hours 1 mins 6.509803921 secs   |
| 0   | 0 days 0 hours 0 mins 29.708039353 secs   | 0 days 0 hours 0 mins 58.786563598 secs  |
| 57  | 0 days 0 hours 0 mins -54.983173266 secs  | 0 days 0 hours 0 mins 56.604541731 secs  |
| 3   | 0 days 0 hours -1 mins -57.406106095 secs | 0 days 0 hours 0 mins 54.878138590 secs  |
| 5   | 0 days 0 hours -1 mins -14.716715226 secs | 0 days 0 hours 0 mins 46.498521635 secs  |
| 68  | 0 days 0 hours -1 mins -41.598033744 secs | 0 days 0 hours 0 mins 41.615774557 secs  |
| 67  | 0 days 0 hours 0 mins 27.802898817 secs   | 0 days 0 hours 0 mins 37.835699842 secs  |
| 38  | 0 days 0 hours -1 mins -0.347826086 secs  | 0 days 0 hours 0 mins 36.797101449 secs  |
| 98  | 0 days 0 hours -1 mins -25.621956238 secs | 0 days 0 hours 0 mins 36.477770676 secs  |
| 34  | 0 days 0 hours -1 mins -18.701492537 secs | 0 days 0 hours 0 mins 27.402985074 secs  |
| 35  | 0 days 0 hours 0 mins -35.877821173 secs  | 0 days 0 hours 0 mins 21.667386721 secs  |
| 17  | 0 days 0 hours -1 mins -38.074618882 secs | 0 days 0 hours 0 mins 17.008558438 secs  |
| 58  | 0 days 0 hours -3 mins -54.294117647 secs | 0 days 0 hours 0 mins 16.588235294 secs  |
| 54  | 0 days 0 hours 0 mins -53.554054054 secs  | 0 days 0 hours 0 mins 16.304054054 secs  |
| 14  | 0 days 0 hours -3 mins -2.845982142 secs  | 0 days 0 hours 0 mins 14.861607142 secs  |
| 13  | 0 days 0 hours -1 mins -6.324333862 secs  | 0 days 0 hours 0 mins 13.454561496 secs  |
| 10  | 0 days 0 hours 0 mins 8.780079478 secs    | 0 days 0 hours 0 mins 9.103530175 secs   |
| 71  | 0 days 0 hours 0 mins 7.617299688 secs    | 0 days 0 hours 0 mins 7.712214154 secs   |
| .                                                                                          |
| .                                                                                          |
| .                                                                                          |
+-----+-------------------------------------------+------------------------------------------+
91 row(s) fetched. (First 40 displayed. Use --maxrows to adjust)
Elapsed 0.224 seconds.

I recommend we also add other columns to the GROUP BY to increase the number of groups / challenge of the aggregate? That could also be a second query potentially

GROUP BY
  "RegionID",
  "UserAgent",
  "OS"