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

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

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