From: | Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script) |
Date: | 2020-03-04 00:37:41 |
Message-ID: | DB6PR0201MB23415D13CB0F5BDDEC8923FCE3E50@DB6PR0201MB2341.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Tom,
Thank you for having a look at this. In the interim I discovered that I could trigger the issue by creating a security barrier view, whereas a regular view worked fine, so I think that also points to your conclusion about leakyness?
I attempted to workaround the issue with a leakproof function, so far with no success.
I tried a leakproof function as below:
create or replace function jsonb_select(obj jsonb,keys text[]) returns jsonb as
$$
select jsonb_agg(value order by key) from jsonb_each(obj) where keys @> ARRAY[key];
$$ language sql immutable strict leakproof;
And created the expression indexes:
create unique index i_10 on testing ((jsonb_select(data,'{value}'))) where type_id = 10 and latest is true;
But my query still produces a bad plan (bitmap heap scan) with rls or a security barrier view enabled:
explain (analyze) select * from testing where (jsonb_select(data,'{value}')) = to_jsonb(10) and type_id = 10 and latest is true;
Is this an approach that could fundamentally work?
Best regards,
Alastair
________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: 04 March 2020 00:04
To: Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)
Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)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 several seconds 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, illustrated below:
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-03-04 04:22:39 | Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script) |
Previous Message | Tom Lane | 2020-03-04 00:04:40 | Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script) |