PostgreSQL中基于列表子查询的更新与FROM子句更新的性能差异及等价性问询

咱们来好好拆解一下这两种PostgreSQL UPDATE写法的区别——不管是执行逻辑还是性能表现，都有值得说道的地方。

一、执行逻辑是否等价？ #

在你给出的这个特定例子里，二者的执行逻辑完全等价：都是把cd.facilities表中facid=0的membercost和guestcost分别乘以1.1，然后更新到facid=1的那条记录里。

不过要注意一个关键前提：这里的子查询select ... where facid=0只会返回一行数据（因为facid通常是主键或唯一约束）。如果子查询返回多行，两种写法的行为就不一样了：

• 第一种行赋值写法会直接抛出错误，因为PostgreSQL不允许用多行结果给单个行的字段赋值；
• 第二种FROM子句写法，如果关联后匹配到多行，会用最后一行的结果覆盖目标行（具体顺序取决于执行计划，通常不可控），这可能导致非预期的更新结果。

二、性能差异到底在哪里？ #

性能差异的核心取决于子查询是否是相关子查询，咱们分两种情况说：

1. 非相关子查询（你的例子就属于这种） #

你的例子里，子查询select ... where facid=0和外层的UPDATE条件facid=1完全无关，属于只需要执行一次的独立子查询。这种情况下，PostgreSQL的查询优化器会把两种写法都优化成几乎一致的执行计划——先一次性获取源数据，再更新目标行。所以性能差异可以忽略不计，选哪种写法全看个人习惯。

2. 相关子查询（批量更新多行的场景） #

如果是需要给多行数据做更新，且子查询依赖外层UPDATE的行字段（比如要根据每行的某个字段去关联取数），两种写法的性能差距就会显现：

• 行赋值写法（set (col1, col2) = (select ... where ... = outer.col)）会对每一行符合条件的记录单独执行一次子查询，相当于N次查询（N是更新行数），数据量一大就会很慢；
• FROM子句写法则是通过JOIN操作把源表和目标表一次性关联起来，批量获取所有需要的数据后再执行更新，只需要一次关联查询，性能会高效很多。

举个简单的相关子查询例子对比：

-- 行赋值写法（低效，每行执行一次子查询）
update cd.members m set (join_date) = (select max(join_date) from cd.members where club_id = m.club_id);

-- FROM子句写法（高效，一次JOIN搞定）
update cd.members m set join_date = m2.max_join_date
from (select club_id, max(join_date) as max_join_date from cd.members group by club_id) m2
where m.club_id = m2.club_id;

总结 #

• 在你给出的单条更新、非相关子查询场景下，两种写法逻辑等价，性能几乎无差异；
• 若是批量更新的相关子查询场景，优先选择FROM子句的写法，性能会更优；
• 行赋值写法胜在语法简洁，适合简单的单值/多值赋值场景。

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