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