On Tue, 14 Mar 2023 at 16:07, Ben Clements <benhasgonewalking@gmail.com> wrote:
> Similar to your "TOP() and BOTTOM() aggregate" idea, you might find Erwin Brandstetter's solution using the LAST()
aggregatefunction interesting: (https://dba.stackexchange.com/a/324646/100880)
Interesting. Just note that ORDER BY aggregates cannot be
parallelised and there are no shortcuts to just look for the highest /
lowest ordered row. All rows in the group must be sorted and the
aggregate will just take the first or last of those once the sort is
done. The difference there (unless using PG16 and an index provides
presorted input) is that there would be O(N log2 N) comparisons to
perform the sort, where as the TOP() / BOTTOM() idea both allows
parallelism and requires less memory and only requires O(N)
comparisons.
If performance is not too critical row now, then what you've found
looks great. I just wanted to mention that as it may be a factor that
matters at some point, even if it does not right now.
David