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@xxxxxxxxx <mailto:derek.hans@xxxxxxxxx> |
> Skype: derek.hans
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
Derek
