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.