Horton

Re: plan cache overhead on plpgsql expression

Pavel Stehule <pavel.stehule@gmail.com>

View thread View on postgr.es
From: Pavel Stehule <pavel.stehule@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-02-16T16:00:35Z
Lists: pgsql-hackers 
ne 16. 2. 2020 v 15:12 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi
>
> when I do some profiling of plpgsql, usually I surprised how significant
> overhead has expression execution. Any calculations are very slow.
>
> This is not typical example of plpgsql, but it shows cleanly where is a
> overhead
>
> CREATE OR REPLACE FUNCTION public.foo()
>  RETURNS void
>  LANGUAGE plpgsql
>  IMMUTABLE
> AS $function$
> declare i bigint = 0;
> begin
>   while i < 100000000
>   loop
>     i := i + 1;
>   end loop;
> end;
> $function$
>
>
> Is interesting so overhead of plan cache about 15%
>
> The execution needs 32 sec on Postgres13 and 27sec on Postgres8.2
>

On same computer same example in Perl needs only 7 sec.

Regards

Pavel


> Regards
>
> Pavel
>
>

Commits

  1. Rearrange validity checks for plpgsql "simple" expressions.

  2. Improve performance of "simple expressions" in PL/pgSQL.

  3. Ensure that plpgsql cleans up cleanly during parallel-worker exit.