Hi pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < daniel.westerm...@dbi-services.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 > >
