Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9D0DD9F9814 for ; Fri, 10 Aug 2007 14:39:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 22161-01 for ; Fri, 10 Aug 2007 14:39:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.autorevenue.com (ip-208.178.167.106.gblx.net [208.178.167.106]) by postgresql.org (Postfix) with ESMTP id EEF039F929C for ; Fri, 10 Aug 2007 14:38:46 -0300 (ADT) Received: (qmail 27658 invoked from network); 10 Aug 2007 13:42:39 -0400 Received: from unknown (HELO D1RPQZB1) (msteben@192.168.1.181) by mail.autorevenue.com with SMTP; 10 Aug 2007 13:42:39 -0400 From: "Mark Steben" To: Subject: Mass updates on a large table Date: Fri, 10 Aug 2007 13:37:07 -0400 Message-ID: <009601c7db75$12876c30$b501a8c0@D1RPQZB1> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0097_01C7DB53.8B75CC30" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcfbdRJmSeYQ08A4Re6WvOFuk6fA1w== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2929 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.192 tagged_above=0 required=5 tests=AWL=0.190, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200708/122 X-Sequence-Number: 26453 This is a multi-part message in MIME format. ------=_NextPart_000_0097_01C7DB53.8B75CC30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. I have already adjusted the CHECKPOINT_SEGMENTS parameter up by 3X To minimize the impact of checkpoints. The SHARED_BUFFERS parameter has been bumped up to 140000 on a 20meg RAM box. 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) Would an update statement referencing the date fields work faster than a trigger? Do you have any other suggestions to speed this up? We simply cannot afford this table to be down for 3+ days during a production update. The production box is a 32meg RAM box. We are at Postgres 7.4.5. Thanks for your time and brainpower Mark Steben Senior Database Administrator @utoRevenueT A Dominion Enterprises Company 480 Pleasant Street Suite B200 Lee, MA 01238 413-243-4800 Home Office 413-243-4809 Corporate Fax msteben @autorevenue.com Visit our new website at www.autorevenue.com IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited. If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it. Thank you. ------=_NextPart_000_0097_01C7DB53.8B75CC30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D = 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.  I have already adjusted the CHECKPOINT_SEGMENTS parameter up by = 3X

To minimize the impact of checkpoints.   The = SHARED_BUFFERS parameter has been bumped up to 140000 on a 20meg RAM = box.

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)

 

Would an update statement referencing the date fields work = faster than a trigger?  Do you have any other suggestions to speed this = up?

We simply cannot afford this table to be down for 3+ days during = a production update.  The production box is a 32meg RAM = box.

 

We are at Postgres 7.4.5.

 

Thanks for your time and brainpower

 

Mark Steben

Senior Database Administrator
@utoRevenue™
A Dominion = Enterprises Company
480 = Pleasant Street
Suite = B200
Lee, MA 01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben@autorevenue.com

Visit our = new website at
www.autorevenue.com

 

IMPORTANT: The information = contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is = prohibited.  If you have received this e-mail message in error, please reply to = the sender that you have received this e-mail message in error and then = delete it.  Thank you.

 

------=_NextPart_000_0097_01C7DB53.8B75CC30--