calling procedures is slow and consumes extra much memory against calling function
Pavel Stehule <pavel.stehule@gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-05-10T20:20:53Z
Lists: pgsql-hackers
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.
Regards
Pavel
Commits
-
Fix memory leak in plpgsql's CALL processing.
- c1e044bb30f2 12.5 landed
- 13a1901bad47 11.10 landed
- f0e4ec74e452 13.1 landed
- a6b1f5365d58 14.0 landed