Thread: BUG #8495: PLSQL PG Terrible performance by using two-dimensional arrays.

The following bug has been logged on the website:

Bug reference:      8495
Logged by:          Miguel A. Manso Callejo
Email address:      m.manso@upm.es
PostgreSQL version: 9.1.9
Operating system:   Ubuntu 12.04LTS
Description:

I'm trying to random access to a 2-dimensional array of double precision
numbers. When range of the array increase, the performance decrease
quickly.
A simple function as:
CREATE OR REPLACE FUNCTION fill_2d_array( rows integer, cols integer)
RETURNS integer AS
$BODY$
DECLARE
    img double precision[][];
    i integer; j integer;
    cont integer;
BEGIN
    img  := ARRAY( SELECT 0 FROM generate_series(1, rows * cols) ) ;
    cont:= 0;
    For i IN 1..rows LOOP
        For j IN 1..cols LOOP
            img[i * cols + j] := (i * cols + j)::double precision;
            cont := cont + 1;
        END LOOP;
    END LOOP;
    return cont;
END;
$BODY$
  LANGUAGE plpgsql;
ALTER FUNCTION fill_2d_array( integer, integer)
  OWNER TO postgres;


when call the function with 700 rows & 1200 cols (explain (analyze,buffers)
select fill_2d_array(700,1200); ) the time consumed is about 50minutes.


What is bad? what i'm doing bad?


Thank you very much.

Hello


2013/10/1 <m.manso@upm.es>

> The following bug has been logged on the website:
>
> Bug reference:      8495
> Logged by:          Miguel A. Manso Callejo
> Email address:      m.manso@upm.es
> PostgreSQL version: 9.1.9
> Operating system:   Ubuntu 12.04LTS
> Description:
>
> I'm trying to random access to a 2-dimensional array of double precision
> numbers. When range of the array increase, the performance decrease
> quickly.
> A simple function as:
> CREATE OR REPLACE FUNCTION fill_2d_array( rows integer, cols integer)
> RETURNS integer AS
> $BODY$
> DECLARE
>     img double precision[][];
>     i integer; j integer;
>     cont integer;
> BEGIN
>     img  := ARRAY( SELECT 0 FROM generate_series(1, rows * cols) ) ;
>     cont:= 0;
>     For i IN 1..rows LOOP
>             For j IN 1..cols LOOP
>                     img[i * cols + j] := (i * cols + j)::double precision;
>                     cont := cont + 1;
>             END LOOP;
>     END LOOP;
>     return cont;
> END;
> $BODY$
>   LANGUAGE plpgsql;
> ALTER FUNCTION fill_2d_array( integer, integer)
>   OWNER TO postgres;
>
>
> when call the function with 700 rows & 1200 cols (explain (analyze,buffers)
> select fill_2d_array(700,1200); ) the time consumed is about 50minutes.
>
>
> What is bad? what i'm doing bad?
>

update a large array is terrible slow due fact, so PostgreSQL arrays are
immutable - so any update is same as copy of array.

sometimes is better to generate table and build a array from table, but it
is not possible for two dimensional array without custom C extension :(

if you can, try to use PLPerl instead PL/pgSQL

You cannot do much more - for 1D arrays exists some tricks, but it doesn't
work for 2D arrays. You can write relative simply C extension, and solve
problem there.

Regards

Pavel Stehule





>
>
> Thank you very much.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>