Optimizing Postgres Functions

If you often write postgres functions you should be familiar with the STABLE and STRICT attributes to optimize them for the postgres query planer.

TL;DR;

If you write a postgres function think about its behavior and tell postgres about it to get better query performance.

The basics on postgres functions

I don’t want to go into the details on how to write function in postgres and the plpgsql syntax. Just a small reminder:

A basic plpgsql function:

1
2
3
4
5
CREATE FUNCTION add_ten(num int) RETURNS integer AS $$
BEGIN
RETURN num + 10;
END
$$ LANGUAGE 'plpgsql';

You can call that function with:

1
2
3
4
5
6
7
SELECT add_ten(3);
 add_ten
---------
      13
(1 row)

Time: 0,296 ms

Now lets’ see what happens when calling the function multiple times:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
testdb=# SELECT i, add_ten(3) FROM generate_series(1, 10) i;
 i  | add_ten
----+---------
  1 |      13
  2 |      13
  3 |      13
  4 |      13
  5 |      13
  6 |      13
  7 |      13
  8 |      13
  9 |      13
 10 |      13
(10 rows)

Time: 0,215 ms

Well nothing fancy yet.

simulating heavy work

When it comes to test and benchmark stuff on postgres the little pg_sleep function is always welcome to simulate heavy work.

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION add_ten(num int) RETURNS integer AS $$
BEGIN
PERFORM pg_sleep(0.1);
RETURN num + 10;
END
$$ LANGUAGE 'plpgsql';

Running the same query runs in about one second:

1
2
3
4
5
6
7
testdb=# SELECT i, add_ten(3) FROM generate_series(1, 10) i;
 i  | add_ten
----+---------
...
(10 rows)

Time: 1010,707 ms

Obviously the function got called 10 times.

IMMUTABLE

But this is actually not necessary as the result for the function is the same for a given argument. The function is immutable and if we tell postgres about this behavior the query planer can use this information to optimize.

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION add_ten(num int) RETURNS integer AS $$
BEGIN
PERFORM pg_sleep(0.1);
RETURN num + 10;
END
$$ LANGUAGE 'plpgsql' IMMUTABLE;
1
2
3
4
5
6
7
8
testdb=# SELECT i, add_ten(3) FROM generate_series(1,10) i;
 i  | add_ten
----+---------
 ...
  
(10 rows)

Time: 101,645 ms

So postgres only needs to call the function once and returns the result for each row.

STRICT

There is one more think we could tell the query planer about the function. If we call the function with a NULL value a NULL value will be returned:

1
2
3
4
5
6
7
testdb=# SELECT add_ten(NULL);
 add_ten
---------

(1 row)

Time: 100,824 ms

So let’s tell postgres about it:

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION add_ten(num int) RETURNS integer AS $$
BEGIN
PERFORM pg_sleep(0.1);
RETURN num + 10;
END
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;

This time postgres doesn’t even need to call the function at all.

1
2
3
4
5
6
7
SELECT add_ten(NULL);
 add_ten
---------

(1 row)

Time: 0,228 ms

Comments