|From:||Ildar Musin <i(dot)musin(at)postgrespro(dot)ru>|
|To:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>|
|Cc:||Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: MAP syntax for arrays|
|Views:||Raw Message | Whole Thread | Download mbox|
Hello Tom, Ashutosh,
On 07.05.2018 18:16, Tom Lane wrote:
> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)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;
select map(x * 2 for x in array[[1,2],[3,4],[5,6]]);
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]);
|Next Message||Ildar Musin||2018-05-08 12:57:14||Re: MAP syntax for arrays|
|Previous Message||David Steele||2018-05-08 12:31:12||Re: perlcritic and perltidy|