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 C2A44D1BABB for ; Mon, 5 Apr 2004 17:05:04 +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 62338-01 for ; Mon, 5 Apr 2004 14:05:31 -0300 (ADT) Received: from www.totalcardinc.com (unknown [64.33.232.186]) by svr1.postgresql.org (Postfix) with ESMTP id BAC39D1B441 for ; Mon, 5 Apr 2004 14:04:59 -0300 (ADT) Received: from RSCHWARZW2K ([10.250.0.37]) (authenticated bits=0) by www.totalcardinc.com (8.12.8/8.12.8) with ESMTP id i35H5bTN019089 for ; Mon, 5 Apr 2004 12:05:37 -0500 From: "Rosser Schwarz" To: Subject: Re: atrocious update performance Date: Mon, 5 Apr 2004 12:05:37 -0500 Message-ID: <009801c41b30$37872380$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: <004301c411be$74004650$2500fa0a@CardServices.TCI.com> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/42 X-Sequence-Number: 6342 After deinstalling and scrubbing PostgreSQL from my server and doing a clean build using a vanilla 7.4.2 tree, I'm rather more confident that foreign key validation is at cause in my performance problems. I recreated my schemas and ran the original update, with foreign keys referring to the identity column of the target table. The update took roughly two days, as I'd predicted based on my analysis of the previous installation. (I can't say how long with certainty, beyond that it finished some time between when I left work one night and came in the next morning, the second day after starting the query.) I'm not sure what was wrong with the previous install, such that the update took several days; two-ish days is long enough. Just this morning, however, I created a copy of the target table (all 4.7M rows), with absolutely no foreign keys referring to it, and ran the update against the copy. That update took 2300 seconds. The join columns were indexed in both cases. I'm in the process of migrating the machine to run kernel 2.6.4, following the thread started by Gary, though I suspect that the kernel revision is moot with respect to whether or not foreign keys are being incorrectly validated. I can keep the 2.4 kernel and modules around to run using the current versions for testing purposes, though any such work would necessarily be off-hours. Please advise of anything I can do to help narrow down the specific cause of the issue; I know just enough C to be mildly dangerous. /rls -- Rosser Schwarz Total Card, Inc.