Re: range_agg extremely slow compared to naive implementation in obscure circumstances

On Mon, 30 Jan 2023 at 23:43, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Maybe there is some problem in range_deserialize function

It seems to be that range_deserialize is called from within
range_compare which is the qsort comparison function (see
multirange_canonicalize). That'll end up calling range_deserialize
twice, once for each item being compared about O(N log N) times.

Ordinarily, this probably isn't too bad as we only do this in the
aggregate's final function.  It's likely the performance is bad here
as the aggregate is being used as a window function and the finalfn
must be called once for each row rather than once per group as it
would if it was being used as a normal aggregate function.

It might be better if we had multirange_canonicalize() deserialize
these once and used some representation that could more easily be
qsorted. I'm not planning on doing any work on it though.

It's probably unlikely that we'd do anything about this as part of a bug fix.

David