PostgreSQL 9.6 中不受歡迎的 Nest Loop 與 Hash Join

我在 PostgreSQL 9.6 查詢計劃方面遇到了麻煩。我的查詢如下所示：

SET role plain_user;

SELECT properties.*
FROM properties
JOIN entries_properties
 ON properties.id = entries_properties.property_id
JOIN structures
 ON structures.id = entries_properties.entry_id 
WHERE structures."STRUKTURBERICHT" != ''
 AND properties."COMPOSITION" LIKE 'Mo%'
 AND (
   properties."NAME" LIKE '%VASP-ase-preopt%'
   OR properties."CALCULATOR_ID" IN (7,22,25)
 )
AND properties."TYPE_ID" IN (6)

我為上述使用的表啟用了行級安全性。

• ，set enable_nestloop = True查詢計劃器執行嵌套循環連接，總執行時間約為 37 秒：https ://explain.depesz.com/s/59BR
• with set enable_nestloop = False，使用Hash Join方式，查詢時間約0.3秒： https ://explain.depesz.com/s/PG8E

我VACUUM ANALYZE在執行查詢之前做過，但沒有幫助。

我知道這不是一個好習慣set enable_nestloop = False，以及規劃師的任何其他類似選項。但是我怎麼能“說服”計劃者在不禁用嵌套循環的情況下使用雜湊連接呢？

重寫查詢是一種選擇。

如果我在繞過 RLS 的角色下執行相同的查詢，那麼它的執行速度非常快。行級安全策略如下所示：

CREATE POLICY properties_select
ON properties
FOR SELECT
USING (
 (
   properties.ouid = get_current_user_id()
   AND properties.ur
 )
 OR (
   properties.ogid in (select get_current_groups_id())
   AND properties.gr
 )
 OR properties.ar
);

任何想法或建議將不勝感激。

這裡發生的事情是嵌套循環在一側是很遠的。當一側非常小時，例如返回一行時，嵌套循環*非常有效。*在您的查詢中，規劃器在這裡摸索並估計 Hash Join 將只返回一行。相反，該 Hash Join (property_id = id) 返回 1,338 行。這會強制 1,338 個循環在已經有 3,444 行的嵌套循環的另一側執行。當您只期待一個（這甚至不是一個“循環”）時，這真是太棒了。反正..

隨著我們向下移動的進一步檢查表明，Hash Join 確實被由此產生的估計所困擾，

Filter: (((properties."COMPOSITION")::text ~~ 'Mo%'::text) AND (((properties."NAME")::text ~~ '%VASP-ase-preopt%'::text) OR (properties."CALCULATOR_ID" = ANY ('{7,22,25}'::integer[]))))

PostgreSQL 期望返回一行。但事實並非如此。而且，這確實是你的問題。所以這裡有一些選項，不涉及取出大錘和禁用nested_loop

• 您可以添加一個或兩個索引properties來幫助它可能完全跳過 seq 掃描，或者更好地估計返回。

CREATE INDEX ON properties USING ( "TYPE_ID", "CALCULATOR_ID" );
-- the gist_trgm_ops may or may not be needed depending on selectivity of above.
CREATE INDEX ON properties USING GIST (
 "COMPOSITION" gist_trgm_ops,
 "NAME"        gist_trgm_ops
);
ANALYZE properties;

• 或者，您可以將屬性內容移動到 CTE 或子選擇中，OFFSET 0從而創建圍欄。

WITH t AS (
 SELECT *
 FROM properties.
 WHERE "COMPOSITION" LIKE 'Mo%'
 AND (
   "NAME" LIKE '%VASP-ase-preopt%'
   OR "CALCULATOR_ID" IN (7,22,25)
 )
 AND "TYPE_ID" IN (6)
)
SELECT * FROM structures
JOIN t ON (
 structures.id = entries_properties.entry_id
)

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