I have the following situation where I don't understand why the underlying index will not be used through the security_barrier view.
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;
-----------------------------------------------
When I join to the underlying table it uses the correct index:
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);
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;
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.
Please try to explain to me what is happening.
Thanks in advance
Jacques Combrink