Can you provide more detail on what you mean by your two suggestions below: 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. 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. I guess I was just surprised by the speed it takes to update the field in Postgres since on an almost identical table in FoxPro (400,000 records less), it updates the table with the same exact update table statement in about 4 minutes. --- Decibel! <decibel@xxxxxxxxxxx> wrote: > 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) > ____________________________________________________________________________________ Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
