Inspired by some previous posts on PostgreSQL security barriers I had the idea of abusing PostgreSQL security barriers (http://www.postgresql.org/docs/9.3/static/sql-createview.html) to cheat on the optimizer. I have constructed an example how a security barrier can theoretically be used to speed up things under some very nasty circumstances.
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.
Ordering restrictions
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 to executing cheaper restrictions before expensive restrictions. Usually the user has no control over this process and practically – 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
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 us create a normal view with
CREATE VIEW v AS
SELECT *
FROM t_test
WHERE id % 2 = 0
AND fast_func(id) = 0;Let us run the code now:
SELECT * FROM v WHERE slow_func(id) = 0;
What we see here is now that the slow function is executed quite frequently. As the function filters out all rows there is no need to post-filter anything using the fast function. As we have mislead PostgreSQL into calling the slow function first using our cost parameters, 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
What we see here is that we need 10 seconds to execute this one.
Adding a security barrier
Let us abuse a security barrier now 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 those filters.
Bottom line
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 those barriers so that execution plans may change and performance might differ significantly.