Re: Performance issue with thousands of calls to procedures and functions?

Hi

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) <
daniel(dot)westermann(at)dbi-services(dot)com> napsal:

> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>

> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>

Unfortunately yes, it is possible. PL/pgSQL is interpreted language without
**any** compiler optimization. PL/SQL is now a fully compiled language with
a lot of compiler optimization. There is main overhead with repeated
function's initialization and variable's initialization. Your example is
the worst case for PL/pgSQL - and I am surprised so the difference is only
3-4x.

Maybe (probably) Oracle does inlining of f1 function. You can get the same
effect if you use SQL language for this function. PL/pgSQL is bad language
for one line functions. When I did it, then then I got 34 sec (on my comp
against 272 sec)

and mark this function as immutable helps a lot of too - it takes 34 sec on
my computer.

Regards

Pavel

> Thanks for any inputs
> Regards
> Daniel
>
>