Recently one of my customers shared the below code snippet and I was looking for ways to gain performance.
The code snippet
A simple function that has evaluated expression in a loop for one billion iterations.
function speed_test(p_loops int) returns int language plpgsql as $$
declare
v_number bigint;
v_multiplier float = 3.14159;
loop_cnt bigint;
begin
for loop_cnt in 1..p_loops * 1000000
loop
v_number := 333;
v_number := v_number * v_multiplier;
end loop;
return 0;
end;$$;
The piece of code shared caught my attention because the code has evaluated expressions, and this was one of the areas where improvements were made after PostgreSQL 12. I ran a simple test to prove this.
Test run on PostgreSQL v12:
I ran the code run on PostgreSQL v12 in Azure Database for PostgreSQL Flexible Server.
- Region: West Europe
- SKU: GP D2ds_v4, 2 vCores 8 GB RAM, 128 GB Storage
Test run on PostgreSQL v15:
The same piece of code was run on PostgreSQL v15 in Azure Database for PostgreSQL Flexible Server.
- Region: West Europe
- SKU: GP D2ds_v4, 2 vCores 8 GB RAM, 128 GB Storage
We can clearly see that the code snippet on PostgreSQL 15 is 5x faster than PostgreSQL 12. The rationale behind this is several patches that reduced the overhead of the expression's value in PL/pgSQL. PL/pgSQL was not originally designed for extensive numeric calculations like this but the need was realized as more and more developers started to use it this way, so over time there were several commits by the community that lead to this performance improvement.
If you are curious to know more about this, check out this and this commit, which describes the improvements and history of this change.
Thanks to Pavel Stehule from the PostgreSQL community to point out the rationale and consolidate out the commits associated with this change.
If you are on an older version of PostgreSQL like PostgreSQL 12, this is just one of many other reasons to upgrade to PostgreSQL 15. You can see the feature matrix here. PostgreSQL15 will be released on Azure Database for PostgreSQL Flexible Server soon. If your workload is already running an older version of PostgreSQL, then plan your upgrade using the Major Version Upgrade.