4

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?

  • Can you include the explain (analyze, buffers), not just explain? Also, what plan do you get with RLS if you set enable_bitmapscan=off ? – jjanes May 4 at 11:41
  • @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. – Calebmer May 6 at 17:50
  • 1
    I wonder of this is improved in the version of Postgres currently under development? They have changed the way CTEs work for example. – Colin 't Hart May 6 at 19:17
  • 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. – Calebmer May 7 at 18:09
1

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));
  • 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. – Calebmer May 6 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.