PostgreSQL 更新連接與 SQL Server 更新連接

我最近開始將一個個人項目從 Microsoft SQL Server 轉換為 PostgreSQL，我對UPDATE JOIN在兩個表之間進行操作時遇到的糟糕性能感到驚訝。

假設它們看起來像：

CREATE TABLE foo (
 id INTEGER NOT NULL PRIMARY KEY,
 bar INTEGER NULL
);

CREATE TABLE foo2 (
 id INTEGER NOT NULL PRIMARY KEY,
 bar INTEGER NULL
);

在 T-SQL 中，我會使用類似這樣的連接進行更新：

UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

但是在 Postgres 中執行，查詢速度非常慢。

如果我將其更改為：

UPDATE foo
SET bar = t2.bar
FROM foo2 t2
WHERE foo.id = t2.id;

這算不上問題。

我知道語法不同，但我希望查詢優化器能在同一個球場上解決問題。相反，事情變得一團糟。除了語法差異之外，我看不到的兩個查詢之間是否存在細微差別？

解釋計劃

Update on foo  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
 ->  Nested Loop  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
       ->  Seq Scan on foo  (cost=0.00..145721.10 rows=582410 width=1010)
       ->  Materialize  (cost=85852.43..247935.91 rows=580908 width=17)
             ->  Hash Join  (cost=85852.43..241627.37 rows=580908 width=17)
                   Hash Cond: (t1.id = t2.id)
                   ->  Seq Scan on foo t1  (cost=0.00..145721.10 rows=582410 width=10)
                   ->  Hash  (cost=75754.08..75754.08 rows=580908 width=15)
                         ->  Seq Scan on foo2 t2  (cost=0.00..75754.08 rows=580908 width=15)

Update on foo (cost=87575.47..535974.25 rows=581621 width=1022)
 ->  Hash Join  (cost=87575.47..535974.25 rows=581621 width=1022)
       Hash Cond: (foo.id = t2.id)
       ->  Seq Scan on foo (cost=0.00..151301.17 rows=1140417 width=1011)
       ->  Hash  (cost=75761.21..75761.21 rows=581621 width=36)
             ->  Seq Scan on foo2 t2  (cost=0.00..75761.21 rows=581621 width=36)

但是在 Postgres 中執行，查詢速度非常慢。

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id;

foo和之間沒有連接條件t1，隱式CROSS JOIN強制笛卡爾積，即O(N²)(!) 更新操作而不是O(N). 結果是不確定的廢話。這種效果在查詢計劃中也很明顯：rows=338326628280而不是rows=581621（另外：兩個計劃都是根據稍微不同的表生成的，但這似乎與問題無關。）

可以通過添加如下連接條件來修復：

UPDATE foo
SET    bar = t2.bar
FROM   foo t1
JOIN   foo2 t2 ON t1.id = t2.id
WHERE  foo.id = t1.id;  -- !

好吧，從技術上講，這是一個WHERE條件，但都是一樣的。

但這只是在豬身上塗口紅。雖然id是每個表的 PK 列，但這只是增加了噪音。請改用您已經找到的命令：

UPDATE foo
SET    bar = t2.bar
FROM   foo2 t2
WHERE  foo.id = t2.id;

該手冊建議使用以下FROM條款UPDATE：

不要將目標表重複為 a from_item，除非您打算進行自聯接（在這種情況下，它必須在 中出現別名from_item）。

和：

當存在FROM子句時，實質上發生的情況是目標表連接到列表中提到的from_item表，連接的每個輸出行代表目標表的更新操作。使用時，FROM您應確保連接最多為要修改的每一行生成一個輸出行。換句話說，目標行不應連接到來自其他表的多個行。如果是這樣，那麼只有一個連接行將用於更新目標行，但將使用哪一個是不容易預測的。

LEFT [OUTER] JOIN如果您需要額外的表，這樣的自連接是有意義的（甚至是必要的！ ）。可悲的是，SQL 中沒有規定可以*"FROM LEFT"*在UPDATE. 例子：

• 如果子查詢返回空，則取消更新中的列

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