CREATE INDEX search__gist 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