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.11GHz
RAM 16 GB
Windows 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.
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.