Re: GIST/GIN index not used with Row Level Security
| От | Derek Hans |
|---|---|
| Тема | Re: GIST/GIN index not used with Row Level Security |
| Дата | |
| Msg-id | CAGrP7a0RKh16LRX+HVgfMVTW3_pPvivnW-G1FZbP-boxRa6d=Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: GIST/GIN index not used with Row Level Security (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Список | pgsql-general |
What are the RLS policies on the table?
From select * from pg_policies:
"((tenant_name)::name = CURRENT_USER)"
What is the definition of the GIN index?
CREATE INDEX search__gist
ON public.search USING gist
(search COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE pg_default;
ON public.search USING gist
(search COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE pg_default;
Best guess is the RLS is preventing access to the field needed by the index.
I didn't realize RLS can limit access to a specific field/index - my understanding was that it only affects what rows get returned/can be update/inserted.
>
> select * from search where search like '%yo'
>
> Creates this query plan:
> "Seq Scan on search (cost=0.00..245.46 rows=1 width=163)"
> " Filter: (((tenant_name)::name = CURRENT_USER) AND (search ~~
> '%yo'::text))"
>
> Running this same query with the owner of the table, thereby disabling
> RLS, the index gets used as expected:
> "Bitmap Heap Scan on search (cost=4.49..96.33 rows=44 width=163)"
> " Recheck Cond: (search ~~ '%yo'::text)"
> " -> Bitmap Index Scan on search__gist (cost=0.00..4.48 rows=44 width=0)"
> " Index Cond: (search ~~ '%yo'::text)"
>
> I see the same behavior with more complex queries, switching to GIN
> index, more complex RLS rules, using word_similarity instead of like,
> using full text search and larger data sets (e.g. 100k rows). This is on
> PostgreSQL v11.1 on Windows 10.
>
> --
> *Derek*
> +1 (415) 754-0519 |derek.hans@gmail.com <mailto:derek.hans@gmail.com> |
> Skype: derek.hans
--
Adrian Klaver
adrian.klaver@aklaver.com
Derek
В списке pgsql-general по дате отправления: