Re: MAP syntax for arrays

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
Date: 2018-05-08 12:49:28
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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;
(1 row)

select map(x * 2 for x in array[[1,2],[3,4],[5,6]]);
(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]);
(1 row)

Ildar Musin

Attachment Content-Type Size
map_v2.patch text/x-diff 20.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
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