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 6C63ED1EC41 for ; Tue, 23 Mar 2004 22:27:34 +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 53556-06 for ; Tue, 23 Mar 2004 18:27:33 -0400 (AST) Received: from COLSWEEPER.cranel.local (newmail.cranel.com [66.192.200.227]) by svr1.postgresql.org (Postfix) with ESMTP id 1114BD1E2E2 for ; Tue, 23 Mar 2004 18:27:30 -0400 (AST) Received: from colmail01.cranel.local (colmail01.cranel.com) by COLSWEEPER.cranel.local (Content Technologies SMTPRS 4.3.12) with ESMTP id ; Tue, 23 Mar 2004 17:26:52 -0500 Received: from cranel.com (192.168.11.134 [192.168.11.134]) by colmail01.cranel.local with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id HN8F6AVH; Tue, 23 Mar 2004 17:31:43 -0500 Message-ID: <4060B9CB.6080305@cranel.com> Date: Tue, 23 Mar 2004 17:27:23 -0500 From: Greg Spiegelberg Organization: Cranel, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rosser Schwarz Cc: pgsql-performance@postgresql.org Subject: Re: atrocious update performance References: <003c01c410f1$0192d7e0$2500fa0a@CardServices.TCI.com> In-Reply-To: <003c01c410f1$0192d7e0$2500fa0a@CardServices.TCI.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/334 X-Sequence-Number: 6190 Rosser Schwarz wrote: > Greg Spiegelberg wrote: > > >>I've been following this thread closely as I have the same problem >>with an UPDATE. Everything is identical here right down to the >>strace output. > > >>Has anyone found a workaround or resolved the problem? If not, >>I have test systems here which I can use to help up test and explore. > > > I'm still gathering data. The explain analyze I'd expected to finish > Thursday afternoon hasn't yet. I'm going to kill it and try a few > smaller runs, increasing in size, until the behavior manifests. > > Will advise. I've replaced my atrocious UPDATE with the following. begin; -- Drop all contraints alter table ORIG drop constraint ...; -- Drop all indexes drop index ...; -- Update update ORIG set column=... where...; commit; Problem is when I recreate the indexes and add the constraints back on ORIG I end up with the same long running process. The original UPDATE runs for about 30 minutes on a table of 400,000 with the WHERE matching about 70% of the rows. The above runs for about 2 minutes without adding the constraints or indexes however adding the constraints and creating the dropped indexes negates any gain. RedHat 7.3 + Kernel 2.4.24 + ext3 + PostgreSQL 7.3.5 Dual PIII 1.3'ishGHz, 2GB Memory U160 OS drives and a 1Gbps test SAN on a Hitachi 9910 Greg -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: gspiegelberg@Cranel.com Cranel. Technology. Integrity. Focus.