如何防止 PostgreSQL 重寫 OUTER JOIN 查詢？

我的查詢是：

SELECT  Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2
FROM    tableA LEFT JOIN
           (tableB FULL JOIN tableC ON (Bcol1 = Ccol1))
           ON (Acol1 = Bcol1)

EXPLAIN ANALYZE給我：

                                                     QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
Hash Right Join  (cost=99.65..180.45 rows=1770 width=24) (actual time=0.043..0.103 rows=3 loops=1)
  Hash Cond: (tableb.bcol1 = tablea.acol1)
  ->  Hash Left Join  (cost=49.83..104.08 rows=1770 width=16) (actual time=0.011..0.062 rows=3 loops=1)
        Hash Cond: (tableb.bcol1 = tablec.ccol1)
        ->  Seq Scan on tableb  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.001..0.002 rows=3 loops=1)
        ->  Hash  (cost=27.70..27.70 rows=1770 width=8) (actual time=0.004..0.004 rows=3 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 1kB
              ->  Seq Scan on tablec  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.001..0.002 rows=3 loops=1)
  ->  Hash  (cost=27.70..27.70 rows=1770 width=8) (actual time=0.014..0.014 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 1kB
        ->  Seq Scan on tablea  (cost=0.00..27.70 rows=1770 width=8) (actual time=0.009..0.011 rows=3 loops=1)
Total runtime: 0.151 ms

Postgres 將和之間的完整外連接更改為右外連接，因為稍後左外連接無論如何都會消除空值。它等同於原始查詢。tableB``tableC``tableA

但是，我正在破解 Postgres 以實現我的連接列舉相關算法並進行實驗。我不希望 Postgres 將完整的外連接更改為左外連接。有沒有辦法這樣做？

您可以為您的目的引入優化障礙。

前言

對於這個問題，普通EXPLAIN（沒有）就足夠了。ANALYZE表達式周圍的括號ON只是噪音。添加表別名是明確的。

我們確實看到了完全連接本身的**“完全連接” ：**

SELECT * FROM tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1;

我們可以用子查詢重寫全連接：

SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
  SELECT *  -- sort out conflicting names with aliases
  FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
  ) d ON a.Acol1 = d.Bcol1;

您必須在子查詢中使用別名對沖突的名稱進行排序 - 但話又說回來，無論如何，您都需要在外部SELECT為基礎表中的多個同名列執行此操作。

由於子查詢不會施加優化障礙，因此查詢仍會作為一個整體進行優化。您仍然會看到**“Left Join”或“Right Join”**。但是，我們可以擴展此表單以得出解決方案：

解決方案 1. OFFSET 0hack（未記錄）

EXPLAIN
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN (
  SELECT *  -- you'll have to sort out conflicting names with aliases
  FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
  **OFFSET 0**  -- undocumented hack
  ) d ON a.Acol1 = d.Bcol1;

您將看到**“完全加入”**。

**為什麼？**一旦子查詢使用OFFSET子句，查詢計劃器/優化器就會單獨計劃子查詢。OFFSET 0是邏輯噪音，但 Postgres 仍然考慮使該子句成為有效實現子查詢的查詢提示的子句。（儘管 Postgres 不支持查詢提示。）這是一個備受爭議的問題。有關的：

• 如何防止 Postgres 內聯子查詢？
• http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

解決方案 2. 使用 CTE（已記錄）

EXPLAIN
WITH cte AS MATERIALIZED ( -- requires "MATERIALIZED" in Postgres 12 or later!
  SELECT *  -- you'll have to sort out conflicting names with aliases
  FROM   tableB b FULL JOIN tableC c ON b.Bcol1 = c.Ccol1
  ) 
SELECT a.Acol1, a.Acol2, d.Bcol1, d.Bcol2, d.Ccol1, d.Ccol2
FROM   tableA a
LEFT   JOIN cte d ON a.Acol1 = d.Bcol1;

您還會看到**“完全加入”**。

Postgres 11 的手冊（之前AS MATERIALIZED添加）：

查詢的一個有用屬性WITH是每次執行父查詢時它們只被評估一次，即使它們被父查詢或同級WITH查詢多次引用。因此，可以將在多個地方需要的昂貴計算放在 WITH 查詢中以避免冗餘工作。另一個可能的應用是防止對具有副作用的函式進行不必要的多次評估。然而，這枚硬幣的另一面是，與普通子查詢相比，優化器將限制從父查詢向下推到 WITH 查詢的能力較差。查詢通常會被評估為已寫入，而不會抑制父查詢之後可能丟棄的行**。WITH**（但是，如上所述，如果對查詢的引用只需要有限數量的行，則評估可能會提前停止。）

從Postgres 12 開始，手冊增加了：

但是，如果WITH查詢是非遞歸且無副作用的（即，它不SELECT包含易變函式），則可以將其折疊到父查詢中，從而允許兩個查詢級別的聯合優化。預設情況下，如果父查詢WITH僅引用一次查詢，則會發生這種情況，但如果它多次引用查詢，則不會發生這種情況WITH。**MATERIALIZED您可以通過指定強制單獨計算WITH查詢或指定NOT MATERIALIZED**強制將其合併到父查詢中來覆蓋該決定 。後一種選擇有重複計算查詢的風險 ，但如果每次使用查詢只需要查詢完整輸出的一小部分，WITH它仍然可以節省淨成本 。WITH``WITH

大膽強調我的。

db<>fiddle here

舊sqlfiddle

嘗試修復程式碼

SELECT  Acol1, Acol2, K.D.Bcol1, K.D.Bcol2, K.D.Ccol1, K.D.Ccol2  
FROM tableA LEFT JOIN  
( SELECT  D.Bcol1, D.Bcol2, D.Ccol1, D.Ccol2  
 FROM (tableB FULL JOIN   
tableC ON  
Bcol1 = Ccol1 ) D ) K  
ON Acol1 = K.D.Bcol1    

我使用KD來指示數據流。

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