This is a short note on the peformance of User Definded Functions and a volatility classification.

I have been working with PG for 3 years. As time goes on, I realize how foolish I was.

The following is one of the things I missed in the projects I worked on in the past.

Usually I guide developers not to create UDFs, because UDFs do a lot of harm to the performance of DB systems. And when I am forced to allow them to create UDFs, I just teach them how to reduce UDF calls, not giving any instructions on how to write UDFs.

Recently I read the PG document and found out that depending on the volatility of the UDF, the performance can be different.

Below is the URL of the menual.

https://www.postgresql.org/docs/9.5/xfunc-volatility.html

I did some performance experiments on the volatility categories.

Here are sample codes running under PostgreSQL 13.

CREATE TABLE ORDERS_DETAIL(ORD_LINE_NO BIGINT NOT NULL,ORD_NO BIGINT NOT NULL,PROD_ID VARCHAR(10) NOT NULL,COMMENT VARCHAR(100),ORD_AMT BIGINT);

ALTER TABLE ORDERS_DETAIL ADD CONSTRAINT PK_ORDERS_DETAIL PRIMARY KEY(ORD_LINE_NO);

CREATE TABLE PROD (PROD_ID VARCHAR(10) NOT NULL,PROD_NM VARCHAR(100) NOT NULL);

ALTER TABLE PROD ADD CONSTRAINT PK_PROD PRIMARY KEY(PROD_ID);

INSERT INTO ORDERS_DETAIL

SELECT ias ORD_LINE_NO  

          , mod(i,1000000) AS ORD_NO  

          , 'PP'||MOD(i,5) AS PROD_ID  

          , lpad('X',10,'Y') as comment

      , case when i< 1000 then i*100 else iend as prod_amt

  FROM generate_series(1,10000000) a(i);

INSERT INTO PROD    

 SELECT PROD_ID

           , MAX(ORD_NO)||'TEST_NAME'        

  FROM ORDERS_DETAIL

 GROUP BY PROD_ID;

--I have created a pure sql function with VOLATILE. (Volatile id default)

CREATE OR REPLACE FUNCTION portal.f_get_prod_nm(text)

 RETURNS text

 LANGUAGE sql

AS $$

SELECT PROD_NM

  FROM PROD

 WHERE PROD_ID = $1;

$$;

--I have created an sql function with STABLE volatility.

 CREATE OR REPLACE FUNCTION portal.f_get_prod_nm_pure(text)

 RETURNS text

 STABLE

 LANGUAGE sql

AS $$

SELECT PROD_NM

  FROM PROD

 WHERE PROD_ID = $1;

 $$;

--I have created a volatile PL/PgSQL function. (Volatile is default.)

CREATE OR REPLACE FUNCTION F_GET_PROD_NM_plpgsql(I_PROD_ID VARCHAR)

RETURNS VARCHAR

AS $$

/* PROD_ID 입력 시 PROD_NM 출력 */

DECLARE  O_PROD_NM VARCHAR(100);

BEGIN  

    SELECT PROD_NM   

        INTO O_PROD_NM   

      FROM PROD  

    WHERE PROD_ID = I_PROD_ID;  

     RETURN O_PROD_NM;

END; $$

LANGUAGE plpgsql;

--I have created a STABLE PL/PgSQL function.

CREATE OR REPLACE FUNCTION F_GET_PROD_NM_stable(I_PROD_ID VARCHAR)

RETURNS VARCHAR

STABLE

AS $$

/* PROD_ID 입력 시 PROD_NM 출력 */

DECLARE  O_PROD_NM VARCHAR(100);

BEGIN  

    SELECT PROD_NM   

        INTO O_PROD_NM   

      FROM PROD  

    WHERE PROD_ID = I_PROD_ID;  

     RETURN O_PROD_NM;

END; $$

LANGUAGE plpgsql;

--I have created an IMMUTABLE PL/PgSQL function

CREATE OR REPLACE FUNCTION 

F_GET_PROD_NM_immutable(I_PROD_ID VARCHAR)

RETURNS VARCHAR

IMMUTABLE

AS $$

/* PROD_ID 입력 시 PROD_NM 출력 */

DECLARE  O_PROD_NM VARCHAR(100);

BEGIN  

    SELECT PROD_NM   

        INTO O_PROD_NM   

      FROM PROD  

    WHERE PROD_ID = I_PROD_ID;  

     RETURN O_PROD_NM;

