> # explain update account.cust set prodid = tempprod.prodid
> where tempprod.did = origid;
>
> Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
> Merge Cond: (("outer".origid)::text = ("inner".did)::text)
> -> Index Scan using ix_origid on cust (cost=0.00..94876.83
> rows=4731410 width=244)
> -> Index Scan using ix_did on tempprod (cost=0.00..66916.71
> rows=4731410 width=18)
I'm going to hazard a guess and say you have a number of foreign keys
that refer to account.cust.prodid? This is probably the time consuming
part -- perhaps even a missing index on one of those keys that refers to
this field.
Going the other way should be just as good for your purposes, and much
faster since you're not updating several foreign key'd fields bound to
account.cust.prodid.
UPDATE tempprod.prodid = prodid
FROM account.cust
WHERE temprod.did = cust.origid;