UPDATE LEFT JOIN执行慢、嵌套循环及全量更新问题咨询

1. 第一个查询为什么会产生嵌套循环？ #

你第一个UPDATE的写法触发了隐式交叉连接（CROSS JOIN）：UPDATE后的目标表是union_suppliers，同时你又在FROM子句中给union_suppliers起了别名us，但没有写这两个同表实例的关联条件，相当于把5000行的表和自身做了无关联条件的笛卡尔积，总数据量直接膨胀到5000*5000=2500万行。
优化器评估行数时出现误判，认为小行数关联走嵌套循环成本更低，最终导致大量重复循环扫描，执行时间飙升到70秒。

2. 两个查询表面逻辑一致，第二个查询和第一个的实际差异是什么？ #

两个查询逻辑完全不一样：

• 第一个查询是目标表和FROM子句的union_suppliers无关联的笛卡尔积，只要任意一行FROM侧的us+sd组合满足WHERE条件，就会对应更新目标表的行，甚至会出现同一行被重复更新多次的情况。
• 第二个查询新增了uso.uuid = us.uuid的关联条件，把UPDATE目标表和FROM侧的union_suppliers实例做了主键关联，消除了笛卡尔积，每一行目标表只会对应FROM侧的一行同主键的uso记录。

3. 为什么第二个查询会更新所有行？ #

问题出在LEFT JOIN + coalesce(sd.last_sync > uso.supp_date_update, TRUE)的组合：
你用了LEFT JOIN关联source_duns，当某行uso没有匹配的sd记录时，sd.last_sync会是NULL，此时sd.last_sync > uso.supp_date_update的结果也是NULL，coalesce会把NULL替换为TRUE，相当于所有没有匹配到sd的行也满足WHERE条件。哪怕匹配到了sd的行，只要last_sync更大也满足条件，最终所有行都会命中更新规则，没有匹配sd的行会被把supp_date_update设置为NULL。

正确写法参考

如果你的需求是仅当source_duns存在对应记录，且同步时间晚于当前更新时间时才更新，可以直接去掉FROM子句里重复的union_suppliers，用内连接即可：

UPDATE union_suppliers us
SET supp_date_update = sd.last_sync
FROM source_duns sd
WHERE us.source_duns_uuid = sd.uuid
  AND sd.last_sync > us.supp_date_update;

如果需要保留无匹配sd时的更新逻辑，也请明确指定无匹配时supp_date_update要更新的具体值，避免被赋值为NULL。

内容的提问来源于stack exchange，提问作者Preston