DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=MS1leRy2cEPn33NFqvPjRqDbj1WyqPMnJqOgIZETOiSSaVpkTEw2Wx6Wr/vtqJqdz6eoabKjvJikzgVmkRM4JXnNh8GL44z/B6hQ+aqye3AzSdD5YA9xvX+OUdC01H1AneqvvY0Fx5E/MNWwfb1LgHvkxj2Sue94pW8N5Ax57cI=
Message-ID: <dcc563d10708071236v4c79413fpd90a4077ad93eac9@mail.gmail.com>
Date: Tue, 7 Aug 2007 14:36:18 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: Decibel! <decibel@decibel.org>
Subject: Re: Update table performance
Cc: "Richard Huxton" <dev@archonet.com>,
	"Mark Makarowsky" <bedrockconstruction@yahoo.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <20070807175913.GH25704@nasby.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <435532.80672.qm@web32210.mail.mud.yahoo.com>
	<46B8749F.8010002@archonet.com> <20070807175913.GH25704@nasby.net>

On 8/7/07, Decibel! <decibel@decibel.org> wrote:
> On Tue, Aug 07, 2007 at 02:33:19PM +0100, Richard Huxton wrote:
> > Mark Makarowsky wrote:
> > >I have a table with 4,889,820 records in it.  The
> > >table also has 47 fields.  I'm having problems with
> > >update performance.  Just as a test, I issued the
> > >following update:
> > >
> > >update valley set test='this is a test'
> > >
> > >This took 905641 ms.  Isn't that kind of slow?
> >
> > The limiting factor here will be how fast you can write to your disk.
>
> Well, very possibly how fast you can read, too. Using your assumption of
> 1k per row, 5M rows means 5G of data, which might well not fit in
> memory. And if the entire table's been updated just once before, even
> with vacuuming you're now at 10G of data.

Where one might have to update just one column of a wide table often,
it's often a good idea to move that column into its own dependent
table.

Or just don't update one column of every row in  table...