Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 09CE6633A34 for ; Thu, 7 Jan 2010 17:05:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 32556-02 for ; Thu, 7 Jan 2010 21:05:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f227.google.com (mail-fx0-f227.google.com [209.85.220.227]) by mail.postgresql.org (Postfix) with ESMTP id 358386337C5 for ; Thu, 7 Jan 2010 17:05:35 -0400 (AST) Received: by fxm27 with SMTP id 27so4144365fxm.3 for ; Thu, 07 Jan 2010 13:05:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=nYji2LosBKUYJxnNRmZFBA++/3fTDwWQZCkPZZw3Uig=; b=Xb59bmRESdEljm6lTtvTQKzMkIhCacZCFHhjQnPZwVeIHt1HrRLo0FnX3ckccIJ+vu lwIC+AJpvdk0ojcHC76SiSK58g+vJP9theNzy0k1W/N+JkQmkBoDLv46LzAxDXe5jlls AhmfzKSAfKgz2rcJZZnrFo0Q161oxAvtQ3wUI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=NRe4bJA1aizsUKb+RFqNdWgDmzimJhXbCRT2hsXOVpeCM1NhpWmZSs/288xTMH2P7m 6MgFV/QN+Bx3Nk9RzQU4GQm6TupqLshqtxRdttWbP8XuxkxrHOWbtgdmy/IvEdYTMjns CPVXVILC4aS9S7rEBq58hh5cWm/dSKCXP1Ovk= MIME-Version: 1.0 Received: by 10.223.18.137 with SMTP id w9mr1706762faa.61.1262898334357; Thu, 07 Jan 2010 13:05:34 -0800 (PST) In-Reply-To: References: From: marcin mank Date: Thu, 7 Jan 2010 22:05:14 +0100 Message-ID: Subject: Re: Massive table (500M rows) update nightmare To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.563 tagged_above=-10 required=5 tests=AWL=0.036, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/122 X-Sequence-Number: 37002 > every update is a UPDATE ... WHERE id >>= x AND id < x+10 and a commit is performed after every 1000 updates > statement, i.e. every 10000 rows. What is the rationale behind this? How about doing 10k rows in 1 update, and committing every time? You could try making the condition on the ctid column, to not have to use the index on ID, and process the rows in physical order. First make sure that newly inserted production data has the correct value in the new column, and add 'where new_column is null' to the conditions. But I have never tried this, use at Your own risk. Greetings Marcin Mank