[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: GIST combo index condition chosen for users queries is different from table owner's query
From: Dennis White <dwhite () seawardmoon ! com>
Date: 2022-09-20 12:50:01
Message-ID: CAE=rie_miUgeoOgQmaPip-aosPvZX93Ssd0Csi58=Y8GcK69Vw () mail ! gmail ! com
[Download RAW message or body]
Thanks.
As soon as I read your reply I recalled the leakproof issue from a
discussion with a former colleague years ago.
At the time, I was new to Postgresql and I realize now I should have
remembered that.
Disabling the RLS indeed resulted in the superior plan for the test_user.
The harder part will be baking the function call used for RLS into all
query predicates rather than relying on RLS to do it for us.
I also recall that we got around the leakproof problem in postgres 10.2 by
somehow just declaring st_intersects() to be leakproof but that would
probably not work in an AWS RDS deployment. I will research the leakproof
issue more and see what options we may have in dealing with this problem.
Perhaps sometime in the future RLS won't break such queries but I
understand that is probably not an easy task.
Thanks for replying and helping me on my way.
Dennis
On Mon, Sep 19, 2022 at 7:28 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dennis White <dwhite@seawardmoon.com> writes:
> > Is there something I can do to allow users queries to use the index with
> a
> > condition like that used for the table owner's query?
>
> It looks like the problem in your badly-optimized query is that
> there is not an indexable condition being extracted from the
> ST_INTERSECTS() call. In the well-optimized one, we've got
>
> -> Index Scan using
> qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx...
> Index Cond: ((posit &&
> '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840E \
> E7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry)
> AND ...
> Filter: ((test.user_has_access(security_tag) = '1'::text) AND
> st_intersects(posit,
>
> '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840E \
> E7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))
>
> I presume what's happening there is that st_intersects() has got a support
> function that knows that "st_intersects(foo, bar)" implies "foo && bar"
> and the latter can be used with an index on foo.
>
> However, to do that in the presence of RLS we have to know that the
> extracted condition would be leakproof. I'm not sure that the geometry &&
> operator is leakproof in the first place; and even if it is, we might not
> consider this option unless st_intersects() is also marked leakproof,
> which most likely it isn't. You'd have to ask the PostGIS crew whether
> either of those things would be safe to consider leakproof ... but I'm
> betting they'll say that doing so would create an unreasonably large
> bug surface.
>
> By and large, the combination of RLS with complicated WHERE conditions
> is just deadly for performance, because most of the time we won't be
> able to use the WHERE conditions until after applying the RLS filter.
> Do you really need to use RLS in this application? If you're stuck
> doing so, you could maybe ameliorate things by implementing the RLS
> check functions in the fastest way you can, like writing C code
> for them.
>
> regards, tom lane
>
[Attachment #3 (text/html)]
<div dir="ltr"><div>Thanks. <br></div><div>As soon as I read your reply I recalled \
the leakproof issue from a discussion with a former colleague years ago.</div><div>At \
the time, I was new to Postgresql and I realize now I should have remembered \
that.<br></div><div><br></div><div>Disabling the RLS indeed resulted in the superior \
plan for the test_user.<br></div><div>The harder part will be baking the function \
call used for RLS into all query predicates rather than relying on RLS to do it for \
us.</div><div>I also recall that we got around the leakproof problem in postgres 10.2 \
by somehow just declaring st_intersects() to be leakproof but that would probably not \
work in an AWS RDS deployment. I will research the leakproof issue more and see what \
options we may have in dealing with this problem. Perhaps sometime in the future RLS \
won't break such queries but I understand that is probably not an easy \
task.<br></div><div>Thanks for replying and helping me on my \
way.</div><div><br></div><div>Dennis<br></div></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Mon, Sep 19, 2022 at 7:28 PM Tom Lane <<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">Dennis White <<a \
href="mailto:dwhite@seawardmoon.com" target="_blank">dwhite@seawardmoon.com</a>> \
writes:<br> > Is there something I can do to allow users queries to use the index \
with a<br> > condition like that used for the table owner's query?<br>
<br>
It looks like the problem in your badly-optimized query is that<br>
there is not an indexable condition being extracted from the<br>
ST_INTERSECTS() call. In the well-optimized one, we've got<br>
<br>
-> Index Scan using \
qtest_posit_t1_p2022_09_02_posit_toi_security_tag_idx...<br> Index Cond: ((posit \
&&<br> '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40 \
BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry) \
AND ...<br>
Filter: ((test.user_has_access(security_tag) = '1'::text) \
AND<br> st_intersects(posit,<br>
'0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE989484 \
0EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry))<br>
<br>
I presume what's happening there is that st_intersects() has got a support<br>
function that knows that "st_intersects(foo, bar)" implies "foo \
&& bar"<br> and the latter can be used with an index on foo.<br>
<br>
However, to do that in the presence of RLS we have to know that the<br>
extracted condition would be leakproof. I'm not sure that the geometry \
&&<br> operator is leakproof in the first place; and even if it is, we might \
not<br> consider this option unless st_intersects() is also marked leakproof,<br>
which most likely it isn't. You'd have to ask the PostGIS crew whether<br>
either of those things would be safe to consider leakproof ... but I'm<br>
betting they'll say that doing so would create an unreasonably large<br>
bug surface.<br>
<br>
By and large, the combination of RLS with complicated WHERE conditions<br>
is just deadly for performance, because most of the time we won't be<br>
able to use the WHERE conditions until after applying the RLS filter.<br>
Do you really need to use RLS in this application? If you're stuck<br>
doing so, you could maybe ameliorate things by implementing the RLS<br>
check functions in the fastest way you can, like writing C code<br>
for them.<br>
<br>
regards, tom lane<br>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic