Avoiding stored procedure calls in PostgreSQL that are not necessary

Preamble

How PostgreSQL treats procedures

As was already mentioned, stored procedures can be created in almost any language. PostgreSQL just sends the code for the stored procedure to the other language and gets the result back. A stored procedure is somewhat of a “black box” because PostgreSQL rarely understands what takes place there.

Here’s an illustration:

 test=# CREATE OR REPLACE FUNCTION mymax(int, int) RETURNS int AS $$ BEGIN RETURN CASE WHEN $1 > $2 THEN $1 ELSE $2 END; END; $$ LANGUAGE 'plpgsql'; CREATE FUNCTION

The results are not very spectacular:

 test=# SELECT mymax(20, 30); mymax ------- 30 (1 row)

Here, it’s crucial to remember that the PL/pgSQL function is a wholly closed system. The planner is unaware of the function of the “external” language in this scenario. This has some significant repercussions. Think about the following instance:

 test=# CREATE TABLE demo AS 	SELECT 	* 	FROM generate_series(1, 1000000) AS id; SELECT 1000000 test=# CREATE INDEX idx_id ON demo(id); CREATE INDEX

The table is already big enough to take indexes into account:

 test=# explain SELECT * FROM demo WHERE id = 20; QUERY PLAN --------------------------------------------------------------- Index Only Scan using idx_id on demo 	(cost=0.42..8.44 rows=1 width=4) Index Cond: (id = 20) (2 rows)

Functions in PostgreSQL are usually blackboxes

The problem is: If we start using the approach I just described, everything completely changes:

 test=# explain SELECT * 	FROM 	demo 	WHERE 	id = mymax(20, 20); QUERY PLAN --------------------------------------------------------- Seq Scan on demo (cost=0.00..266925.00 rows=1 width=4) Filter: (id = mymax(20, 20)) (2 rows)

PostgreSQL is unaware that the function will return 20. The machine was not informed that this was a given outcome, despite the fact that it is obvious to humans. The outcome of a function is regarded as “volatile” by PostgreSQL, meaning that anything can occur. As a result, it cannot just ask the index to return the appropriate row. Even though the parameters for the first and second function calls are the same, the first function call may not return the same result. In order to be safe, the optimizer will perform a sequential scan, which will undoubtedly yield the desired outcome.

A function in PostgreSQL can be:

• VOLATILE
• STABLE
• IMMUTABLE

If you call a function multiple times with the exact same input parameters and mark it as VOLATILE, it will return whatever you pass it. In the case of STABLE, the function will produce the same outcome when given the same inputs during the same transaction.

The most well-known STABLE function, now(), consistently returns the same outcome within a single transaction:

 test=# SELECT now(); now ------------------------------- 2018-01-09 11:48:46.385457+01 (1 row) test=# BEGIN; BEGIN test=# SELECT now(); now ------------------------------- 2018-01-09 11:48:51.073123+01 (1 row) test=# SELECT now(); now ------------------------------- 2018-01-09 11:48:51.073123+01 (1 row) test=# COMMIT; COMMIT test=# SELECT now(); now ------------------------------- 2018-01-09 11:48:59.640697+01 (1 row)

There are even some IMMUTABLE functions. In this instance, regardless of the transaction, the result will be constant given the same input parameters. In this situation, cosine would be an example:

 test=# SELECT cos(10), cos(20); cos | cos --------------------+------------------- -0.839071529076452 | 0.408082061813392 (1 row) test=# SELECT cos(10), cos(20); cos | cos --------------------+------------------- -0.839071529076452 | 0.408082061813392 (1 row)

The cosine of a number will remain the same even in the second transaction.

Reducing the number of function calls

To resolve our issue, we must stop utilizing the current function.

 test=# DROP FUNCTION mymax(int, int); DROP FUNCTION

… and duplicate it:

 test=# CREATE OR REPLACE FUNCTION mymax(int, int) RETURNS int AS $$ BEGIN RETURN CASE WHEN $1 > $2 THEN $1 ELSE $2 END; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE FUNCTION

If the same input values are given, the planner will do an index scan because it will know that the function is meant to return a fixed value:

 test=# explain SELECT * 	FROM 	demo 	WHERE 	id = mymax(20, 20); QUERY PLAN ------------------------------------------------------ Index Only Scan using idx_id on demo 	(cost=0.42..8.44 rows=1 width=4) Index Cond: (id = 20) (2 rows)

Since the index scan is orders of magnitude quicker, it will return right away.

Detecting bottlenecks

Thankfully, PostgreSQL has a system view that could provide some insight into functions that might be problematic. Setting “track_functions = ‘all'” will instruct PostgreSQL to collect function statistics, which is the first thing you need to do:

 test=# SELECT * FROM demo WHERE id = mymax(20, 20); id ---- 20 (1 row)

When this option is enabled, the following useful data will be available in pg_stat_user_functions:

 test=# SELECT * FROM pg_stat_user_functions ; funcid | schemaname | funcname | calls | total_time | self_time --------+------------+----------+-------+------------+----------- 16429 | public | mymax | 1 | 0.025 | 0.025 (1 row)

It can make sense to inspect a function if you notice that it is being called irrationally frequently to see if it is VOLATILE unnecessarily. Speed can be greatly increased by altering the function definition.

Creating indexes on functions in PostgreSQL

You must confirm that a function is actually IMMUTABLE if you want to create indexes on it. PostgreSQL won’t build the index for you if you don’t do this. The explanation is straightforward: PostgreSQL must ensure that the index’s content is reliable and does not need to be updated over time if the underlying data remains constant.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.