END; $$

LANGUAGE plpgsql;

Below are the SQL statements to check UDFs' performance followed by their excution plans.

SELECT A.ORD_NO, F_GET_PROD_NM(A.PROD_ID) AS PROD_NM   

   FROM ORDERS_DETAIL A          

 WHERE A.ORD_NO BETWEEN 1 AND 100000;

 Seq Scan on orders_detail a (actual time=0.221..3589.892 rows=1000000 loops=1)

   Filter: ((ord_no >= 1) AND (ord_no <= 100000))

   Rows Removed by Filter: 9000000

   Buffers: shared hit=1000627 read=82726

 Planning:

   Buffers: shared hit=400

 Planning Time: 8.921 ms

 Execution Time: 3637.446 ms

SELECT A.ORD_NO, F_GET_PROD_NM_PURE(A.PROD_ID) AS PROD_NM   

   FROM ORDERS_DETAIL A          

 WHERE A.ORD_NO BETWEEN 1 AND 100000;

 Seq Scan on orders_detail a (actual time=0.251..3328.829 rows=1000000 loops=1)

   Filter: ((ord_no >= 1) AND (ord_no <= 100000))

   Rows Removed by Filter: 9000000

   Buffers: shared hit=1001107 read=82246

 Planning:

   Buffers: shared hit=400

 Planning Time: 2.277 ms

 Execution Time: 3370.142 ms

We can notice that an sql funtion with STABLE volatility is faster(3637ms->3370ms).

SELECT A.ORD_NO, F_GET_PROD_NM_PLPGSQL(A.PROD_ID) AS PROD_NM   

   FROM ORDERS_DETAIL A          

 WHERE A.ORD_NO BETWEEN 1 AND 100000;

 Seq Scan on orders_detail a (actual time=0.120..4559.058 rows=1000000 loops=1)

   Filter: ((ord_no >= 1) AND (ord_no <= 100000))

   Rows Removed by Filter: 9000000

   Buffers: shared hit=1000672 read=82662

 Planning Time: 0.095 ms

 Execution Time: 4602.952 ms

SELECT A.ORD_NO, F_GET_PROD_NM_STABLE(A.PROD_ID) AS PROD_NM   

   FROM ORDERS_DETAIL A          

 WHERE A.ORD_NO BETWEEN 1 AND 100000; 

 Seq Scan on orders_detail a (actual time=0.198..4309.964 rows=1000000 loops=1)

   Filter: ((ord_no >= 1) AND (ord_no <= 100000))

   Rows Removed by Filter: 9000000

   Buffers: shared hit=1000704 read=82630

 Planning Time: 0.048 ms

 Execution Time: 4351.524 ms

The elapsed time reduced from 4602 ms to 4351 ms by using a STABLE function.

SELECT A.ORD_NO, F_GET_PROD_NM_IMMUTABLE(A.PROD_ID) AS PROD_NM   

   FROM ORDERS_DETAIL A          

 WHERE A.ORD_NO BETWEEN 1 AND 100000; 

 Seq Scan on orders_detail a  (cost=0.00..476799.75 rows=973863 width=40) (actual time=0.212..4243.079 rows=1000000 loops=1)

   Filter: ((ord_no >= 1) AND (ord_no <= 100000))

   Rows Removed by Filter: 9000000

   Buffers: shared hit=1000736 read=82598

 Planning Time: 0.085 ms

 Execution Time: 4285.180 ms

I wanted to view statistics about executions of the functions.

set track_functions = 'all';

--You execute the queries above.

analdb=# select * from pg_stat_user_functions;

 funcid | schemaname |        funcname         |  calls  | total_time | self_time

--------+------------+-------------------------+---------+------------+-----------

  42612 | portal     | f_get_prod_nm           | 1000000 |   2722.779 |  2722.779

50937 | portal     | f_get_prod_nm_pure      | 1000000 |    2536.63 |   2536.63

  50934 | portal     | f_get_prod_nm_plpgsql   | 1000000 |   3901.993 |  3901.993

  50935 | portal     | f_get_prod_nm_stable    | 1000000 |   3809.062 |  3809.062

  50936 | portal     | f_get_prod_nm_immutable | 1000000 |   3804.095 |  3804.095

Conclusion

1. In most cases sql functions which only use pure SQL are a better solution than PL/PgSQL funtions. When you create a function without specifying language you are creating PL/PgSQL functions which are less efficient. When you don't need any procedural elements or variables that are not available in sql functions, You should not create PL/PgSQL functions.

