po 11. 5. 2020 v 7:25 odesÃlatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> Hi > > ne 10. 5. 2020 v 22:20 odesÃlatel Pavel Stehule <pavel.steh...@gmail.com> > napsal: > >> Hi >> >> I try to use procedures in Orafce package, and I did some easy >> performance tests. I found some hard problems: >> >> 1. test case >> >> create or replace procedure p1(inout r int, inout v int) as $$ >> begin v := random() * r; end >> $$ language plpgsql; >> >> This command requires >> >> do $$ >> declare r int default 100; x int; >> begin >> for i in 1..300000 loop >> call p1(r, x); >> end loop; >> end; >> $$; >> >> about 2.2GB RAM and 10 sec. >> >> When I rewrite same to functions then >> >> create or replace function p1func2(inout r int, inout v int) as $$ >> begin v := random() * r; end >> $$ language plpgsql; >> >> do $$ >> declare r int default 100; x int; re record; >> begin >> for i in 1..300000 loop >> re := p1func2(r, x); >> end loop; >> end; >> $$; >> >> Then execution is about 1 sec, and memory requirements are +/- zero. >> >> Minimally it looks so CALL statements has a memory issue. >> > > The problem is in plpgsql implementation of CALL statement > > In non atomic case - case of using procedures from DO block, the > expression plan is not cached, and plan is generating any time. This is > reason why it is slow. > > Unfortunately, generated plans are not released until SPI_finish. Attached > patch fixed this issue. > But now, recursive calling doesn't work :-(. So this patch is not enough > Regards > > Pavel > > >> Regards >> >> Pavel >> >>
