Introduction

Using the RDBMS only to store data is restricting the full potential of the database systems, which were designed for server-side processing and provide other options besides being a data container. Some of these options are stored procedures and functions that allow the user to write server-side code, using the principle of bringing computation to data, avoiding large datasets round trips and taking advantage of server resources. PostgreSQL allows programming inside the database since the beginning, with User Defined Functions (UDFs). These functions can be written in several languages like SQL, PL/pgsql, PL/Python, PL/Perl, and others. But the most common are the first two mentioned: SQL and PL/pgsql. However, there may be “anti-patterns” in your code within functions and they can affect performance. This blog will show the reader some simple tips, examples and explanations about increasing performance in server-side processing with User Defined Functions in PostgreSQL. It is also important to clarify that the intention of this post isn’t to discuss whether Business Logic should be placed, but only how you can take advantage of the resources of the database server.

Avoid these antipatterns in your PL/pgsql’s code

1: Use PL/pgsql functions for simple SQL statements

SQL functions, in certain conditions, can have their function bodies inlined into the main query directly. This can be a performance advantage because the function code can be analyzed by the planner, which can apply some optimizations. When can I apply this pattern? When you have a query or group of simple queries that do not require intermediate analysis/process before returning the result. On the other hand, writing these simple SQL sentences in PL/pgsql requires overhead for the PL/pgsql compiler. Example:

1- CREATE OR REPLACE FUNCTION hemisphere_sql (character varying)
    RETURNS character varying     LANGUAGE sql 
    AS $$
    SELECT
        CASE WHEN $1 IN ('UK', 'Germany', 'Japan', 'US', 'China', 'Canada', 'Russia', 'France') THEN
            'North'
        WHEN $1 IN ('South Africa', 'Australia', 'Chile') THEN
            'South'
        ELSE
            'unknown'
        END
$$;

2- CREATE OR REPLACE FUNCTION hemisphere_plpgsql (character varying)
    RETURNS character varying   LANGUAGE plpgsql
    AS $$
DECLARE
    result character varying;
BEGIN
    result:= (
        SELECT
            CASE WHEN $1 IN ('UK', 'Germany', 'Japan', 'US', 'China', 'Canada', 'Russia', 'France') THEN
                'North'
            WHEN $1 IN ('South Africa', 'Australia', 'Chile') THEN
                'South'
            ELSE
                'unknown'
            END);
    RETURN result;
END;
$$;

1- EXPLAIN (ANALYZE,VERBOSE ) SELECT hemisphere_sql(country) FROM customers;

Seq Scan on public.customers  (cost=0.00..963.00 rows=20000 width=32) (actual time=0.039..29.309 rows=20000 loops=1)
   Output: CASE WHEN ((country)::text = ANY ('{UK,Germany,Japan,US,China,Canada,Russia,France}'::text[])) THEN 'North'::text WHEN ((country)::text = ANY ('{"South Africa",Australia,Chile}'::text[])) THEN 'South'::text ELSE 'unknown'::text END
 Planning Time: 0.458 ms
 Execution Time: 32.306 ms

2-EXPLAIN (ANALYZE,VERBOSE ) SELECT hemisphere_plpgsql(country) FROM customers;

 Seq Scan on public.customers  (cost=0.00..5688.00 rows=20000 width=32) (actual time=0.654..174.685 rows=20000 loops=1)
   Output: hemisphere_plpgsql(country)
 Planning Time: 0.082 ms
 Execution Time: 175.972 ms 

As we can see in the outputs of the explains commands, on first EXPLAIN, output tag has the SQL code that belongs to SQL function, which means that this code is inlined into the calling query, rather than call the function, as the second EXPLAIN, this can improve the performance of our queries.

2: Unnecessary usage of SELECT INTO clause

Inside the PL/pgsql function, it is a bit more costly to assign a value using SELECT INTO than a simple assignment using :=. When can I apply this? When the := operator can replace the INTO clause. Example:

1- CREATE OR REPLACE FUNCTION simple ()  RETURNS void
    AS $$
DECLARE
    s int DEFAULT 0;
BEGIN
    FOR i IN 1..10000 LOOP
        s := s + 1;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

2- CREATE OR REPLACE FUNCTION using_select ()  RETURNS void
    AS $$
DECLARE
    s int DEFAULT 0;
BEGIN
    FOR i IN 1..10000 LOOP
        SELECT s + 1 INTO s;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

1-SELECT simple();
 Time: 16.980 ms
2-SELECT using_select();
Time: 86.931 ms

