Re: calling procedures is slow and consumes extra much memory againstcalling function

so 16. 5. 2020 v 5:55 odesílatel Ranier Vilela <ranier.vf@gmail.com> napsal:

Em sáb., 16 de mai. de 2020 às 00:07, Pavel Stehule <pavel.stehule@gmail.com> escreveu:

so 16. 5. 2020 v 0:34 odesílatel Ranier Vilela <ranier.vf@gmail.com> napsal:

Em dom., 10 de mai. de 2020 às 17:21, Pavel Stehule <pavel.stehule@gmail.com> escreveu:

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.

I am having a consistent result of 3 secs, with a modified version (exec_stmt_call) of your patch.
But my notebook is (Core 5, 8GB and SSD), could it be a difference in the testing hardware?

My notebook is old T520, and more I have a configured Postgres with --enable-cassert option.

The hardware is definitely making a difference, but if you have time and don't mind testing it,

I can send you a patch, not that the modifications are a big deal, but maybe they'll help.

With more testing, I found that latency increases response time.
With 3 (secs) the test is with localhost.
With 6 (secs) the test is with tcp (local, not between pcs).

Anyway, I would like to know if we have the number of parameters previously, why use List instead of Arrays?
It would not be faster to create plpgsql variables.

regards,

Ranier Vilela