Assignment performance in PL/pgSQL

In my post about choosing the language for stored functions I showed what impact the different languages have.

When choosing PL/pgSQL as the language, there is one performance optimization that is often overlooked: assignment of values to variables. Different approaches have a different performance.

Let’s have a look at the impact of the different assignment options:

Assignment using := and a select statement (unfortunately I do see this a lot):

create or replace function apply_tax_1(p_amount numeric)
  returns numeric
as
$$
declare 
  l_result numeric;
begin  
  l_result := (select p_amount  * 1.19);
  return l_result;
end;    
$$
language plpgsql;

Assignment using select ... into:

create or replace function apply_tax_2(p_amount numeric)
  returns numeric
as
$$
declare 
  l_result numeric;
begin  
  select p_amount * 1.19 
     into l_result;
     
  return l_result;
end;    
$$
language plpgsql;

And finally, direct assignment of the result:

create or replace function apply_tax_3(p_amount numeric)
  returns numeric
as
$$
declare 
  l_result numeric;
begin  
  l_result := p_amount * 1.19;
  return l_result;
end;    
$$
language plpgsql;

Let’s call this function 5 million times:

explain (analyze)
select apply_tax_1(42.1)
from generate_series(1,5e6) t(i);

QUERY PLAN                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series t  (cost=0.00..260.00 rows=1000 width=32) (actual time=798.001..14348.384 rows=5000000 loops=1)
Planning Time: 0.036 ms                                                                                                         
Execution Time: 14506.170 ms                                                                                                    

explain (analyze)
select apply_tax_2(42.1)
from generate_series(1,5e6) t(i);

QUERY PLAN                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series t  (cost=0.00..260.00 rows=1000 width=32) (actual time=790.721..10848.528 rows=5000000 loops=1)
Planning Time: 0.025 ms                                                                                                         
Execution Time: 11003.520 ms                                                                                                    

explain (analyze)
select apply_tax_3(42.1)
from generate_series(1,5e6) t(i);

QUERY PLAN                                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series t  (cost=0.00..260.00 rows=1000 width=32) (actual time=834.983..4651.848 rows=5000000 loops=1)
Planning Time: 0.035 ms                                                                                                        
Execution Time: 4747.803 ms                                                                                                    

As we can see, the “assignment” using a select statement is about 3 times slower then a direct assignment.