Re: GIST/GIN index not used with Row Level Security

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(dot)hans(at)gmail(dot)com <mailto:derek(dot)hans(at)gmail(dot)com> |
> Skype: derek.hans

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com