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

From Pavel Stehule
Subject Re: BUG #8495: PLSQL PG Terrible performance by using two-dimensional arrays.
Date
Msg-id CAFj8pRB+ruxJnWybAm-U=Und0V86-QtBFP9v2=CeoG=xiU3x7w@mail.gmail.com
Whole thread Raw
In response to BUG #8495: PLSQL PG Terrible performance by using two-dimensional arrays.  (m.manso@upm.es)
List pgsql-bugs
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
>

pgsql-bugs by date:

Previous
From: m.manso@upm.es
Date:
Subject: BUG #8495: PLSQL PG Terrible performance by using two-dimensional arrays.
Next
From: Mark Kirkwood
Date:
Subject: Re: BUG #8494: Cannot Backup / Restore