3: Overusing RAISE clause

RAISE clause can be useful to debug and show some information about the code, but it carries or has an extra load within the functions. Only use it if necessary in the production environments. Example:

1- CREATE OR REPLACE FUNCTION some_sum (val int, cnt int)
    RETURNS int
    AS $$
DECLARE
    i int;
    result int:= 0;
BEGIN
    FOR i IN 1.. $2 LOOP
        result:= result + $1;
    END LOOP;
    RAISE notice 'Final value of result: %', result;
    RETURN result;
END;
$$
LANGUAGE plpgsql;
2- CREATE OR REPLACE FUNCTION some_sum_raise (val int, cnt int)
    RETURNS int
    AS $$
DECLARE
    i int;
    result int:= 0;
BEGIN
    FOR i IN 1.. $2 LOOP
        result:= result + $1;
        RAISE notice 'Temporary value of result: %', result;
    END LOOP;
    RAISE notice 'Final value of result: %', result;
    RETURN result;
END;
$$
LANGUAGE plpgsql;

1- SELECT  some_sum(3,100);
NOTICE:  Final value of result: 300
Time: 1.843 ms

2- SELECT  some_sum_raise(3,100);
NOTICE:  Temporary value of result: 3
...
NOTICE:  Temporary value of result: 300
NOTICE:  Final value of result: 300
Time: 8.578 ms

4: Overusing the high-level programming coding style for SQL activities

Even programmers who come from high-level programming are unaware of the benefits of SQL, and ADVANCED SQL a language that can speed up the performance considerably by avoiding unnecessary loops. For example iterating on a FOR LOOP and doing a select within can be replaced by a single query using the LATERAL clause, which essentially is like a SQL for each loop

1- CREATE OR REPLACE  FUNCTION oldest_orders_by_customer (int) RETURNS SETOF t_oldest_orders_by_customer  AS $$
 DECLARE
    c customers;
    result record;
 BEGIN 
	 FOR c IN SELECT * FROM customers c2 WHERE age > $1    loop
	     SELECT c.firstname,o.orderid, o.orderdate , o.totalamount into result
              FROM orders o 
              WHERE o.customerid = c.customerid
              ORDER BY o.orderdate DESC
              LIMIT 1;
         IF result is not null THEN       
             RETURN NEXT result;   
         END IF;   
     END LOOP;
    RETURN;
 END;
 $$
 LANGUAGE plpgsql;


2- CREATE OR REPLACE  FUNCTION oldest_orders_by_customer_lateral (int) RETURNS SETOF t_oldest_orders_by_customer  AS  $$
 BEGIN 
	RETURN QUERY SELECT customer_sub.firstname  , o_sub.*
       FROM (SELECT * FROM customers c2 WHERE age > $1) customer_sub,
       LATERAL (SELECT o.orderid, o.orderdate , o.totalamount 
                FROM orders o 
                WHERE o.customerid = customer_sub.customerid
                ORDER BY o.orderdate DESC
                LIMIT 1) o_sub;
 END;
 $$
 LANGUAGE plpgsql;

 
1- SELECT * FROM oldest_orders_by_customer(80);
Time: 89.296 ms

2- SELECT * FROM oldest_orders_by_customer_lateral(80);
Time: 45.230 ms

Using functions properties

The definition of functions has some properties that can help with function performance, for example:

1: Use PARALLEL SAFE whenever possible

The planner cannot determine automatically if a function is parallel safe, but under certain conditions parallel mode can boost performance significantly if you process a large dataset. The number of workers that the planner will use is limited by the parameters max_parallel_workers_per_gather, which in turn are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers, the maximum number of concurrent queries to execute with parallelism is determined by the following formula, as long as max_worker_processes<= server cores:

#Q_concurrent_par = max_worker_processes /max_parallel_workers_per_gather (integer division)

When is it safe to use PARALLEL in a function? As long as your code does not perform the following, you should be ready to use it:

  • Writes to the database.
  • Access sequences.
  • Change the transaction state.
  • Makes persistent changes to settings.
  • Access temporary tables.
  • Use cursors.
  • Defines prepared statements

Example:


1- CREATE OR REPLACE FUNCTION pair_div_4 (i int) RETURNS boolean
    AS $$
BEGIN
    IF $1%2 = 0 AND $1%4 = 0 THEN
        RETURN TRUE;
    END IF;
    RETURN FALSE;
END;
$$
LANGUAGE plpgsql;

2- CREATE OR REPLACE FUNCTION pair_div_4_ps (i int) RETURNS boolean
    AS $$