2. When you create functions you have to check the function's volatility.  When you are sure that the output value of the UDF to the input value is fixed or deterministic, you'd better create the UDF with STABLE volatility.

FOOTNOTE 1

You can reset the statistics in the pg_stat_user_functions.

select pg_stat_reset_single_function_counters(oid);

FOOTNOTE 2

Regarding the difference between the language sql and the language PL/PgSQL, refer to the answer on the site below.

https://stackoverflow.com/questions/24755468/difference-between-language-sql-and-language-plpgsql-in-postgresql-functions

Addendum

Regarding PostgreSQL internal functions, there are three kinds of functions.

* Immutable Function : If you provide the same parameter set, the planner can calculate it during its planning time. So the internal function becomes a constant. Therefore, it can use its statistics.

* Stable Function      : You read the database and you don't change it.

* Volatile Function     : The function changes the database value.

* Stable and volatile functions will not be calculated during planning time.

The following is a test script to check the peformance difference of stable and immutable.

 create table t2 (c1 int, c2 date);

insert into t2

 select i, make_date(2021,12,31) -i

   from generate_series(1,900000) a(i);

MAKE_DATE is an immutable function.

select *

  from t2

 where c2 >= make_date(1200,1,1);

 Seq Scan on t2  (cost=0.00..15233.00 rows=301398 width=8) (actual time=0.033..162.367 rows=300229 loops=1)

   Filter: (c2 >= '1200-01-01'::date)

   Rows Removed by Filter: 599771

 Planning Time: 1.087 ms

 Execution Time: 176.750 ms

(5 rows)

TO_DATE is a stable function.

select *

  from t2

 where c2 >= to_date('12000101','yyyymmdd');

 Seq Scan on t2  (cost=0.00..17483.00 rows=301398 width=8) (actual time=0.027..668.266 rows=300229 loops=1)

   Filter: (c2 >= to_date('12000101'::text, 'yyyymmdd'::text))

   Rows Removed by Filter: 599771

 Planning Time: 0.565 ms

 Execution Time: 682.389 ms

The elapsed time increased from 176 to 682 ms. We can infer that the function MAKE_DATE was executed only once at planning time and the function TO_DATE was executed 999999 times.

In order to make a stable function to run only once, you should use a subquery.

select *

  from t2

 where c2 >= (select to_date('12000101','yyyymmdd'));

Seq Scan on t2  (cost=0.01..15233.01 rows=300000 width=8) (actual time=0.063..134.467 rows=300229 loops=1)

   Filter: (c2 >= $0)

   Rows Removed by Filter: 599771

   InitPlan 1 (returns $0)

     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

 Planning Time: 0.128 ms

 Execution Time: 148.075 ms

The drawback is that access to an InitPlan or SubPlan is always parallel restricted. A parallel restricted operation is one which cannot be performed in a parallel worker, but which can be performed in the leader while parallel query is in use.

In the same context, you are recommended not to use a to_timestamp function. 

Here is a sample code to demonstrate my point.

select *

  from t2

 where c2 >= to_timestamp('12000101','yyyymmdd');

 Seq Scan on t2  (cost=0.00..17483.00 rows=301398 width=8) (actual time=0.021..753.129 rows=300229 loops=1)

   Filter: (c2 >= to_timestamp('12000101'::text, 'yyyymmdd'::text))

   Rows Removed by Filter: 599771

 Planning Time: 0.142 ms

 Execution Time: 766.851 ms

Here is the SQL statement I optimized.

explain (analyze)

select *

  from t2

 where c2 >= '12000101'::timestamp;

 Seq Scan on t2  (cost=0.00..15233.00 rows=301398 width=8) (actual time=0.023..130.683 rows=300229 loops=1)

   Filter: (c2 >= '1200-01-01 00:00:00'::timestamp without time zone)

   Rows Removed by Filter: 599771

 Planning Time: 0.093 ms

 Execution Time: 143.832 ms

We can see that the elapsed time decreased from 766 ms to 143 ms.

I can’t guarantee the correctness of the description I’ve given here, but it’s probably fairly accurate.

"The stable internal function to_timestamp('12000101','yyyymmdd') was executed 999999 times. When you replace to_timestamp('12000101','yyyymmdd') with  '12000101'::timestamp, you can get tremendous performance boost because the latter is run only once."