[Solved] Postgres Row Level Security policy optimizes poorly compared to inline version

EverSQL Database Performance Knowledge Base

Postgres Row Level Security policy optimizes poorly compared to inline version

Database type:

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?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX post_idx_group_id_published_id ON "post" ("group_id","published_at" desc,"id");
CREATE INDEX post_idx_published_at_id ON "post" ("published_at" desc,"id");
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.