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


4 comments:

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

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

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

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

      Delete