Hello a very ugly test shows a possibility about 100% speedup on reported example (on small arrays, a patch is buggy and doesn't work for larger arrays).
I updated a code to be read only
CREATE OR REPLACE FUNCTION public.fill_2d_array(rows integer, cols integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
img double precision[][];
i integer; j integer;
cont integer; r double precision;
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 r := img[i * cols + j];
r := (i * cols + j)::double precision;
cont := cont + 1; --raise notice '%', img;
END LOOP;
END LOOP;
return cont;
END;
$function$
It exec all expressions
-- original
postgres=# select fill_2d_array(200,200);
fill_2d_array
---------------
40000
(1 row)
Time: 12726.117 ms
-- read only version
postgres=# select fill_2d_array(200,200); fill_2d_array
---------------
40000
(1 row)
Time: 245.894 ms
so there is about 50x slowdown
2013/10/3 Pavel Stehule <pavel.steh...@gmail.com>
>
>
>
> 2013/10/3 Tom Lane <t...@sss.pgh.pa.us>
>
>> Pavel Stehule <pavel.steh...@gmail.com> writes:
>> > If you can do a update of some array in plpgsql now, then you have to
>> work
>> > with local copy only. It is a necessary precondition, and I am think it
>> is
>> > valid.
>>
>> If the proposal only relates to assignments to elements of plpgsql local
>> variables, it's probably safe, but it's also probably not of much value.
>> plpgsql has enough overhead that I'm doubting you'd get much real-world
>> speedup. I'm also not very excited about putting even more low-level
>> knowledge about array representation into plpgsql.
>>
>
> I looked to code, and I am thinking so this can be done inside array
> related routines. We just have to signalize request for inplace update (if
> we have a local copy).
>
> I have not idea, how significant speedup can be (if any), but current
> behave is not friendly (and for multidimensional arrays there are no
> workaround), so it is interesting way - and long time I though about some
> similar optimization.
>
> Regards
>
> Pavel
>
>
>>
>> regards, tom lane
>>
>
>
fast_array_update.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
