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
Last activity: Sep 30, 2024, 10:04 AM