Re: GIST combo index condition chosen for users queries is different from table owner's query

Dennis White <dwhite(at)seawardmoon(dot)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