Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Te8uU-0003dA-EW for pgsql-performance@arkaria.postgresql.org; Thu, 29 Nov 2012 18:32:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Te8uT-0004cF-FT for pgsql-performance@arkaria.postgresql.org; Thu, 29 Nov 2012 18:32:29 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Te8uS-0004c8-JN for pgsql-performance@postgresql.org; Thu, 29 Nov 2012 18:32:28 +0000 Received: from mail-pa0-f46.google.com ([209.85.220.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Te8uR-000411-5w for pgsql-performance@postgresql.org; Thu, 29 Nov 2012 18:32:27 +0000 Received: by mail-pa0-f46.google.com with SMTP id bh2so7812741pad.19 for ; Thu, 29 Nov 2012 10:32:26 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:subject:from:to:cc:date:in-reply-to:references :organization:content-type:x-mailer:content-transfer-encoding :mime-version:x-gm-message-state; bh=u6fM9PqZzHvx2mRhWoYn/o2gO+oSN++7EWqx1/ml1Sw=; b=guxckKoez/9aviftgdx9VsJ1a873gJ1XFvi4ksPNoc4B0cVM/lgPeN95hnoC9uh4XL W5v2VC+o2xwiXC6NYG0fItU5eDJbPj0eJtCU04xl22EkCUtjgt/INLzWt0nuqwRDI5DA wfV5ofb5EEiol/S/3V199PzmXMjIb0lIc1Vd9rHoAYM+jvZdGqIyPUgdx+o2na+0ep/J 7/5PWIzhGFkUgjxsDet33zJSAbqWUf+Jz9Mm265+0AZBma5mERINXGc2X0fZYokoDEdj UdFMXzP3BwJc+ZvKOb5szG4T9UKBZiuhU2EfvA6mBAcVq3Fv2tejHbwp0CK2UrjfYwGs mNYQ== Received: by 10.68.219.164 with SMTP id pp4mr70346901pbc.72.1354213945947; Thu, 29 Nov 2012 10:32:25 -0800 (PST) Received: from [192.168.1.102] (c-69-181-249-16.hsd1.ca.comcast.net. [69.181.249.16]) by mx.google.com with ESMTPS id p5sm1435623paz.22.2012.11.29.10.32.23 (version=SSLv3 cipher=OTHER); Thu, 29 Nov 2012 10:32:24 -0800 (PST) Message-ID: <1354213942.10198.208.camel@jdavis-laptop> Subject: Re: Savepoints in transactions for speed? From: Jeff Davis To: Claudio Freire Cc: Mike Blackwell , postgres performance list Date: Thu, 29 Nov 2012 10:32:22 -0800 In-Reply-To: References: <1354145331.1766.84.camel@sussancws0025> Organization: Content-Type: text/plain; charset="UTF-8" X-Mailer: Evolution 3.2.3-0ubuntu6 Content-Transfer-Encoding: 7bit Mime-Version: 1.0 X-Gm-Message-State: ALoCoQkU9JbAcG+HRzhSm1nI/PHdRUZiyE1NJQoTG4JpUqIJMJT2qrEa7FxzSCT6emMmuqXAkMfx X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Thu, 2012-11-29 at 00:48 -0300, Claudio Freire wrote: > Not really that fast if you have indices (and who doesn't have a PK or two). > > I've never been able to update (update) 2M rows in one transaction in > reasonable times (read: less than several hours) without dropping > indices. Doing it in batches is way faster if you can't drop the > indices, and if you can leverage HOT updates. I tried a quick test with 2M tuples and 3 indexes over int8, numeric, and text (generated data). There was also an unindexed bytea column. Using my laptop, a full update of the int8 column (which is indexed, forcing cold updates) took less than 4 minutes. I'm sure there are other issues with real-world workloads, and I know that it's wasteful compared to something that can make use of HOT updates. But unless there is something I'm missing, it's not really worth the effort to batch if that is the size of the update. Regards, Jeff Davis -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance