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

Hello

2013/10/1 <m(dot)manso(at)upm(dot)es>

> The following bug has been logged on the website:
>
> Bug reference: 8495
> Logged by: Miguel A. Manso Callejo
> Email address: m(dot)manso(at)upm(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>