PostgreSQL更新x，其中id在y中。

Question

可能已经有人问过了，但使用“IN”等关键词进行搜索并不起作用。

这是我的查询：

UPDATE tblCustomer SET type = 2 
WHERE idcustomer                                
IN (SELECT fidcustomer1
    FROM tblorder                   
     UNION                      
    SELECT fidcustomer2
    FROM tblorder                   
   )                                

要简单概括一下：我想将所有在订单表中的顾客（无论是哪一列）的类型（只是int类型）设为2。

我的测试数据表行不超过几百行，但查询需要好几分钟才能运行完（即使没有UNION，这好像也没有什么区别），显然对于每一个顾客的行都要重新执行内部查询。我可以重写成一个SELECT DISTINCT（id）和几百个单行更新，以及用ODBC访问所使用的语言来处理逻辑，但这只是一个 hack。

如何正确地重写它呢？

补充说明： 我想更新的表包含大量相对较大的BYTEA blob，每行几MB。它们被设置为Storage External或 Extended，但我想知道这是否会使顺序扫描变慢。所有更新似乎都需要很长时间，而不仅仅是这一个。

- Kajetan Abt

---

"EXPLAIN ANALYZE" 对于性能问题非常有用。请参见 http://stackoverflow.com/tags/postgresql-performance/info - Craig Ringer

Answer 1

我建议采用更简单的方法：

UPDATE tblCustomer c
SET    type = 2 
FROM   tblorder o
WHERE  c.idcustomer IN (o.fidcustomer1, o.fidcustomer2)
AND    c.type IS DISTINCT FROM 2  -- optional, to avoid empty updates

但是，如果tblorder中存在重复项，那么采用与您之前相似的方法是明智的：

UPDATE tblCustomer c
SET    type = 2 
FROM  (
    SELECT fidcustomer1 AS cust FROM tblorder
    UNION
    SELECT fidcustomer2 FROM tblorder
    ) o
WHERE  c.idcustomer = o.cust
AND    c.type IS DISTINCT FROM 2;

无论如何，在PostgreSQL中，使用JOIN操作比使用IN表达式性能更好。

- Erwin Brandstetter

---

在我的测试数据库中，重复数据很少。在真实环境中，我预计一些客户将会负责10%到20%的订单，且重复数据是常态。 - Kajetan Abt

@Kdansky：那就用第二个查询吧。UNION可以去重。如果还有性能问题，那就需要更仔细地检查你的表了。触发器？大量并发写入负载？ - Erwin Brandstetter

这个版本的性能足够快。谢谢。 - Kajetan Abt

Answer 2

-------------------------------
-- Use two EXISTS:
-------------------------------
UPDATE tblCustomer tc
SET type = 2
WHERE EXISTS (
    SELECT *
    FROM tblorder ex
    WHERE ex.fidcustomer1 = tc.idcustomer
    )
OR EXISTS (
    SELECT *
    FROM tblorder ex
    WHERE ex.fidcustomer2 = tc.idcustomer
    );

-------------------------------
-- or combine the two EXISTS::
-------------------------------
UPDATE tblCustomer tc
SET type = 2 
WHERE EXISTS (
    SELECT *
    FROM tblorder ex
    WHERE ex.fidcustomer1 = tc.idcustomer
    OR ex.fidcustomer2 = tc.idcustomer
    );

我的直觉是第一种版本（使用两个单独的exists）会表现得更好，因为执行器可以在其中一个exists返回True时进行短路处理。这将避免UNION结构中固有的去重复（和可能的排序）阶段。

- wildplasser

---

有很多重复的情况下，这可能比我的解决方案更快。但对于只有10-20%的情况，可能并不适用。实践出真知。 - Erwin Brandstetter

问题在于，即使没有实际重复项（规划器无法知道此事），去重也必须进行。排序步骤（N log N）总是需要的（除非要处理非常少量的数据，并且可以使用哈希或位图/索引操作）。一些实验表明，我的两个存在版本总是最快的。有时Erwin#2差不多（有时甚至会产生相同的查询计划）。注意：我在FK1和FK2上添加了索引。 - wildplasser

我已经将实现方式切换为您版本的一种变体。它们在我的数据上表现非常相似，但这种方法可以在SQLite中使用，而UPDATE FROM变体则不能。 - Kajetan Abt