DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=XtSy8npOl4P2Hr+2uTwlIDoXeO6QhIqvgX5BiG9c6XVe70CjrR+ESM6qZ1jxtaPFPHTWnGRwROg7dv2pvwToz69nmrygnBbN5uBIi9xOZYfQrI9MvqPxkBSl3rpbF74kU3w7zHJlZAr0Nzhp5gaV9iVb3GDxjfgrjvIzFiwJ1sk=
Message-ID: <46B97830.1010603@enterprisedb.com>
Date: Wed, 08 Aug 2007 09:00:48 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Erik Jones <erik@myemma.com>
CC: Mark Makarowsky <bedrockconstruction@yahoo.com>,
	pgsql-performance@postgresql.org
Subject: Re: Update table performance
References: <670773.87500.qm@web32209.mail.mud.yahoo.com>
	<1C6EEA75-603F-4B02-8127-920F17AC5411@myemma.com>
In-Reply-To: <1C6EEA75-603F-4B02-8127-920F17AC5411@myemma.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Erik Jones wrote:
> Decibel! wrote:
>> I should mention that if you can handle splitting the
>> update into multiple transactions, that will help a
>> lot since it means you won't be doubling the size of
>> the table.
> 
> As I mentioned above, when you do an update you're actually inserting a
> new row and deleting the old one.  That deleted row is still considered
> part of the table (for reasons of concurrency, read up on the
> concurrency chapter in the manual for the details) and once it is no
> longer visible by any live transactions can be re-used by future
> inserts.  So, if you update one column on every row of a one million row
> table all at once, you have to allocate and write out one million new
> rows.  But, if you do the update a quarter million at a time, the last
> three updates would be able to re-use many of the rows deleted in
> earlier updates.

Only if you vacuum between the updates.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com