When I perform this simple Select query, the response is returned in 30ms.

select u.id, cv.id
from units u
left join volumes v on v.value = u.value
left join carrier_units cu on cu.id = u.mccus_inspection_id
left join carriers c on c.id = cu.carrier_id
left join carrier_volumes cv on cv.vehicle_id = v.id and cv.carrier_id = c.id
where u.carrier_vehicle_id is null
and cv.id is not null
and u.id = 115215784
and cv.date = cu.date

However, when I try and perform an Update using an identical pattern, it takes 120 seconds or more.

UPDATE units
SET carrier_vehicle_id = cv.id
from units u
left join volumes v on v.value = u.value
left join carrier_units cu on cu.id = u.mccus_inspection_id
left join carriers c on c.id = cu.carrier_id
left join carrier_volumes cv on cv.vehicle_id = v.id and cv.carrier_id = c.id
where u.carrier_vehicle_id is null
and cv.id is not null
and u.id = 115215784
and cv.date = cu.date

How do I improve the performance of the Update statement to be more in line with the Select query?

CodePudding user response：

The problem is that your UPDATE joins units with itself without a join condition. Your outer joins are really inner joins, so you should rewrite the UPDATE as

UPDATE units u
SET carrier_vehicle_id = cv.id
from volumes v
cross join carrier_units cu
join carriers c on c.id = cu.carrier_id
join carrier_volumes cv on cv.vehicle_id = v.id and cv.carrier_id = c.id
where v.value = u.value
and cu.id = u.mccus_inspection_id
and u.carrier_vehicle_id is null
and cv.id is not null
and u.id = 115215784
and cv.date = cu.date;