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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>