SQL 更新与连接表 : Out of Memory Error

Question

我正在尝试更新 pSQL 中的一个表并遇到各种内存/执行错误。

奇怪的是，支持更新的 SELECT 查询非常快。我敢肯定，我只是没有真正了解幕后发生的事情。

一些上下文。

---

相关表格

address_book:
loan_id,
county,
zip
---
loan:
id
---
loan_property:
loan_id,
property_id
---
property:
id,
zip,
county

目标

目标是用 address_book 中的值更新属性表的 zip 和 county。 address_book 有一个 loan_id，它是对 property 的连接。

---

SQL

让我们看一个简单的SELECT

WITH ab AS (
SELECT DISTINCT
    left(ab.loan_id, 6) AS loan_id,
    ab.zip AS zip,
    ab.county AS county
FROM 
    address_book ab
WHERE
    ab.address IS NOT NULL
)

SELECT ab.county, p.name

FROM property p
INNER JOIN loan_property lp ON lp.property_id = p.id
INNER JOIN loan           l ON lp.loan_id     = l.id
INNER JOIN               ab ON ab.loan_id     = l.id
WHERE 
    l.id = ab.loan_id

这非常有效并且非常快(约 10k 条记录 0.4 秒)

让我们把上面的内容变成一个 UPDATE 调用:

WITH ab AS (
SELECT DISTINCT
    left(ab.loan_id, 6) AS loan_id,
    ab.zip AS zip,
    ab.county AS county
FROM 
    address_book ab
WHERE
    ab.address IS NOT NULL
)

UPDATE property
SET zip=ab.zip, county=ab.county

FROM property p
INNER JOIN loan_property lp ON lp.property_id = p.id
INNER JOIN loan           l ON lp.loan_id     = l.id
INNER JOIN               ab ON ab.loan_id     = l.id
WHERE 
    l.id = ab.loan_id

此更新运行 2 分钟，然后通常会失败，基于

SQL Error [53200]: ERROR: out of memory

是否有更优化的方式来运行此更新？即使我必须按 LIMIT/OFFSET 进行批处理或将 SELECT 结果保存到表中，然后直接从该表执行更新 - 运行此更新而不遇到内存错误的方法是什么？

非常感谢大家!

Answer 1

As documented in the manual 不要在 UPDATE 语句中重复目标表:

...
UPDATE property
   SET zip = ab.zip, 
       county = ab.county
FROM loan_property lp
   JOIN loan l ON lp.loan_id = l.id
   JOIN ab ON ab.loan_id = l.id
WHERE lp.property_id = p.id