Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 75A3D9F9875 for ; Fri, 10 Aug 2007 14:57:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 11181-05-6 for ; Fri, 10 Aug 2007 14:57:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.238]) by postgresql.org (Postfix) with ESMTP id 6A27E9F9889 for ; Fri, 10 Aug 2007 14:57:54 -0300 (ADT) Received: by qb-out-0506.google.com with SMTP id o12so3744613qba for ; Fri, 10 Aug 2007 10:57:53 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=J81PtsORc9VAk5E2FrlQRZmR63bHQl9lFasYHtqUsv8G+gqSKeCQkDbU3OJnnKp1D4XjOTj5aUqOlLtZ3wRc37jUdNW4HPejvJl8lezhfFrQu4FmPiqiBDZO0Vjpl/2cufJ/i/mQ7WsfOrTBO8SfOFC95WOZ20v2NijNpXDlePw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=DZm2d1aCqONHgr2BuLTSfiYVgipjRuDG/Z9MPWvv5gEZvgXgUWMd/z8a9UektUHordtXbHttH7fVDdSK24Q4VWUAuJBFxhGqEQBimESYNxeMet8WnHmIQXdf9TBgBSkiz0ss1P9KS2s7jKSpb4Lg9WtDSwMmyef0IsguToJVqTI= Received: by 10.140.226.14 with SMTP id y14mr1161313rvg.1186768673187; Fri, 10 Aug 2007 10:57:53 -0700 (PDT) Received: by 10.141.98.12 with HTTP; Fri, 10 Aug 2007 10:57:53 -0700 (PDT) Message-ID: Date: Fri, 10 Aug 2007 12:57:53 -0500 From: "Scott Marlowe" To: "Mark Steben" Subject: Re: Mass updates on a large table Cc: pgsql-admin@postgresql.org In-Reply-To: <009601c7db75$12876c30$b501a8c0@D1RPQZB1> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <009601c7db75$12876c30$b501a8c0@D1RPQZB1> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/124 X-Sequence-Number: 26455 On 8/10/07, Mark Steben wrote: > > Good afternoon, > > I am attempting an update on two new date field columns on a 17 million row > table. Every row gets updated. > > The update statement is a simple one: > > UPDATE EMAILRCPTS SET ID = ID > > And the update of the new date fields themselves occurs as the result of a > before trigger. > > The update took 3 days, 10 hours to complete on the testing box. That's quite a while for only 17 million rows. Are these rows particularly wide? Is it possible to do it by groups with a vacuum in between each group? That would keep the bloat down. You don't mention your vacuuming strategy. That might affect performance here. Also, are there any FKs to / from this table? > To minimize the impact of checkpoints. The SHARED_BUFFERS parameter has > been bumped up to 140000 on a 20meg RAM box. I assume you meant 20Gig box. Under 7.4 larger shared_buffers may not be a good thing. that's a very large shared buffer setting for 7.4 to handle. > There are about 9 indexes on this table although none of them reference the > date fields so since there are no inserts I don't think they would have an > impact on the update (I've been wrong before though) Doesn't matter. PostgreSQL's implementation of MVCC means that each update results in a new row, and therefore each index has to be updated for each row updated. > Would an update statement referencing the date fields work faster than a > trigger? Possibly. > Do you have any other suggestions to speed this up? > We are at Postgres 7.4.5. Upgrade to a modern version? 7.4 is getting old fast, and 7.4.5 has a LOT of bugs that have been fixed in later versions. It's up to like 7.4.17 so you're missing a LOT of updates just in the branch you're in. But upgrading to 8.2.4 would definitely be a help. > We simply cannot afford this table to be down for 3+ days during a > production update. The production box is a 32meg RAM box. I would question the business process that requires an entire 17 million row table be updated. Also, posting your schema and your triggers might help a bit as well.