The performance cost of pl/pgsql exception block in Postgres

[serpent7776]

One of the languages that Postgres offers for writing stored procedures is pl/pgsql. One of it's features is exception block that is used to catch errors raised in a code block. Appropriate use of this construct can improve code reliability, but when overused it can lead to decreased performance.

The official documentation says the following:
https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

But what exactly significantly more expensive means? Is it 50% slower? 100% slower? Let's check this out.

First, we'll need a table with some data. I'll use the following one:

create table ops(id bigint, body jsonb);

I filled this table with about 20 million rows. Each row contains some json data in body column. The exact structure varies, but there's a common field: all stored jsons are objects containing value key.

Having this, I'll do a simple processing of that data.
For each row in that table I will invoke the following function:

create or replace function process_op(op ops)
returns void volatile language 'plpgsql'
as $$
begin
  insert into data(id, body) select op.id, op.body->>'value';
end;
$$;

This basically extracts the value element and writes it into a separate table data, which has the following structure:

create table data(id bigint, body text);

I will compare obtained results with a second scenario, which will be identical to the first one, with the exception that the following function will be used instead:

create or replace function process_op_exn(op ops)
returns void volatile language 'plpgsql'
as $$
begin
  begin
    insert into data(id, body) select op.id, op.body->>'value';
  exception WHEN division_by_zero then
  end;
end
$$;

The only difference is extra exception block that catches specified exception type. The exact type doesn't matter here, because it will never occur. I have to specify it nonetheless, so it'll be division_by_zero.

Now, here's the script that will do the timing of our processing:

#!/bin/sh
sql() {
    psql -c "$@" ex
}

trunc() {
    sql 'truncate data'
}

trunc
echo 'process_op'
sql 'set jit=off; explain analyze select process_op(op) from ops as op'

trunc
echo 'process_op_exn'
sql 'set jit=off; explain analyze select process_op_exn(op) from ops as op'

This script will use an empty database ex that only contains our two functions and the input and output tables. Before each test, the output table is truncated.
Note that jit is disabled. It's because it'd just introduce additional variance in timing results.
I'll run this script 10 times and collect the time reported by explain analyze.

Here's the times I got on my machine: (PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))

process_op	process_op_exn
97213.305	134802.694
97133.119	134685.843
104139.789	141188.065
103916.659	141514.376
104230.254	140969.064
104054.297	140396.022
104578.363	140755.807
104225.124	140159.735
104693.086	136347.270
100057.957	142008.653

	process_op	process_op_exn	overhead[%]
min	97133.119	134685.843	38.66
max	104693.086	142008.653	35.64
avg	102424.195	139282.753	35.99
stddev	3070.819	2845.707

I repeated the measurements for half of the data size (so about 10 million rows):

process_op	process_op_exn
54302.993	76026.787
54081.487	72822.546
53893.595	79227.740
116475.741	71090.325
52185.475	71807.784
55144.720	79174.003
59353.608	123744.166
59466.183	77938.334
77143.960	83018.802
61518.751	74555.020

	process_op	process_op_exn	overhead[%]
min	52185.475	71090.325	36.23
max	116475.741	123744.166	6.24
avg	64356.651	80940.551	25.77
stddev	19680.908	15501.945

And here's measurements repeated for double the data size (about 40 million rows):

process_op	process_op_exn
286233.511	297223.475
284152.678	351405.713
216810.749	389101.092
295131.977	297492.770
204913.139	274064.567
202276.802	331195.117
266659.591	316588.293
272660.505	336403.196
299557.913	308608.560
210020.255	364334.027

	process_op	process_op_exn	overhead[%]
min	202276.802	274064.567	35.49
max	299557.913	389101.092	29.89
avg	253841.712	326641.681	28.68
stddev	40312.450	34891.699

The timings are pretty consistent and we clearly see that the function with exception block takes longer time to complete.
The slowdown due to the exception block is about 35-38%. This overhead is definitely noticeable, but it'll of course depend on the work load. The more work is performed in such block, the less noticeable it'll be. The important part is that the performance hit is taken even if the exception is never raised.

EDIT 02-02-2024: Added measurements for half and double data set sizes.

Comments

[hivebuzz]

Congratulations @serpent7776! You have completed the following achievement on the Hive blockchain And have been rewarded with New badge(s)

You received more than 10 upvotes. Your next target is to reach 50 upvotes.

_{You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP}

Check out our last posts:

Hive Power Up Day - February 1st 2024

[andablackwidow]

The conclusion is far too premature. The overhead might be constant per call or constant per row being processed, or data dependent but in a different way than the main processing. You are doing two relatively heavy operations in the example: searching for value in jsonb and filling up table. What if you had trivial operation instead? What if you did even more complicated query? How about checking with half of the table rows? Or twice as many? Also I understand the turning off jit, but if you didn't do it, it might actually notice the exception can't be cast and optimize it out - you'd need to run the test with jit on and exception that can be cast depending on data (actually at least two tests - one that depends on indexed data, other with data not included in any index).

Alternative is to look into the postgres sources to see what actually happens when exception handling is added to the block 😁

[serpent7776]

I added the measurements for half and double data sizes.
I really didn't want to go to explore jit possibilities. I'm not very familiar with it and I was also more interested in plpgsql itself.