From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jacques Combrink <jacques@quantsolutions.co.za>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Security barrier view index on join condition
Date: Fri, 10 May 2024 12:10:17 -0400
Message-ID: <1541999.1715357417@sss.pgh.pa.us> (raw)
In-Reply-To: <CAO2Kw=eO1_e_jUYE3-quK8Jrsj6VF65TUKPw80o_D3DOArX9-Q@mail.gmail.com>
References: <CAO2Kw=eO1_e_jUYE3-quK8Jrsj6VF65TUKPw80o_D3DOArX9-Q@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jacques@quantsolutions.co.za, pgsql-bugs@lists.postgresql.org
Subject: Re: Security barrier view index on join condition
In-Reply-To: <1541999.1715357417@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox