Sample Header Ad - 728x90

Help understand why RLS significantly affects query performance

1 vote
0 answers
63 views
I have an RLS policy defined as:
create policy read_contacts on contacts
    as permissive
    for select
    using (company_id = (current_setting('nimble.company_id'::text))::bytea);
And such query:
explain (analyze, buffers, timing, verbose)
select count(*) as count_1
from contacts
where contacts.company_id = '\x4da17d7259ca1646db0dd074'
  and contacts.contact_type = 'person'
  and contacts.created between '2023-10-31 23:00:00.000000'::timestamp and '2024-08-15 22:59:59.000000'::timestamp
  and contacts.id in (select anon_1.contact_id
                      from (((select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('china')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('thailand')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('malaysia')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('indonesia')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('hong kong')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('viet nam'))
                             intersect all
                             ((select contacts_values.contact_id as contact_id
                               from contacts_values
                               where contacts_values.company_id = '\x4da17d7259ca1646db0dd074'
                                 and contacts_values.field_id = '\x618ab13630f1aae76bebaefa'
                                 and contacts_values.field_name = 'custom'
                                 and contacts_values.value__norm =
                                     nimble_field_value_normalize(nimble_skip_long_values('1')))))) as anon_1)
  and (contacts.id not in (select anon_2.contact_id
                           from (((select contacts_last_contacted.contact_id as contact_id
                                   from contacts_last_contacted
                                   where contacts_last_contacted.company_id = '\x4da17d7259ca1646db0dd074'))) as anon_2))
I noticed a significant difference in query plans and execution times when RLS is enabled versus when it is not. Here are the query plans for both cases: - [Query Plan with RLS](https://explain.depesz.com/s/2CXV) - [Query Plan without RLS](https://explain.depesz.com/s/TsIV) Additionally, I have this index:
create index concurrently idx_contacts_addresses_country
    on contacts__addresses (company_id, field_name, field_id,
                            nimble_skip_long_values(jsonb_extract_path_text(value__normalized, 'country')))
    where (nimble_skip_long_values(jsonb_extract_path_text(value__normalized, 'country')) is not null);
In the plan without RLS, this index is fully utilized ([Full Index Usage](https://explain.depesz.com/s/TsIV#l16)) :
Index Cond: 
   ((contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'::bytea) 
AND (contacts__addresses.field_name = 'address_work'::text) 
AND (contacts__addresses.field_id = '\x000000000000000000000000'::bytea) 
AND (CASE
         WHEN (length(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                              VARIADIC '{country}'::text[])) < 1024) THEN
             jsonb_extract_path_text(contacts__addresses.value__normalized,
                                     VARIADIC '{country}'::text[])
         ELSE NULL::text END = 'china'::text))
However, in the plan with RLS, the part involving contacts__addresses is not fully used ([Not Full Index Usage](https://explain.depesz.com/s/2CXV#l16)) :
Index Cond: 
   ((contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'::bytea) 
AND (contacts__addresses.field_name = 'address_work'::text) 
AND (contacts__addresses.field_id = '\x000000000000000000000000'::bytea))
Why does RLS cause such an effect? Also, any other insights or suggestions for optimization would be appreciated. I also suspect that creating a country__norm column, which would store the result of nimble_skip_long_values(jsonb_extract_path_text(value__normalized, 'country')), and indexing it like this:
create index concurrently idx_contacts_addresses_country
    on contacts__addresses (company_id, field_name, field_id, country__norm);
might make it easier for the planner to fully utilize the index. But this is just a hypothesis.
Asked by Mykola Shved (13 rep)
Sep 25, 2024, 03:29 PM
Last activity: Sep 30, 2024, 10:04 AM