X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E3D4DD1BA35 for ; Mon, 15 Mar 2004 21:06:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95014-02 for ; Mon, 15 Mar 2004 17:05:57 -0400 (AST) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id BD013D1E29B for ; Mon, 15 Mar 2004 17:05:54 -0400 (AST) Received: from [134.22.71.76] (dyn-71-76.tor.dsl.tht.net [134.22.71.76]) by tht.net (Postfix) with ESMTP id A086276A5B; Mon, 15 Mar 2004 16:05:54 -0500 (EST) Subject: Re: atrocious update performance From: Rod Taylor To: Rosser Schwarz Cc: Postgresql Performance In-Reply-To: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com> References: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com> Content-Type: text/plain Message-Id: <1079384792.33643.4.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Mon, 15 Mar 2004 16:06:33 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/203 X-Sequence-Number: 6059 > # 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;