Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

Alastair McKinley <a.mckinley@analyticsengines.com> writes:
> I have recently encountered a strange poor query plan choice after implementing RLS.
> My table has a number of partial indexes on a jsonb column and the query went from low number of milliseconds to
severalseconds as the planner chose a different index. 
> Simply stated, in the jsonb column case, "using ( (select true) )" instead of "using (true)" produces a bad plan,
illustratedbelow: 

If the planner isn't sure you have access to all rows in the table,
that disables some of its ability to estimate where-clause selectivity.
In particular it can't run "leaky" where-clauses against all values in
the table's statistics entries to see how many pass, because a nefarious
user could use that to glean info about what's in the table.  Eyeing your
test query, it looks like the issue is that jsonb "->" isn't leakproof,
so that clause falls back to a default selectivity estimate, and you
get a bad plan as a result.

            regards, tom lane