Performance of stored functions

Something that users coming from other database systems easily overlook is, that Postgres supports different languages for writing stored functions (and procedures). Choosing the right language can influence the performance of your functions.

The most popular language for stored functions (or procedures) one is probably PL/pgSQL. But very often a function can just as well written in plain SQL which makes the it more efficient, because you get rid of the overhead of calling the PL/pgSQL engine (or any other PL language).

The overhead can be quite substantial. Consider this simple function that generates a random integer between an upper and lower bound.

create function random_int(p_low int, p_high int)
  returns integer
as
$$
begin
  return (random() * (p_high - p_low) + p_low)::int;
end;
$$
language plpgsql;

This is a pretty straightforward implementation, let’s see how fast it is by calling it 10 million times:

explain analyze
select random_int(1,42)
from generate_series(1,10e6);

This returns the following on my computer:

QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..260.00 rows=1000 width=4) (actual time=1792.193..8569.774 rows=10000000 loops=1)
Planning Time: 0.035 ms 
Execution Time: 8876.048 ms 

However the same function implemented as a SQL function:

create function random_int2(int p_low, int p_high)
  returns integer
as
$$
  select (random() * (p_high - p_low) + p_low)::int;
$$
language sql;

This now runs substantially faster:

QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..20.00 rows=1000 width=4) (actual time=1877.210..3587.354 rows=10000000 loops=1)
Planning Time: 0.114 ms 
Execution Time: 3780.595 ms 

Which is essentially the same runtime as if we had used the expression directly inside a SELECT statement. So the overhead of calling a SQL function is extremely low.

So, functions that don’t need loops or other procedural constructs are better implemented as a SQL function, rather than with PL/pgSQL.