Update in PostgreSQL performance relative to Select

Question

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?

how large is the table and which index have you got on carrier_vehicle_id? — Jim Jones
– Jim Jones, Commented Sep 22, 2021 at 14:36

Laurenz Albe · Accepted Answer · 2021-09-22 14:40:39Z

1

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;

answered Sep 22, 2021 at 14:40

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jgraft Over a year ago

Excellent. Thank you for the answer and the help!

Collectives™ on Stack Overflow

Update in PostgreSQL performance relative to Select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related