From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jony Cohen <jony(dot)cohenjo(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Marcus Engene <mengpg2(at)engene(dot)se>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: efficient math vector operations on arrays |
Date: | 2015-12-30 01:26:36 |
Message-ID: | 568332CC.6040008@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/29/15 6:50 PM, Tom Lane wrote:
> Jim Nasby<Jim(dot)Nasby(at)BlueTreble(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Killian Driscoll | 2015-12-30 07:38:38 | Re: Transfer db from one port to another |
Previous Message | David Rowley | 2015-12-30 01:15:37 | Re: grep -f keyword data query |