When using row level security, GIN and GIST indexes appear to get ignored. Is this expected behavior? Can I change the query to get PostgreSQL using the index? For example, with RLS enabled, this query:
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.h...@gmail.com | Skype: derek.hans
