Last active
March 3, 2020 20:16
-
-
Save a-mckinley/d98fec0fb48a1b8eea3adc526981fb5b to your computer and use it in GitHub Desktop.
Testing table with jsonb partial indexes, does not choose an index scan in all rls cases
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Alastair McKinley 2020 | |
begin; | |
create table testing as | |
select jsonb_build_object('value',value) as data, | |
type_id, | |
created, | |
case when row_number() over (partition by type_id,value order by created desc ) = 1 | |
then true | |
else false | |
end as latest | |
from ( | |
select (random()*(g%200))::bigint as value, | |
(random()*(g%40))::bigint as type_id, | |
clock_timestamp() as created | |
from generate_series(1,3000000) g | |
) s; | |
create index type_id_i on testing (type_id) where latest is true; | |
create unique index i_1 on testing ((data->'value')) where type_id = 1 and latest is true; | |
create unique index i_2 on testing ((data->'value')) where type_id = 2 and latest is true; | |
create unique index i_3 on testing ((data->'value')) where type_id = 3 and latest is true; | |
create unique index i_4 on testing ((data->'value')) where type_id = 4 and latest is true; | |
create unique index i_5 on testing ((data->'value')) where type_id = 5 and latest is true; | |
create unique index i_6 on testing ((data->'value')) where type_id = 6 and latest is true; | |
create unique index i_7 on testing ((data->'value')) where type_id = 7 and latest is true; | |
create unique index i_8 on testing ((data->'value')) where type_id = 8 and latest is true; | |
create unique index i_9 on testing ((data->'value')) where type_id = 9 and latest is true; | |
create unique index i_10 on testing ((data->'value')) where type_id = 10 and latest is true; | |
create unique index i_11 on testing ((data->'value')) where type_id = 11 and latest is true; | |
create unique index i_12 on testing ((data->'value')) where type_id = 12 and latest is true; | |
create unique index i_13 on testing ((data->'value')) where type_id = 13 and latest is true; | |
create unique index i_14 on testing ((data->'value')) where type_id = 14 and latest is true; | |
create unique index i_15 on testing ((data->'value')) where type_id = 15 and latest is true; | |
create unique index i_16 on testing ((data->'value')) where type_id = 16 and latest is true; | |
create unique index i_17 on testing ((data->'value')) where type_id = 17 and latest is true; | |
create unique index i_18 on testing ((data->'value')) where type_id = 18 and latest is true; | |
create unique index i_19 on testing ((data->'value')) where type_id = 19 and latest is true; | |
create unique index i_20 on testing ((data->'value')) where type_id = 20 and latest is true; | |
create unique index i_21 on testing ((data->'value')) where type_id = 21 and latest is true; | |
create unique index i_22 on testing ((data->'value')) where type_id = 22 and latest is true; | |
create unique index i_23 on testing ((data->'value')) where type_id = 23 and latest is true; | |
create unique index i_24 on testing ((data->'value')) where type_id = 24 and latest is true; | |
create unique index i_25 on testing ((data->'value')) where type_id = 25 and latest is true; | |
create unique index i_26 on testing ((data->'value')) where type_id = 26 and latest is true; | |
create unique index i_27 on testing ((data->'value')) where type_id = 27 and latest is true; | |
create unique index i_28 on testing ((data->'value')) where type_id = 28 and latest is true; | |
create unique index i_29 on testing ((data->'value')) where type_id = 29 and latest is true; | |
create unique index i_30 on testing ((data->'value')) where type_id = 30 and latest is true; | |
create unique index i_31 on testing ((data->'value')) where type_id = 31 and latest is true; | |
create unique index i_32 on testing ((data->'value')) where type_id = 32 and latest is true; | |
create unique index i_33 on testing ((data->'value')) where type_id = 33 and latest is true; | |
create unique index i_34 on testing ((data->'value')) where type_id = 34 and latest is true; | |
create unique index i_35 on testing ((data->'value')) where type_id = 35 and latest is true; | |
create unique index i_36 on testing ((data->'value')) where type_id = 36 and latest is true; | |
create unique index i_37 on testing ((data->'value')) where type_id = 37 and latest is true; | |
create unique index i_38 on testing ((data->'value')) where type_id = 38 and latest is true; | |
create unique index i_39 on testing ((data->'value')) where type_id = 39 and latest is true; | |
create unique index i_40 on testing ((data->'value')) where type_id = 40 and latest is true; | |
drop role if exists testing_user; | |
create role testing_user; | |
grant select on testing to testing_user; | |
alter table testing enable row level security; | |
create policy testing_s ON testing for select to testing_user using ( | |
true | |
); | |
-- without rls, this uses an index scan as expected | |
explain (analyze) select * from testing where data->'value' = to_jsonb(10) and type_id = 10 and latest is true; | |
set role testing_user; | |
-- with using (true) rls policy, this uses an index scan as expected | |
explain (analyze) select * from testing where data->'value' = to_jsonb(10) and type_id = 10 and latest is true; | |
set role postgres; | |
alter policy testing_s ON testing to testing_user using ( | |
(select true) | |
); | |
set role testing_user; | |
-- with using ( select (true )) rls policy, this chooses a bitmap index scan instead of an index scan | |
explain (analyze) select * from testing where data->'value' = to_jsonb(10) and type_id = 10 and latest is true; | |
commit; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment