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 <t...@sss.pgh.pa.us> wrote: > Dennis White <dwh...@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 && > '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::geometry) > AND ... > Filter: ((test.user_has_access(security_tag) = '1'::text) AND > st_intersects(posit, > > '0103000020E610000001000000050000002C9B3924B5EE504091F3FE3F4E782F40BEA25BAFE9894840EE7C3F355EFA23C0F47002D369FF434019A9F7544EDB0FC0BE88B663EACE4940AB08371955FA31402C9B3924B5EE504091F3FE3F4E782F40'::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 >
