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

Hi

pá 30. 7. 2021 v 10:02 odesílatel Imre Samu <pella(dot)samu(at)gmail(dot)com> napsal:

> Hi Daniel,
>
> side note:
>
> Maybe you can tune the "function" with some special query optimizer
> attributes:
> IMMUTABLE | STABLE | VOLATILE | PARALLEL SAFE
>
> so in your example:
> create or replace function f1(int) returns double precision as
>
> $$
> declare
> begin
> return 1;
> end;
> $$ language plpgsql *IMMUTABLE PARALLEL SAFE*;
>
>
It cannot help in this case. PL/pgSQL routine (and expression calculations)
is one CPU every time.

Regards

Pavel

>
> """ : https://www.postgresql.org/docs/13/sql-createfunction.html
> PARALLEL SAFE :
> * indicates that the function is safe to run in parallel mode without
> restriction.*
> IMMUTABLE *: indicates that the function cannot modify the database and
> always returns the same result when given the same argument values; that
> is, it does not do database lookups or otherwise use information not
> directly present in its argument list. If this option is given, any call of
> the function with all-constant arguments can be immediately replaced with
> the function value.*
> """
>
> Regards,
> Imre
>
> Daniel Westermann (DWE) <daniel(dot)westermann(at)dbi-services(dot)com> ezt írta
> (időpont: 2021. júl. 30., P, 9:12):
>
>> 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.
>>
>> Thanks for any inputs
>> Regards
>> Daniel
>>
>>