freakyzoidberg opened a new issue, #27301:
URL: https://github.com/apache/datafusion/issues/27301
### Is your feature request related to a problem or challenge?
Hi,
I run into an unexpected performance hit on a production system with the use
of first/last_value when compared to the performance min/max.
I have a proposal for a perf improvement that I'll submit next, creating the
issue for reference and reproduction.
Thanks !
Grouped `first_value(x ORDER BY k)` / `last_value(x ORDER BY k)` is a lot
slower than a plain `max(x)` over the same groups, and it gets worse with many
groups. `min_by` / `max_by`, when rewritten to `last_value(x ORDER BY y)`, hit
the same path.
In `datafusion-cli` (M4 Max, 16 cores):
```sql
CREATE TABLE t AS
SELECT value % 5000000 AS g,
(value * value % 1009) * 50000000 + value AS k,
(value * value % 1013) * 50000000 + value AS x
FROM range(50000000);
-- 0.12s
SELECT count(*), sum(v) FROM (SELECT max(x) AS v FROM t GROUP BY g);
-- 0.32s
SELECT count(*), sum(v) FROM (SELECT last_value(x ORDER BY k) AS v FROM t
GROUP BY g);
-- 1.06s, four of them as min_by / max_by produce
SELECT count(*), sum(a), sum(b), sum(c), sum(d) FROM (
SELECT last_value(x ORDER BY k DESC NULLS LAST) AS a, last_value(x ORDER
BY k ASC NULLS LAST) AS b,
last_value(k ORDER BY x DESC NULLS LAST) AS c, last_value(k ORDER
BY x ASC NULLS LAST) AS d
FROM t GROUP BY g);
```
`FirstLastGroupsAccumulator` keeps each group's ordering as a
`Vec<ScalarValue>`. Every batch winner is converted to `ScalarValue`s and
compared with `compare_rows`, and every batch resets and scans a scoreboard
sized to all groups (#25050 avoided that for pre-ordered input only).
### Describe the solution you'd like
When there is a single ORDER BY expression of a primitive type, keep the
orderings as native values (`Vec<K::Native>` plus a null bitmap) in a dedicated
accumulator. It would be picked with `downcast_primitive!`, the way
`GroupValuesPrimitive` is for GROUP BY keys. Other orderings keep the current
accumulator.
With a prototype, the last two queries take about 0.12s and 0.26s, with the
same results.
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
### Willingness to contribute
I plan to contribute to this feature independently
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
