PostgreSQL Tutorial: Check Slow Functions

February 23, 2024

Summary: in this tutorial, you will learn how to check slow functions in PostgreSQL.

Table of Contents

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.

Starting from PostgreSQL 8.4, PostgreSQL offers a valuable feature: Per function statistics. Prior to this feature , there wasn’t an easy way to find out most time consuming functions in the database.

For time spent on single call, it’s easy to find out by executing function manually but it was too cumbersome and in some cases impossible to find out total time spent by function for the given time period. Enabling full logging and aggregating duration for each call by reading large log files was the only way to get that statistic. Now, you can find this details by querying a single view pg_stat_user_functions!

To enable function tracking you need to enable “track_functions” parameter in postgresql.conf. The default is none, which disables function statistics tracking. Specify pl to track only procedural-language functions, all to also track SQL and C language functions.

Find out slow 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.

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

SELECT schemaname || '.' || funcname AS func_name, calls, total_time,
       round((total_time/calls)::numeric, 2) AS mean_time, self_time
FROM pg_catalog.pg_stat_user_functions
ORDER BY self_time DESC;

which outputs the following:

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