與內聯版本相比，Postgres 行級安全策略優化不佳

我有一個如下所示的查詢：

 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;

此查詢有一個索引，(group_id, published_at DESC, id)當不使用行級安全 (RLS) 策略時，該索引為其提供此查詢計劃。

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)

然後我添加這個策略：

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));

桌子group_member.account_id上group_member.group_id有一個複合主鍵。group_member

我希望 Postgres 將此查詢計劃為僅索引掃描，group_member因為兩者都group_member.account_id將group_member.group_id設置為常量值。由於上面查詢中的條件，group_member.group_id應該是常量。WHERE post.group_id = 1``SELECT

事實上，當我將我的 RLS 策略內聯到這樣的查詢中時，似乎正在發生這種情況：

 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;

我得到查詢計劃：

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))

這就是我一直在尋找的。但是，當我使用真正的 RLS 策略執行查詢時，查詢計劃變為：

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))

這明顯更糟。

這是預期的行為嗎？有沒有辦法為我內聯我的 RLS 策略的版本獲得相同的查詢計劃？

如果沒有樣本數據，就很難重現您的確切場景。

在我看來，您應該使策略中的表達式盡可能簡單。在你的情況下，這將是：

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));

我已經在生產環境中使用 RLS 幾年了，我的經驗是，儘管它非常有用，但它有點抽象。

我相信這個問題以及投票最多的答案將幫助您理解為什麼很難對涉及帶有 RLS 的表的查詢計劃做出正確的假設，尤其是在涉及 JOIN 時。

簡而言之，RLS 給查詢優化器帶來了問題，因為某些優化可能導致數據洩漏，尤其是在使用自定義函式時。因此，優化器採取了更具防禦性的方法，有時會導致更差的查詢計劃和更差的性能。

我還沒有找到解決這個問題的靈丹妙藥，除了考慮在個案情況下發生的事情並試圖規避這個問題。

我意識到這是非常通用的評論，而不是您正在尋找的解決方案，但我希望它仍然有幫助。

引用自：https://dba.stackexchange.com/questions/232789