Postgres Row Level Security policy optimizes poorly compared to inline version

Question

I have a query which looks like this:

 SELECT post.id, post.author_id, post.published_at, post.content
 FROM post
 WHERE post.group_id = 1
ORDER BY post.published_at DESC, post.id
 LIMIT 5;

This query has an index on (group_id, published_at DESC, id) which gives it this query plan when no Row Level Security (RLS) policies are used.

 Limit (cost=0.14..1.12 rows=5 width=143)
 -> Index Scan using post_published_at on post (cost=0.14..15.86 rows=80 width=143)
 Index Cond: (group_id = 1)

Then I add this policy:

CREATE POLICY select_member_of ON post FOR SELECT USING
 (EXISTS (SELECT 1
 FROM group_member
 WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
 group_member.group_id = post.group_id));

There is a compound primary key on group_member.account_id and group_member.group_id on the group_member table.

I expect Postgres to plan this query as an index-only scan of group_member since both group_member.account_id and group_member.group_id will be set to constant values. group_member.group_id should be constant because of the WHERE post.group_id = 1 condition in the SELECT query above.

Indeed it looks like this is happening when I inline my RLS policy into the query like this:

 SELECT id, author_id, published_at, content
 FROM post
 WHERE group_id = 1 AND
 (EXISTS (SELECT 1
 FROM group_member
 WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
 group_member.group_id = post.group_id))
ORDER BY published_at DESC, id
 LIMIT 5;

I get the query plan:

 Limit (cost=0.30..1.85 rows=5 width=143)
 -> Nested Loop Semi Join (cost=0.30..25.04 rows=80 width=143)
 -> Index Scan using post_published_at on post (cost=0.14..15.86 rows=80 width=147)
 Index Cond: (group_id = 1)
 -> Materialize (cost=0.16..8.19 rows=1 width=4)
 -> Index Only Scan using group_member_pkey on group_member (cost=0.16..8.18 rows=1 width=4)
 Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is what I was looking for. However, when I run my query with the real RLS policy the query plan becomes:

 Limit (cost=23.08..23.10 rows=5 width=143)
 -> Sort (cost=23.08..23.28 rows=80 width=143)
 Sort Key: post.published_at DESC, post.id
 -> Subquery Scan on post (cost=8.92..21.75 rows=80 width=143)
 -> Nested Loop Semi Join (cost=8.92..20.95 rows=80 width=147)
 -> Bitmap Heap Scan on post post_1 (cost=8.76..11.76 rows=80 width=147)
 Recheck Cond: (group_id = 1)
 -> Bitmap Index Scan on post_published_at (cost=0.00..8.74 rows=80 width=0)
 Index Cond: (group_id = 1)
 -> Materialize (cost=0.16..8.20 rows=1 width=4)
 -> Subquery Scan on group_member (cost=0.16..8.19 rows=1 width=4)
 -> Index Only Scan using group_member_pkey on group_member group_member_1 (cost=0.16..8.18 rows=1 width=8)
 Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is significantly worse.

Is this the expected behavior? Is there any way to get the same query plan for the version where I inlined my RLS policy?

• jjanes: Can you include the explain (analyze, buffers), not just explain? Also, what plan do you get with RLS if you set enable_bitmapscan=off ?
• Calebmer: @jjanes here are the queries with explain (analyze, buffers) and enable_bitmapscan=off. gist.github.com/calebmer/96c334a26e4f2350d067924e08b7b245 This is on Postgres 11. I believe my original query plans were from Postgres 9-ish.
• Colin 't Hart: I wonder of this is improved in the version of Postgres currently under development? They have changed the way CTEs work for example.
• Calebmer: Is it worth posting to the mailing list? I think what I want, here, is for Postgres to recognize that post.group_id is constant since the query includes WHERE post.group_id = 1. That way all that‘s needed is one index scan on group_member since both the group ID and account ID are constants in the query. It appears the inline version does this but not the RLS version.

Answer 1

I've been using RLS in production for a few years and my experience with it is that, despite being very useful, it's a bit of a leaky abstraction.

I believe this question as well as the most voted answer will help you understand why it's hard to make correct assumptions about query plans that involve tables with RLS, especially when JOINs are involved.

In short, RLS poses a problem for the query optimizer because certain optimizations could lead to data leakage, particularly when custom functions are used. So the optimizer takes a more defensive approach which sometimes results in worse query plans and poorer performance.

I haven't found a silver bullet solution for this problem, other than considering what's happening in a case-by-case situation and attempting to circumvent the problem.

I realize this is very generic comment and not the solution you're looking for, but I hope it still helps.

Answer 2

Without sample data it’s difficult to reproduce your exact scenario.

It seems to me you should make the expression in the policy as simple as possible. In your case that would be:

CREATE POLICY select_member_of ON post FOR SELECT USING (group_id IN (SELECT group_id
 FROM group_member
 WHERE account_id = current_setting('current_account_id', false)::INT));

• Calebmer: This doesn’t quite do what I’m looking for either. Really what I want to see is Postgres recognizing that post.group_id is a constant since I constrain it with post.group_id = 1 in the query so that the policy only needs to do an "index only scan" on the group_member primary key. It seems to recognize this fine when the condition is inline but not when the condition is in an RLS policy.