Hi
pá 10. 2. 2023 v 19:53 odesílatel Adithya Kumaranchath <akumaranchath@xxxxxxxx> napsal:
Hi all,
I am running a simple test and am curious to know why a difference in execution times between PostgreSQL 12 vs PostgreSQL 15.
I have this function:CREATE function test() returns int language plpgsql as $$
declare
v_number bigint;
v_multiplier float = 3.14159;
loop_cnt bigint;
begin
for loop_cnt in 1..1000000000
loop
v_number := 1000;
v_number := v_number * v_multiplier;
end loop;
return 0;
end;$$;
I execute this in PostgreSQL 12:
PostgreSQL 15:
It is much faster in 15 than in 12, and while I love the performance improvement. I am curious to know the rationale behind this improvement on PostgreSQL 15.
The test result is from PostgreSQL on Windows but I observed the same behavior on Linux OS too.
Server Spec:Intel i7-8650U CPU @1.90GHz 2.11GHzRAM 16 GBWindows 11 Enterprise
Thanks,
Adi
Please, don't send screenshots - we believe you :-)
Your code can be little bit faster if you use flag IMMUTABLE
There were more patches that reduced the overhead of _expression_'s evaluation in PL/pgSQL.
History
Some interesting commits
Originally, PL/pgSQL was designed as glue of SQL and the _expression_ evaluation was not too good. It was significantly slower in _expression_'s evaluation than other interpreters like Perl or Python.
But lot of people uses PL/pgSQL for numeric calculations with PostGIS, so speed of _expression_'s evaluation is more important than before, and after all optimizations, although the PL/pgSQL is still slower than generic interprets - still PL/pgSQL should be used mainly like glue of SQL, the difference is significantly less - from 10x times slower to 2 slower. Still there is not any JIT - so the performance is almost good I think.
Regards
Pavel
