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
>