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.
That's RLS kicking in - RLS condition is defined as
((tenant_name)::name = CURRENT_USER)
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 didn't realize you could set access to table statistics. How do I enable this access for this user? If that's not possible, it sounds like it effectively blocks the use of GIN/GIST indexes when RLS is in use.
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.
I've tried this with larger data sets, with the same results. I discovered this problem because the select was taking 10-30 seconds instead of the expected sub-second, when using larger data sets and more fields getting searched. The example is the simplest repro case I could create.
regards, tom lane
Derek
