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 |
|
You can call that function with:
1 2 3 4 5 6 7 |
|
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 |
|
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 |
|
Running the same query runs in about one second:
1 2 3 4 5 6 7 |
|
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 |
|
1 2 3 4 5 6 7 8 |
|
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 |
|
So let’s tell postgres about it:
1 2 3 4 5 6 |
|
This time postgres doesn’t even need to call the function at all.
1 2 3 4 5 6 7 |
|