Security barrier view index on join condition

From: Jacques Combrink <jacques(at)quantsolutions(dot)co(dot)za>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Security barrier view index on join condition
Date: 2024-05-10 15:40:02
Message-ID: CAO2Kw=eO1_e_jUYE3-quK8Jrsj6VF65TUKPw80o_D3DOArX9-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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:

EXPLAIN ANALYZE
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);

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2024-05-10 16:10:17 Re: Security barrier view index on join condition
Previous Message Ugur Yilmaz 2024-05-10 14:11:25 Postgresql 16.3 installation error (setup file) on Windows 11