Re: ToDo: fast update of arrays with fixed length fields for PL/pgSQL
| От | Pavel Stehule |
|---|---|
| Тема | Re: ToDo: fast update of arrays with fixed length fields for PL/pgSQL |
| Дата | |
| Msg-id | CAFj8pRAGAyCUZq+5c7-pUg3Pew-kGcuHBG=52-Tafs39ZgN1NQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: ToDo: fast update of arrays with fixed length fields for PL/pgSQL (Pavel Stehule <pavel.stehule@gmail.com>) |
| Ответы |
Re: ToDo: fast update of arrays with fixed length fields
for PL/pgSQL
|
| Список | pgsql-hackers |
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
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
---------------
40000
(1 row)
Time: 245.894 ms
so there is about 50x slowdown
2013/10/3 Pavel Stehule <pavel.stehule@gmail.com>
2013/10/3 Tom Lane <tgl@sss.pgh.pa.us>Pavel Stehule <pavel.stehule@gmail.com> writes:If the proposal only relates to assignments to elements of plpgsql local
> 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.
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
Вложения
В списке pgsql-hackers по дате отправления: