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

Hi

ne 10. 5. 2020 v 22:20 odesílatel Pavel Stehule <pavel.stehule@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.

Regards

Pavel

Regards

Pavel