From: | Derek Hans <derek(dot)hans(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: GIST/GIN index not used with Row Level Security |
Date: | 2019-08-13 19:31:25 |
Message-ID: | CAGrP7a3JcEorwM98OGn7s-TZ1os8rubCqeEXLgqeZrvX4LfwvQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
>
> 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*
+1 (415) 754-0519 | derek(dot)hans(at)gmail(dot)com | Skype: derek.hans
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-08-13 19:48:36 | Re: GIST/GIN index not used with Row Level Security |
Previous Message | Derek Hans | 2019-08-13 19:26:01 | Re: GIST/GIN index not used with Row Level Security |