On 8/13/19 11:57 AM, Derek Hans wrote:
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:
What are the RLS policies on the table?
What is the definition of the GIN index?
Best guess is the RLS is preventing access to the field needed by the index.
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
