> # 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; ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match