On Tue, Aug 07, 2007 at 02:36:18PM -0500, Scott Marlowe wrote: > On 8/7/07, Decibel! <decibel@xxxxxxxxxxx> 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. 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. -- Decibel!, aka Jim Nasby decibel@xxxxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Attachment:
pgpAA2PKngM1g.pgp
Description: PGP signature
