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
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 |