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 493DED1C952 for ; Mon, 15 Mar 2004 21:16:08 +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 95022-04 for ; Mon, 15 Mar 2004 17:15:58 -0400 (AST) Received: from www.totalcardinc.com (unknown [64.33.232.186]) by svr1.postgresql.org (Postfix) with ESMTP id D86B0D1D45A for ; Mon, 15 Mar 2004 17:15:55 -0400 (AST) Received: from RSCHWARZW2K ([10.250.0.37]) (authenticated bits=0) by www.totalcardinc.com (8.12.8/8.12.8) with ESMTP id i2FLFtCj018906 for ; Mon, 15 Mar 2004 15:15:55 -0600 From: "Rosser Schwarz" To: "'Postgresql Performance'" Subject: Re: atrocious update performance Date: Mon, 15 Mar 2004 15:15:55 -0600 Message-ID: <001501c40ad2$b3f7ade0$2500fa0a@CardServices.TCI.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 In-Reply-To: <1079384792.33643.4.camel@jester> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-MailScanner-From: rschwarz@totalcardinc.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/204 X-Sequence-Number: 6060 > > # 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. Actually, there are no foreign keys to those columns. Once they're populated, I'll apply a foreign key constraint and they'll refer to the appropriate row in the prod and subprod tables, but nothing will reference account.cust.[sub]prodid. There are, of course, several foreign keys referencing account.cust.custid. > 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; Not quite. Without this update, acount.cust.[sub]prodid are null. The data was strewn across multiple tables in MS SQL; we're normalizing it into one, hence the need to populate the two columns independently. /rls -- Rosser Schwarz Total Card, Inc.