Re: efficient math vector operations on arrays

On 12/29/15 6:50 PM, Tom Lane wrote:
> Jim Nasby<Jim.Nasby@BlueTreble.com>  writes:
>> >BTW, if you want to simply apply a function to all elements in an array
>> >there is an internal C function array_map that can do it. There's no SQL
>> >interface to it, but it shouldn't be hard to add one.
> That wouldn't be useful for the example given originally, since it
> iterates over just one array not two arrays in parallel.  But you could
> imagine writing something similar that would iterate over two arrays and
> call a two-argument function.

Actually, I suspect you could pretty easily do array_map(regprocedure,
VARIADIC anyarray).

> Whether it's worth a SQL interface is debatable though.  Whatever
> efficiency you might gain from using this would probably be eaten by the
> overhead of calling a SQL or PL function for each pair of array elements.
> You'd probably end up in the same ballpark performance-wise as the UNNEST
> solution given earlier.

Take a look at [1]; using a rough equivalent to array_map is 6% faster
than unnest().

The array op array version is 30% faster that plpgsql, which based on
the code at [2] I assume is doing

  explain analyze select array(select a*b from unnest(array(select
random() from generate_series(1,1000000)), array(select random() from
generate_series(1,1000000)))) u(a,b);

The syntactic sugar of r := array_map('function(a, b)', in1, in2) (let
alone r := in1 * in2;) is appealing too.

[1]
http://theplateisbad.blogspot.com/2015/12/the-arraymath-extension-vs-plpgsql.html
[2]
http://theplateisbad.blogspot.com/2015/12/more-fortran-90-like-vector-operations.html
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com