postgresql – Postgres row-level security policy optimizes poorly compared to the online version

I have a query that looks like this:

        SELECT post.id, post.author_id, post.published_at, post.content
SINCE publication
WHERE post.group_id = 1
ASK FOR post.published_at DESC, post.id
LIMIT 5;

This query has an index on (group_id, published_at DESC, id) what this query plan gives you when row-level Security (RLS) policies are not used.

    Limit (cost = 0.14..1.12 rows = 5 width = 143)
-> Index scan using post_published_at in publication (cost = 0.14..15.86 rows = 80 width = 143)
Cond Index: (group_id = 1)

Then I add this policy:

CREATE POLICY select_member_of ON the publication TO SELECT THE USE
(EXISTS (SELECT 1
FROM group_member
WHERE group_member.account_id = current_setting (& # 39; current_account_id & # 39 ;, false) :: INT AND
group_member.group_id = post.group_id));

There is a primary key composed in group_member.account_id Y group_member.group_id about him group member table.

I hope that Postgres will plan this query as a single index scan group member since both group_member.account_id Y group_member.group_id it will be established in constant values. group_member.group_id should be constant due to the WHERE post.group_id = 1 condition in the SELECT query above

In fact, it seems that this is happening when I insert my RLS policy in the query in this way:

        SELECT id, author_id, publish_at, content
SINCE publication
WHERE group_id = 1 AND
(EXISTS (SELECT 1
FROM group_member
WHERE group_member.account_id = current_setting (& # 39; current_account_id & # 39 ;, false) :: INT AND
group_member.group_id = post.group_id))
ORDER BY PUBLISHED IN DESC, ID
LIMIT 5;

I get the consultation plan:

    Limit (cost = 0.30..1.85 rows = 5 width = 143)
-> Semi Join of Nested Loop (cost = 0.30..25.04 rows = 80 width = 143)
-> Index scan using post_published_at in publication (cost = 0.14..15.86 rows = 80 width = 147)
Cond Index: (group_id = 1)
-> Materialize (cost = 0.16..8.19 rows = 1 width = 4)
-> Index-only exploration using group_member_pkey in group_member (cost = 0.16..8.18 rows = 1 width = 4)
Cond Index: ((account_id = (current_setting (& # 39; current_account_id & # 39; :: text, false)) :: integer) AND (group_id = 1))

What I was looking for However, when I execute my query with the actual 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)
Classification key: post.published_at DESC, post.id
-> Exploration of subqueries in the publication (cost = 8.92..21.75 rows = 80 width = 143)
-> Semi Join from Nested Loop (cost = 8.92..20.95 rows = 80 width = 147)
-> Exploration of the bitmap heap in the post_1 post (cost = 8.76..11.76 rows = 80 width = 147)
Recheck Cond: (group_id = 1)
-> Exploration of bitmap index in post_published_at (cost = 0.00..8.74 rows = 80 width = 0)
Cond Index: (group_id = 1)
-> Materialize (cost = 0.16..8.20 rows = 1 width = 4)
-> Exploration of subqueries in group_member (cost = 0.16..8.19 rows = 1 width = 4)
-> Index scan only using group_member_pkey in group_member group_member_1 (cost = 0.16..8.18 rows = 1 width = 8)
Cond Index: ((account_id = (current_setting (& # 39; current_account_id & # 39; :: 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 in which I registered my RLS policy?