update line_items set product_id = dups.latest_product_id
from line_items as li
join products p on li.product_id = p.id
join vendors v on p.vendor_id = v.id
join vendorgroups vg on v.vendorgroup_id = vg.id
join duplicate_product_sets dups
on dups.host_id = vg.host_id and dups.vendor_id = v.id
and dups.invtid = p.invtid and dups.latest_product_id <> p.id
I have dropped indexes on line_items
(except the primary key) because I figured since it is the table being updated, indexes would slow down the update.
Other than that, I have indexes on these columns (in addition to primary keys):
products.invtid
products.vendor_id
vendors.vendorroup_id
vendorgroups.host_id
duplicate_product_sets.host_id
duplicate_product_sets.vendor_id
duplicate_product_sets.invtid
duplicate_product_sets.latest_product_id
The result of EXPLAIN
:
-------------------------------------------------------------------------------------------------------------------------------
Update on line_items (cost=5444.25..5004105.00 rows=397508809 width=753)
-> Nested Loop (cost=5444.25..5004105.00 rows=397508809 width=753)
-> Seq Scan on line_items (cost=0.00..12944.60 rows=434160 width=719)
-> Materialize (cost=5444.25..20030.69 rows=916 width=34)
-> Hash Join (cost=5444.25..20026.11 rows=916 width=34)
Hash Cond: (li.product_id = p.id)
-> Seq Scan on line_items li (cost=0.00..12944.60 rows=434160 width=10)
-> Hash (cost=5433.62..5433.62 rows=851 width=32)
-> Nested Loop (cost=147.90..5433.62 rows=851 width=32)
Join Filter: (v.id = p.vendor_id)
-> Hash Join (cost=147.47..1253.41 rows=700 width=40)
Hash Cond: ((dups.vendor_id = v.id) AND (dups.host_id = vg.host_id))
-> Seq Scan on duplicate_product_sets dups (cost=0.00..706.97 rows=39197 width=28)
-> Hash (cost=117.37..117.37 rows=2007 width=20)
-> Hash Join (cost=36.70..117.37 rows=2007 width=20)
Hash Cond: (v.vendorgroup_id = vg.id)
-> Seq Scan on vendors v (cost=0.00..53.07 rows=2007 width=14)
-> Hash (cost=24.09..24.09 rows=1009 width=14)
-> Seq Scan on vendorgroups vg (cost=0.00..24.09 rows=1009 width=14)
-> Index Scan using products_by_invtid on products p (cost=0.42..5.96 rows=1 width=24)
Index Cond: ((invtid)::text = (dups.invtid)::text)
Filter: ((dups.latest_product_id <> id) AND (dups.vendor_id = vendor_id))
(22 rows)
I am using Postgres 9.4.
EXPLAIN (BUFFERS, ANALYZE)
would be more useful than justEXPLAIN
. – Erwin Brandstetter Apr 18 '15 at 21:37