Date: Tue, 7 Aug 2007 16:42:28 -0500
From: Decibel! <decibel@decibel.org>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Richard Huxton <dev@archonet.com>,
	Mark Makarowsky <bedrockconstruction@yahoo.com>,
	pgsql-performance@postgresql.org
Subject: Re: Update table performance
Message-ID: <20070807214227.GR25704@nasby.net>
References: <435532.80672.qm@web32210.mail.mud.yahoo.com>
	<46B8749F.8010002@archonet.com> <20070807175913.GH25704@nasby.net>
	<dcc563d10708071236v4c79413fpd90a4077ad93eac9@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="pmxJj2ePOk1wdOw7"
Content-Disposition: inline
In-Reply-To: <dcc563d10708071236v4c79413fpd90a4077ad93eac9@mail.gmail.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


--pmxJj2ePOk1wdOw7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 07, 2007 at 02:36:18PM -0500, Scott Marlowe wrote:
> 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=3D'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.
>=20
> 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.

Yeah, I've used "vertical partitioning" very successfully in the past,
though I've never done it for just a single field. I'll typically leave
the few most common fields in the "main" table and pull everything else
into a second table.

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

Yeah, that too. :) Though sometimes you can't avoid it.

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.
--=20
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--pmxJj2ePOk1wdOw7
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGuOdDdO30qud8SkgRAoOJAJsHDPek8pPO81PXm3ujM1PKuMwFIwCgh0FK
qg3F3f1L348f4m1MsvM9pjw=
=UwvB
-----END PGP SIGNATURE-----

--pmxJj2ePOk1wdOw7--