UPDATED August 2023: I was inspired by some previous posts with the idea of abusing PostgreSQL security barriers to cheat on the optimizer. I have constructed an example of how a security barrier can theoretically be used to speed up things under some very nasty circumstances.
WARNING – Please keep in mind: This is a demo – it is not meant to be a tuning guideline. It is merely an example showing how things work and what PostgreSQL is capable of. Please don’t use this to tune your databases.
If you write a query in PostgreSQL, the optimizer will try to find a smart way to execute the query. Ideally any SQL database will try to get rid of as much data as possible as soon as possible. In addition it tries to reduce costs by executing cheaper restrictions before expensive restrictions. Usually the user has no control over this process, and there is usually no point in trying to control this process. Remember, the core idea of SQL is that a user can send a query and the database tries to find out the best way – it is not the user who is supposed to arrange restrictions in a clever way.
A little experiment using security barriers
Just for fun (and nothing more) I came up with a small example showing how the order of those quals can be tweaked using security barriers. Here is an example.
First of all we create two functions. One function is going to be fast and one function is going to be slow:
-- funcs CREATE FUNCTION slow_func(int4) RETURNS int4 AS $$ BEGIN EXECUTE 'SELECT pg_sleep(1)'; RAISE NOTICE 'slow_func: %', $1; RETURN $1; END; $$ LANGUAGE 'plpgsql' IMMUTABLE COST 10; CREATE FUNCTION fast_func(int4) RETURNS int4 AS $$ BEGIN RAISE NOTICE 'fast_func: %', $1; RETURN $1; END; $$ LANGUAGE 'plpgsql' IMMUTABLE COST 100;
To make the function slow, we simply call pg_sleep to make it wait for a while. In this example we have also set cost parameters to tell PostgreSQL how expensive a function is. In my example I have marked the slow function as cheaper so that the planner will execute it first under normal circumstances.
Then we can create some test data. 20 rows should be sufficient for our purpose:
-- test data CREATE TABLE t_test (id int4); INSERT INTO t_test SELECT * FROM generate_series(1, 20);
Let’s create a normal view with:
CREATE VIEW v AS SELECT * FROM t_test WHERE id % 2 = 0 AND fast_func(id) = 0;
Now let’s run the code:
SELECT * FROM v WHERE slow_func(id) = 0;
What we see here is that the slow function is now executed quite frequently. Since the function filters out all rows, there is no need to post-filter anything using the fast function. We’ve misled PostgreSQL into calling the slow function first using our cost parameters, and now we can observe the following result:
\timing SELECT * FROM v WHERE slow_func(id) = 0; NOTICE: slow_func: 2 NOTICE: slow_func: 4 NOTICE: slow_func: 6 NOTICE: slow_func: 8 NOTICE: slow_func: 10 NOTICE: slow_func: 12 NOTICE: slow_func: 14 NOTICE: slow_func: 16 NOTICE: slow_func: 18 NOTICE: slow_func: 20 id ---- (0 rows) Time: 10012.580 ms
We need 10 seconds to execute this one.
Adding a security barrier
Now, let’s abuse a security barrier and see what happens:
CREATE VIEW v WITH (security_barrier) AS SELECT * FROM t_test WHERE id % 2 = 0 AND fast_func(id) = 0;
We can execute the very same query again:
Timing is on. NOTICE: fast_func: 2 NOTICE: fast_func: 4 NOTICE: fast_func: 6 NOTICE: fast_func: 8 NOTICE: fast_func: 10 NOTICE: fast_func: 12 NOTICE: fast_func: 14 NOTICE: fast_func: 16 NOTICE: fast_func: 18 NOTICE: fast_func: 20 id ---- (0 rows) Time: 0.685 ms
What we see now is that the query is a lot faster because PostgreSQL was not allowed to swap the filters.
Bottom line on security barriers
As I have stated before, security barriers are a SECURITY issue and not a performance issue. However, you have to take into consideration that the optimizer is restricted by these barriers, so that execution plans may change and performance might differ significantly.
Further important security info
Find out all about View Permissions and Row Level Security as of PostgreSQL v15 here.