Re: ToDo: fast update of arrays with fixed length fields for PL/pgSQL

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(dot)stehule(at)gmail(dot)com>

>
>
>
> 2013/10/3 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>>
>
>