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

[Pavel Stehule]

Hi

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

diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index aeb6c8fefc..bb08037a94 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2309,13 +2309,19 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
 		 * could have been unset already, in case of a recursive call.
 		 */
 		if (expr->plan && !expr->plan->saved)
+		{
+			SPI_freeplan(expr->plan);
 			expr->plan = NULL;
+		}
 		PG_RE_THROW();
 	}
 	PG_END_TRY();
 
 	if (expr->plan && !expr->plan->saved)
+	{
+		SPI_freeplan(expr->plan);
 		expr->plan = NULL;
+	}
 
 	if (rc < 0)
 		elog(ERROR, "SPI_execute_plan_with_paramlist failed executing query \"%s\": %s",