Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D69FA9F981D for ; Fri, 10 Aug 2007 15:20:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 99356-07 for ; Fri, 10 Aug 2007 15:20:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 06EFA9F980A for ; Fri, 10 Aug 2007 15:20:55 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7AIKp0u011248; Fri, 10 Aug 2007 14:20:51 -0400 (EDT) To: "Mark Steben" cc: pgsql-admin@postgresql.org Subject: Re: Mass updates on a large table In-reply-to: <009601c7db75$12876c30$b501a8c0@D1RPQZB1> References: <009601c7db75$12876c30$b501a8c0@D1RPQZB1> Comments: In-reply-to "Mark Steben" message dated "Fri, 10 Aug 2007 13:37:07 -0400" Date: Fri, 10 Aug 2007 14:20:51 -0400 Message-ID: <11247.1186770051@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/125 X-Sequence-Number: 26456 "Mark Steben" writes: > 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. When you don't show us the trigger, it's hard to make any useful comment ... but 60 rows/second seems slow enough to suspect that the trigger is very inefficient. > 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) And you are again ... but still, it's awfully slow. > We are at Postgres 7.4.5. That's a big problem right there. One thing I can tell you is that it is sheer folly to set shared_buffers so high on PG 7.x. It wasn't till about 8.1 that we had buffer management algorithms that were good enough for lots of buffers. Dial it down to something under 100K buffers. And think about an update. At the very least you should be on a far more current 7.4.x release. regards, tom lane