From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
Subject: | Re: calling procedures is slow and consumes extra much memory against calling function |
Date: | 2020-05-11 06:07:48 |
Message-ID: | CAFj8pRDLRin+3Ge-WnuhyvpJ_T1iEST+TckmVLOZoceQeQMxJQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
po 11. 5. 2020 v 7:25 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:
> Hi
>
> ne 10. 5. 2020 v 22:20 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2020-05-11 06:19:34 | Re: should INSERT SELECT use a BulkInsertState? |
Previous Message | Michael Paquier | 2020-05-11 06:07:34 | Re: calling procedures is slow and consumes extra much memory against calling function |