Thursday, December 24, 2015

FORTRAN 90 like vector operations in PostgreSQL revisited

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 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.


  1. Have you reported this as a bug on github?

    1. Not yet, but it got noticed and fixed in the meantime.

  2. Thanks 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).

    1. Thank 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.