JOIN产生重复行时UPDATE语句的性能对比及优选方案咨询

问题背景 #

现有三张关联表：clients（1对多）→orders（1对多）→order_updates，需要通过关联order_updates将clients表中符合条件的status字段更新为active。以下是两种实现写法：

写法1（JOIN关联，会产生clients重复行） #

UPDATE "clients"
SET "clients"."status" = 'active'
FROM "orders"
JOIN "order_updates" ON "orders".id = "order_updates"."order_id"
WHERE "clients"."status" <> 'active' AND
      "orders"."client_id" = "clients"."id" AND 
      "order_updates"."created_at" > (CURRENT_DATE - INTERVAL '10 day')::date

写法2（IN子查询，避免重复行） #

注：修正原语句中子查询缺少FROM的语法错误

UPDATE "clients"
SET "clients"."status" = 'active'
WHERE "clients"."id" IN (
  SELECT "orders"."client_id"
  FROM "orders"
  JOIN "order_updates" ON "orders".id = "order_updates"."order_id"
  WHERE "order_updates"."created_at" > (CURRENT_DATE - INTERVAL '10 day')::date
)

性能与优劣分析 #

1. 底层执行逻辑差异 #

• 写法1：由于JOIN关联会生成大量重复的clients行记录，PostgreSQL会对同一个client行执行多次UPDATE操作。虽然最终结果一致（都是将status设为active），但数据库需要处理冗余的行匹配逻辑，额外消耗CPU、IO资源，尤其当某个客户存在大量订单及订单更新记录时，重复更新的开销会被显著放大。
• 写法2：子查询阶段会自动完成去重（PostgreSQL的IN子查询默认对结果集去重），得到唯一的client_id集合，之后仅对每个client行执行一次UPDATE操作，从根源上避免了重复更新。

2. 性能对比 #

在数据量较大的场景下，写法2性能明显更优：

• 写法1会触发大量冗余的UPDATE，即使PostgreSQL行锁是幂等的，锁的获取、释放仍会产生额外开销；
• 写法2的子查询可利用orders.client_id、order_updates.order_id上的索引快速筛选并去重，得到目标客户ID列表后再执行UPDATE，整体IO和CPU消耗更低。

3. 写法2的优化方向 #

可以将IN子查询替换为EXISTS子查询，性能会进一步提升——EXISTS在找到第一个匹配项后就会停止扫描，避免了子查询中可能的全量去重操作：

UPDATE "clients" c
SET "status" = 'active'
WHERE c."status" <> 'active'
AND EXISTS (
  SELECT 1
  FROM "orders" o
  JOIN "order_updates" ou ON o.id = ou."order_id"
  WHERE o."client_id" = c.id
  AND ou."created_at" > (CURRENT_DATE - INTERVAL '10 day')::date
)

结论 #

两种写法最终结果一致，但性能不一致，写法2（及优化后的EXISTS写法）更优。核心原因是写法1会产生重复UPDATE操作，带来不必要的资源消耗；而写法2通过子查询去重，确保每个客户仅被更新一次。

内容的提问来源于stack exchange，提问作者suslov.nikita