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

Derek Hans <derek(dot)hans(at)gmail(dot)com> writes:
> 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:

Your example is obscuring the issue by incorporating a tenant_name
condition (where did that come from, anyway?) in one case and not
the other. Without knowing how selective that is, it's hard to
compare the EXPLAIN results.

However, wild-guess time: it might be that without access to the
table statistics, the "search like '%yo'" condition is estimated
to be too unselective to make an indexscan profitable. And putting
RLS in the way would disable that access if the ~~ operator is not
marked leakproof, which it isn't.

I'm not sure that you should get too excited about this, however.
You're evidently testing on a toy-size table, else the seqscan
cost estimate would be a lot higher. With a table large enough
to make it really important to guess right, even the default
selectivity estimate might be enough to get an indexscan.

regards, tom lane