Performance issue with thousands of calls to procedures and functions?
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 _test_case.sql Description: _test_case.sql
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) 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 > >
Re: Performance issue with thousands of calls to procedures and functions?
Hi pá 30. 7. 2021 v 10:02 odesílatel Imre Samu 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) 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 >> >>
Re: Performance issue with thousands of calls to procedures and functions?
Hi pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < [email protected]> 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 > >
Re: Performance issue with thousands of calls to procedures and functions?
pá 30. 7. 2021 v 10:04 odesílatel Pavel Stehule napsal: > Hi > > pá 30. 7. 2021 v 10:02 odesílatel Imre Samu 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) 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 >>> >>>
Re: Performance issue with thousands of calls to procedures and functions?
pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) mailto:[email protected]>> 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. Thank you, Pavel. As far as I understand the docs, I cannot use immutable as the "real" functions and procedures do database lookups. Regards Daniel Thanks for any inputs Regards Daniel
Re: Performance issue with thousands of calls to procedures and functions?
pá 30. 7. 2021 v 10:12 odesílatel Daniel Westermann (DWE) < [email protected]> napsal: > > pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < > [email protected]> 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. > > Thank you, Pavel. As far as I understand the docs, I cannot use immutable > as the "real" functions and procedures do database lookups. > In your example, the bottleneck is calling the function f1. So you need to check only this function. It is not important if other functions or procedures do database lookups. Or if it does just one database lookup, then you can use SQL language. I repeat, PL/pgSQL is not good for ultra very frequent calls (where there is minimal other overhead). Generally, start of function or start of query are more expensive on Postgres than on Oracle. Postgres is much more dynamic, and it needs to do some rechecks. The overhead is in nanoseconds, but nanoseconds x billions are lot of seconds > Regards > Daniel > > > > Thanks for any inputs > Regards > Daniel > >
Re: Performance issue with thousands of calls to procedures and functions?
>In your example, the bottleneck is calling the function f1. So you need to >check only this function. It is not important if other functions or >>procedures do database lookups. >Or if it does just one database lookup, then you can use SQL language. I >repeat, PL/pgSQL is not good for ultra very frequent calls (where >there is >minimal other overhead). >Generally, start of function or start of query are more expensive on Postgres >than on Oracle. Postgres is much more dynamic, and it needs >to do some >rechecks. The overhead is in nanoseconds, but nanoseconds x billions are lot >of seconds Thank you Pavel, for all the information. That was very helpful. Regards Daniel
