Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> Is there a way we can improve unnest() and array_agg() to match the
> performance you have specified by let's say optimizing the cases
> specially when those two are used together. Identifying that may be
> some work, but will not require introducing new syntax.
+1. The first thing I thought on seeing this proposal was "I wonder
how long it will be before the SQL committee introduces some syntax
that uses the MAP keyword and breaks this".
ISTM the planner could be taught to notice the combination of unnest
and array_agg and produce a special output plan from that.
It is, however, fair to wonder whether this is worth our time to
optimize. I've not noticed a lot of people complaining about
performance of this sort of thing, at least not since we fixed
array_agg to not be O(N^2).
regards, tom lane