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
In-reply-to: 
 <CAO2Kw=eO1_e_jUYE3-quK8Jrsj6VF65TUKPw80o_D3DOArX9-Q@mail.gmail.com>
References: 
 <CAO2Kw=eO1_e_jUYE3-quK8Jrsj6VF65TUKPw80o_D3DOArX9-Q@mail.gmail.com>
Comments: In-reply-to Jacques Combrink <jacques@quantsolutions.co.za>
	message dated "Fri, 10 May 2024 17:40:02 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1541998.1715357417.1@sss.pgh.pa.us>
Date: Fri, 10 May 2024 12:10:17 -0400
Message-ID: <1541999.1715357417@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1541999.1715357417%40sss.pgh.pa.us>
Precedence: bulk

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