Hi all,
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:
postgres=# create policy testing_s ON testing for select to testing_user using (
postgres(# true
postgres(# );
postgres=# set role testing_user;
SET
postgres=> explain (analyze) select * from testing where data->'value' =
to_jsonb(10) and type_id = 10 and latest is true;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Index Scan using i_10 on testing (cost=0.15..8.17 rows=1 width=49) (actual
time=0.007..0.008 rows=1 loops=1)
Index Cond: ((data -> 'value'::text) = to_jsonb(10))
Planning Time: 0.221 ms
Execution Time: 0.017 ms
(4 rows)
postgres=# alter policy testing_s ON testing to testing_user using (
postgres(# (select true)
postgres(# );
postgres=> explain (analyze) select * from testing where data->'value' =
to_jsonb(10) and type_id = 10 and latest is true;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testing (cost=9.16..17582.89 rows=1 width=49) (actual
time=0.088..0.877 rows=1 loops=1)
Recheck Cond: ((type_id = 10) AND (latest IS TRUE))
Filter: ($0 AND ((data -> 'value'::text) = to_jsonb(10)))
Rows Removed by Filter: 199
Heap Blocks: exact=185
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.000..0.000
rows=1 loops=1)
-> Bitmap Index Scan on i_10 (cost=0.00..9.14 rows=7500 width=0) (actual
time=0.012..0.012 rows=200 loops=1)
Planning Time: 0.306 ms
Execution Time: 0.894 ms
(10 rows)
Tested on PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
My two full tests cases are linked below, the first works as expected, the
second produces a bad plan:
https://gist.github.com/a-mckinley/94a4ada1e40bf79e134a90349cd2a380
https://gist.github.com/a-mckinley/d98fec0fb48a1b8eea3adc526981fb5b
This problem seems to make row level security unusable for me, I am missing
something in regards to RLS and indexes on jsonb?
Alastair
