Testing table with partial indexes, uses index scan in all rls cases

ok.sql

-- Alastair McKinley 2020

begin;

create table testing as
select value,
    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 (value) where type_id = 1 and latest is true;
create unique index i_2 on testing (value) where type_id = 2 and latest is true;
create unique index i_3 on testing (value) where type_id = 3 and latest is true;
create unique index i_4 on testing (value) where type_id = 4 and latest is true;
create unique index i_5 on testing (value) where type_id = 5 and latest is true;
create unique index i_6 on testing (value) where type_id = 6 and latest is true;
create unique index i_7 on testing (value) where type_id = 7 and latest is true;
create unique index i_8 on testing (value) where type_id = 8 and latest is true;
create unique index i_9 on testing (value) where type_id = 9 and latest is true;
create unique index i_10 on testing (value) where type_id = 10 and latest is true;
create unique index i_11 on testing (value) where type_id = 11 and latest is true;
create unique index i_12 on testing (value) where type_id = 12 and latest is true;
create unique index i_13 on testing (value) where type_id = 13 and latest is true;
create unique index i_14 on testing (value) where type_id = 14 and latest is true;
create unique index i_15 on testing (value) where type_id = 15 and latest is true;
create unique index i_16 on testing (value) where type_id = 16 and latest is true;
create unique index i_17 on testing (value) where type_id = 17 and latest is true;
create unique index i_18 on testing (value) where type_id = 18 and latest is true;
create unique index i_19 on testing (value) where type_id = 19 and latest is true;
create unique index i_20 on testing (value) where type_id = 20 and latest is true;
create unique index i_21 on testing (value) where type_id = 21 and latest is true;
create unique index i_22 on testing (value) where type_id = 22 and latest is true;
create unique index i_23 on testing (value) where type_id = 23 and latest is true;
create unique index i_24 on testing (value) where type_id = 24 and latest is true;
create unique index i_25 on testing (value) where type_id = 25 and latest is true;
create unique index i_26 on testing (value) where type_id = 26 and latest is true;
create unique index i_27 on testing (value) where type_id = 27 and latest is true;
create unique index i_28 on testing (value) where type_id = 28 and latest is true;
create unique index i_29 on testing (value) where type_id = 29 and latest is true;
create unique index i_30 on testing (value) where type_id = 30 and latest is true;
create unique index i_31 on testing (value) where type_id = 31 and latest is true;
create unique index i_32 on testing (value) where type_id = 32 and latest is true;
create unique index i_33 on testing (value) where type_id = 33 and latest is true;
create unique index i_34 on testing (value) where type_id = 34 and latest is true;
create unique index i_35 on testing (value) where type_id = 35 and latest is true;
create unique index i_36 on testing (value) where type_id = 36 and latest is true;
create unique index i_37 on testing (value) where type_id = 37 and latest is true;
create unique index i_38 on testing (value) where type_id = 38 and latest is true;
create unique index i_39 on testing (value) where type_id = 39 and latest is true;
create unique index i_40 on testing (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 value = 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 value = 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 uses an index scan as expected
explain (analyze) select * from testing where value = 10 and type_id = 10 and latest is true;

commit;