Hello Tom, Ashutosh,
On 07.05.2018 18:16, Tom Lane wrote:
> 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).
The main point of this patch was about convenience; the performance
thing came out later just as a side effect :) Many users are familiar
with "map/reduce/filter" concept that many languages (not only
functional ones) utilized. And my though was that it would be great to
have those in postgres too.
Apparently there is also a case when unnest/array_agg may not produce
the result we're looking for. I mean multidimensional arrays. E.g.
select array_agg(x * 2)
from unnest(array[[1,2],[3,4],[5,6]]) as x;
array_agg
-----------------
{2,4,6,8,10,12}
(1 row)
select map(x * 2 for x in array[[1,2],[3,4],[5,6]]);
?column?
-----------------------
{{2,4},{6,8},{10,12}}
(1 row)
array_agg produces plain arrays no matter what the input was.
There is a new version of the patch in the attachment which introduces
arbitrary per-element expressions (instead of single function call). So
now user can specify a placeholder representing one element of the array
and use it in the expressions. Like following:
select map (pow(x, 2) - 1 for x in array[1,2,3]);
?column?
---------------
{1,3,7,15,31}
(1 row)
--
Ildar Musin
i.musin@postgrespro.ru
