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

pá 30. 7. 2021 v 10:04 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> 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.
>

IMMUTABLE helps, surely, because it is translated to constant in this case.

Regards

Pavel

> 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
>>>
>>>