From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Erik Jones <erik@myemma.com>
Cc: Mark Makarowsky <bedrockconstruction@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update table performance
Date: Wed, 08 Aug 2007 09:00:48 +0100
Message-ID: <46B97830.1010603@enterprisedb.com> (raw)
In-Reply-To: <1C6EEA75-603F-4B02-8127-920F17AC5411@myemma.com>
References: <670773.87500.qm@web32209.mail.mud.yahoo.com>
<1C6EEA75-603F-4B02-8127-920F17AC5411@myemma.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, erik@myemma.com, bedrockconstruction@yahoo.com
Subject: Re: Update table performance
In-Reply-To: <46B97830.1010603@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox