Postgres Pro
Downloads
Home   >   mailing lists

Security barrier view index on join condition - Mailing list pgsql-bugs

From Jacques Combrink
Subject Security barrier view index on join condition
Date
Msg-id CAO2Kw=eO1_e_jUYE3-quK8Jrsj6VF65TUKPw80o_D3DOArX9-Q@mail.gmail.com
Whole thread Raw
Responses Re: Security barrier view index on join condition
List pgsql-bugs
Tree view
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




pgsql-bugs by date:

Previous
From: Ugur Yilmaz
Date:
Subject: Postgresql 16.3 installation error (setup file) on Windows 11
Next
From: Tom Lane
Date:
Subject: Re: Security barrier view index on join condition