Postgres Row Level Security policy optimizes poorly compared to inline version 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)
   -&gt;  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)
   -&gt;  Nested Loop Semi Join  (cost=0.30..25.04 rows=80 width=143)
         -&gt;  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=147)
               Index Cond: (group_id = 1)
         -&gt;  Materialize  (cost=0.16..8.19 rows=1 width=4)
               -&gt;  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)
   -&gt;  Sort  (cost=23.08..23.28 rows=80 width=143)
         Sort Key: post.published_at DESC, post.id
         -&gt;  Subquery Scan on post  (cost=8.92..21.75 rows=80 width=143)
               -&gt;  Nested Loop Semi Join  (cost=8.92..20.95 rows=80 width=147)
                     -&gt;  Bitmap Heap Scan on post post_1  (cost=8.76..11.76 rows=80 width=147)
                           Recheck Cond: (group_id = 1)
                           -&gt;  Bitmap Index Scan on post_published_at  (cost=0.00..8.74 rows=80 width=0)
                                 Index Cond: (group_id = 1)
                     -&gt;  Materialize  (cost=0.16..8.20 rows=1 width=4)
                           -&gt;  Subquery Scan on group_member  (cost=0.16..8.19 rows=1 width=4)
                                 -&gt;  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?
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));