Can this UPDATE be faster with indexes?
Query

You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements:

UPDATE line_items li
SET    product_id = d.latest_product_id
FROM   products               p
JOIN   vendors                v  ON v.id  = p.vendor_id
JOIN   vendorgroups           vg ON vg.id = v.vendorgroup_id
JOIN   duplicate_product_sets d  ON d.invtid    = p.invtid
                                AND d.vendor_id = p.vendor_id
                                AND d.host_id   = vg.host_id
                                AND d.latest_product_id &lt;&gt; p.id
WHERE  p.id = li.product_id;



You don't need to join line_items a second time. And you also forgot to join the two instances, producing a cross join (as pointed out by @a_horse in the comments). I removed the redundant table and now the join to products does the job.


Indices


  I have dropped indexes on line_items (except the primary key index) ...


Basically correct, but If you only update a small fraction of rows, you should still have an index on product_id, even if that incurs a small cost for index update. The gain should be much more substantial.

For best performance, these are the perfect indices:

li(product_id)
p(vendor_id, invtid, id)
v(id, vendorgroup_id)
vg(id, host_id)
d(vendor_id, invtid, host_id, latest_product_id)


I mean multicolumn indices where I listed multiple columns. Indices on individual columns also go a long way, but not all the way. Multicolumn indices are most efficient when index-only scans are possible. In the best case scenario, this update would only need to scan the suggested indices without touching any table except line_items.

Whether it pays to optimize this operation depends on the complete picture. Additional indices incur a small cost for write performance.

And be sure, that you use the same (adequate) data type for invtid in both columns. I see a cast to text in the EXPLAIN output that shouldn't be there.

Related:


How do I know what indexes to create for a table
Very slow simple PostgreSQL query on RDS
Working of indexes in PostgreSQL