pá 30. 7. 2021 v 10:04 odesílatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> Hi > > pá 30. 7. 2021 v 10:02 odesílatel Imre Samu <pella.s...@gmail.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.westerm...@dbi-services.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 >>> >>>
