calling procedures is slow and consumes extra much memory against calling function

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: calling procedures is slow and consumes extra much memory against calling function
Date: 2020-05-10 20:20:53
Message-ID: CAFj8pRDiiU1dqym+_P4_GuTWm76knJu7z9opWayBJTC0nQGUUA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira 2020-05-10 21:10:40 deferred primary key and logical replication
Previous Message Justin Pryzby 2020-05-10 20:09:47 Re: PG 13 release notes, first draft (ltree dot star)