PostgreSQL中相同逻辑UPDATE比SELECT慢的性能优化问题

UPDATE性能大幅下降的核心原因是UPDATE语句缺少待更新表和FROM子句中同名表别名的关联条件：

• UPDATE后直接声明的units表，和FROM子句中别名u的units会被数据库识别为两个独立的表实例，缺少关联条件时会产生笛卡尔积，相当于遍历全表做匹配，直接导致执行时间暴涨。
• 另外WHERE条件中已经过滤了cv.id IS NOT NULL，原来的LEFT JOIN完全可以替换为INNER JOIN，减少无效行扫描，优化执行计划。

方案1：修正UPDATE关联逻辑（推荐，保持现有写法的前提下性能对齐SELECT） #

修正后的语句如下：

UPDATE units
SET carrier_vehicle_id = cv.id
from units u
inner join volumes v on v.value = u.value
inner join carrier_units cu on cu.id = u.mccus_inspection_id
inner join carriers c on c.id = cu.carrier_id
inner join carrier_volumes cv on cv.vehicle_id = v.id and cv.carrier_id = c.id
where units.id = u.id -- 新增核心关联条件，绑定待更新行和查询结果
and u.carrier_vehicle_id is null
and cv.id is not null
and u.id = 115215784
and cv.date = cu.date

方案2：拆分查询+更新（性能最优） #

因为仅需要更新单行数据，完全可以先执行原来30ms的SELECT语句拿到cv.id的值，再直接执行单表UPDATE即可：

-- 先执行查询拿到cv.id，例如得到值为1234
UPDATE units SET carrier_vehicle_id = 1234 WHERE id = 115215784 AND carrier_vehicle_id IS NULL;

可选索引优化 #

为了进一步稳定性能，建议确认以下索引存在：

• carrier_volumes表建立联合索引：(vehicle_id, carrier_id, date, id)
• volumes表的value字段建立普通索引
• carrier_units表的id为主键（默认已存在）、date字段可建立普通索引

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