Thread: Security barrier view index on join condition
I have the following situation where I don't understand why the underlying index will not be used through the security_barrier view.
Setup
Setup
-----------------------------------------------
CREATE TABLE join_test (
id SERIAL PRIMARY KEY,
description text
);
INSERT INTO join_test (id) SELECT generate_series(1, 2000000);
CREATE TABLE join_test_2 (
id SERIAL PRIMARY KEY,
description text
);
INSERT INTO join_test_2 (id) SELECT generate_series(1, 2000000);
ANALYZE join_test;
ANALYZE join_test_2;
CREATE OR REPLACE VIEW l2_security_view WITH (security_barrier=true) AS
SELECT * FROM join_test_2;
id SERIAL PRIMARY KEY,
description text
);
INSERT INTO join_test (id) SELECT generate_series(1, 2000000);
CREATE TABLE join_test_2 (
id SERIAL PRIMARY KEY,
description text
);
INSERT INTO join_test_2 (id) SELECT generate_series(1, 2000000);
ANALYZE join_test;
ANALYZE join_test_2;
CREATE OR REPLACE VIEW l2_security_view WITH (security_barrier=true) AS
SELECT * FROM join_test_2;
-----------------------------------------------
When I join to the underlying table it uses the correct index:
EXPLAIN ANALYZE
SELECT join_test_2.id FROM join_test
LEFT JOIN join_test_2 USING(id)
WHERE join_test.id IN (6, 10);
SELECT join_test_2.id FROM join_test
LEFT JOIN join_test_2 USING(id)
WHERE join_test.id IN (6, 10);
When I join to the security barrier view it does not work:
EXPLAIN ANALYZE
SELECT l2_security_view.id FROM join_test
LEFT JOIN l2_security_view USING(id)
WHERE join_test.id IN (6, 10);
EXPLAIN ANALYZE
SELECT l2_security_view.id FROM join_test
LEFT JOIN l2_security_view USING(id)
WHERE join_test.id IN (6, 10);
Although when I change the query WHERE condition to a straight up equals, it somehow works:
EXPLAIN ANALYZE
SELECT l2_security_view.id FROM join_test
LEFT JOIN l2_security_view USING(id)
WHERE join_test.id = 6;
Any of the following permutations in the WHERE clause when joining to the security barrier view does not work:
WHERE join_test.id IN (6, 10);
WHERE join_test.id = ANY(ARRAY[6, 10])
WHERE join_test.id < 10;
EXPLAIN ANALYZE
SELECT l2_security_view.id FROM join_test
LEFT JOIN l2_security_view USING(id)
WHERE join_test.id = 6;
Any of the following permutations in the WHERE clause when joining to the security barrier view does not work:
WHERE join_test.id IN (6, 10);
WHERE join_test.id = ANY(ARRAY[6, 10])
WHERE join_test.id < 10;
There are some changes to the query plan and execution in most of the queries above if you tinker with:
enable_seqscan
random_page_cost
seq_page_cost
enable_hashjoin
enable_mergejoin
enable_nestloop
There are also some other ways to write the query to get a different plan and execution. But the way I have it here is representative of real world problems that we are experiencing now.
enable_seqscan
random_page_cost
seq_page_cost
enable_hashjoin
enable_mergejoin
enable_nestloop
There are also some other ways to write the query to get a different plan and execution. But the way I have it here is representative of real world problems that we are experiencing now.
Please try to explain to me what is happening.
Thanks in advance
Jacques Combrink
Jacques Combrink <jacques@quantsolutions.co.za> writes:
> I have the following situation where I don't understand why the underlying
> index will not be used through the security_barrier view.
[ shrug ... ] A security barrier is a pretty crippling restriction
on what the optimizer can do. The barrier view will always be planned
separately, which is why you usually end up with a seqscan on join_test_2.
> Although when I change the query WHERE condition to a straight up equals,
> it somehow works:
> EXPLAIN ANALYZE
> SELECT l2_security_view.id FROM join_test
> LEFT JOIN l2_security_view USING(id)
> WHERE join_test.id = 6;
This happens to work because the implied USING qual is combined with
the equality clause from WHERE to produce the replacement conditions
"join_test.id = 6 AND l2_security_view.id = 6". Then, after verifying
that the subquery's restriction clause "l2_security_view.id = 6" is
leakproof, it's allowed to be pushed down into the subquery, becoming
"join_test_2.id = 6", from which the subquery can produce an indexscan
plan. But that doesn't work for normal join conditions, since those
can't be pushed into the subquery. It also doesn't get applied with
WHERE conditions that are anything but simple equality to a constant.
Certainly there's work that could be done to make this a little
better, but it would be a lot of work and probably would not move the
goalposts very far. You should expect security barriers to hurt
performance-wise.
regards, tom lane