I tried to compare the arraymath extension suggested in a comment with my plpgsql solution from the last post.
PostgreSQL was 9.5rc1 on Ubuntu 14.04 LTS.
make
make install
create extension arraymath;
No problems so far, but...
select array(select random() from generate_series(1,1000000)) @+ array(select random() from generate_series(1,1000000));
NOTICE: Hello from ArrayMath 1.0
********** Error **********
Server crash!
Whereas the plpgsql code does the addition of two one million element vectors in 1.2 seconds.
Interestingly this works:
SELECT ARRAY[1,2,3,4,5,6,7,8] @+ ARRAY[1,2,3,4,5,6,7,8];
While this crashes:
SELECT ARRAY[1,2,3,4,5,6,7,8,9] @+ ARRAY[1,2,3,4,5,6,7,8,9];
So arraymath seems to fail with vectors larger than 8 elements. Probably only on 9.5, but I couldn't get it to work properly.
Have you reported this as a bug on github?
ReplyDeleteNot yet, but it got noticed and fixed in the meantime.
DeleteThanks for finding the crash, it's fixed in master now. I time your 1M length array test at about 2.4 seconds on my 1.7Ghz MacBook Air. The speed of the pl/pgsql code is impressive, I guess once one is unrolling arrays it's all "kind of native" mostly anyways. I may try changing to array iterators and see if that makes any difference (doubtful).
ReplyDeleteThank you, I'll check the timings here too. IMHO the main advantage of the C functions is that they can be vectorized or threaded. However, the additional overhead may justify this only for very large vectors.
Delete