BEGIN
    IF $1%2 = 0 AND $1%4 = 0 THEN
        RETURN TRUE;
    END IF;
    RETURN FALSE;
END;
$$
LANGUAGE plpgsql
PARALLEL SAFE;


1- EXPLAIN ANALYZE  SELECT * from trade where pair_div_4 (id);

 Seq Scan on trade  (cost=0.00..448684.86 rows=563520 width=16) (actual time=0.323..2459.553 rows=422640 loops=1)
   Filter: pair_div_4(id)
   Rows Removed by Filter: 1267921
 Planning Time: 0.070 ms
 Execution Time: 2471.796 ms


2- explain analyze  select * from trade where pair_div_4_ps (id);

 Gather  (cost=1000.00..249635.11 rows=563520 width=16) (actual time=0.883..1386.856 rows=422640 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on trade  (cost=0.00..192283.11 rows=234800 width=16) (actual time=0.868..1301.220 rows=140880 loops=3)
         Filter: pair_div_4_ps(id)
         Rows Removed by Filter: 422640
 Planning Time: 0.138 ms
 Execution Time: 1405.412 ms

As shown in the outputs of the explains, on the second EXPLAIN, Workers Launched tag has value 2, this means that this query used 2 workers to execute, and the first EXPLAIN was executed without parallelisms

2: Use IMMUTABLE when it is possible

The IMMUTABLE option informs the query optimizer about the behavior of the function and can apply some optimization. Any call to the function with all-constant arguments can be immediately replaced with the function value. To mark a function as IMMUTABLE you need to comply with the following:

  • You cannot modify the database (state) and always returns the same result for the same argument values;

  • Do not search in the databases or use information that is not directly present in its argument list values.

Example:

1- CREATE OR REPLACE FUNCTION get_date (date) RETURNS int
    AS $$
DECLARE
    i int;
    result int:= 0;
BEGIN
   RETURN extract (day from $1);
END;
$$
LANGUAGE plpgsql;


2- CREATE OR REPLACE FUNCTION get_date_i (date) RETURNS int
    AS $$
DECLARE
    i int;
    result int:= 0;
BEGIN
   RETURN extract (day from $1);
END;
$$
LANGUAGE plpgsql IMMUTABLE;


1- SELECT 1  from trade where id=get_date(current_date);
 Time: 2557.521 ms (00:02.558)
2- SELECT 1  from trade where id=get_date_i(current_date);;
 Time: 2208.848 ms (00:02.209)

Monitoring performance of functions

PostgreSQL allows the user to track the performance of functions in the database. For example, we can see the performance stats using the view pg_stat_user_functions, as long as you configure the parameter named track_functions, that allows tracking function call counts and time spent. To simplify the configuration we can leverage the option that gives us postgresqlcon.nf to share a configuration file, download it and apply it to your server. Specifically, to track function performance, select the download format alter_system, apply the modification to your server, and reload the configuration using select pg_reload_conf(). This allows us to detect which functions are working as expected or are slow.

For example, to use this view you can write a query like this:

select schemaname||'.'||funcname func_name, calls, total_time, round((total_time/calls)::numeric,2) as mean_time, self_time   from pg_catalog.pg_stat_user_functions;


         func_name         | calls | total_time | mean_time | self_time 
---------------------------+-------+------------+-----------+-----------
 public.f_plpgsql          |     2 |     93.908 |     46.95 |    93.908
 public.auditoria_clientes |  2684 |    593.705 |      0.22 |   593.705
 public.prc_clientes       |     2 |      1.447 |      0.72 |     0.387
 public.max_pro_min        |     3 |      1.589 |      0.53 |     1.589
 public.registro_ddl       |    17 |     39.217 |      2.31 |    39.217
 public.registro_ddl_drop  |     2 |    422.386 |    211.19 |   422.386

calls: Number of times this function has been called

total_time: Time(ms) spent in this function and all other functions called by it inside their code

mean_time: AVG Time(ms) spent in this function and all other functions called by it inside their code

self_time: Time(ms) spent in this function itself, without including other functions called by it

Conclusions

The tips and examples shown above have shown us that sometimes with minimum changes written in our code in PostgreSQL’s functions we can get some performance benefits. These tips are not exclusive, whenever possible these can be combined to achieve an improvement. e.g.: PARALLEL SAFE and avoid overusing the RAISE clause. If you know any other tips or examples please feel free to share them with us. Also, we can monitor our function’s performance by issuing a simple change in PostgreSQL’s configuration.