Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8EF7BB5D94C for ; Mon, 27 Jun 2011 16:29:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 43464-06 for ; Mon, 27 Jun 2011 19:29:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id 14D2BB5D90E for ; Mon, 27 Jun 2011 16:29:21 -0300 (ADT) Received: by qwk3 with SMTP id 3so2354984qwk.19 for ; Mon, 27 Jun 2011 12:29:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=rsfGS4VPHZzPab+AO7JkqMwJ8g5Ud9zh7ApyTPAfgNg=; b=UCqpmhHLqZRA/E+pq9Sz1cvgqc9I32k8e73zQXBV1gJZXQRyhJeIzHJkPYLZEgYbR1 3ljveRgsGRZVEjn7761qdBgaqCafsON8DROqVkd8lcPSCCQF2kyq5a6XGu+OBFNsnh99 PAmjaE9FxTbwJniaYCeq9OAnZc7k2dVbYvUoQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=fDQR7MT6bwGpdZH8rCVv1jKMykXC+4sFp6JigymvXPPdET+Y55yBKTyI4slSmuKGAA aQoyfHtlP52vTFA5GOLykazc+udbHA4dDw+A5JYIa8o/zb4OdNWenlBiCR1Wu2h352wz DguJUKxoWsVyzNq4MDJXlRGzpWxH8ayIUnmUA= MIME-Version: 1.0 Received: by 10.229.98.16 with SMTP id o16mr176390qcn.277.1309202961462; Mon, 27 Jun 2011 12:29:21 -0700 (PDT) Received: by 10.229.72.230 with HTTP; Mon, 27 Jun 2011 12:29:21 -0700 (PDT) In-Reply-To: References: <4E035649.20007@riskcontrollimited.com> <4E089B6A.4000208@riskcontrollimited.com> Date: Mon, 27 Jun 2011 21:29:21 +0200 Message-ID: Subject: Re: Long Running Update - My Solution From: Robert Klemme To: tv@fuzzy.cz Cc: Harry Mantheakis , 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=-1.889 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201106/250 X-Sequence-Number: 44183 On Mon, Jun 27, 2011 at 5:37 PM, wrote: >> The mystery remains, for me: why updating 100,000 records could complete >> in as quickly as 5 seconds, whereas an attempt to update a million >> records was still running after 25 minutes before we killed it? > > Hi, there's a lot of possible causes. Usually this is caused by a plan > change - imagine for example that you need to sort a table and the amount > of data just fits into work_mem, so that it can be sorted in memory. If > you need to perform the same query with 10x the data, you'll have to sort > the data on disk. Which is way slower, of course. > > And there are other such problems ... I would rather assume it is one of the "other problems", typically related to handling the TX (e.g. checkpoints, WAL, creating copies of modified records and adjusting indexes...). Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/