Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0E2C7633BE0 for ; Fri, 8 Jan 2010 13:15:01 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51214-03-6 for ; Fri, 8 Jan 2010 17:14:50 +0000 (UTC) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 0FFB7633A27 for ; Fri, 8 Jan 2010 13:14:26 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id o08HEP9D089384 for ; Fri, 8 Jan 2010 13:14:25 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o08H5UOT086412 for pgsql-performance@postgresql.org; Fri, 8 Jan 2010 13:05:30 -0400 (AST) (envelope-from news) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Massive table (500M rows) update nightmare Date: Fri, 8 Jan 2010 12:06:14 -0500 Organization: Hub.Org Networking Services Lines: 10 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org In-Reply-To: X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6002.18005 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.19 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188, STOX_REPLY_TYPE=0.001 X-Spam-Level: *** X-Archive-Number: 201001/145 X-Sequence-Number: 37025 > crank it up more and delay the checkpoints as much as possible during > these updates. 64 segments is already 1024M. We have 425M rows, total table size is 78GB, so we can imagine a worst case UPDATE write is less than 200 bytes * number of rows specified in the update (is that logic correct?). Inerestingly, the total index size is 148GB, twice that of the table, which may be an indication of where the performance bottleneck is.