Re: Running update in chunks?

Richard Huxton wrote:
> On 21/01/13 20:09, Tim Uckun wrote:

>> Just to close this up and give some guidance to future
>> googlers...

> Careful, future googlers.

+1

>> Conclusion. Updates on postgres are slow

> Nope.

Agreed.

>> (given the default postgresql.conf). I presume this is due to
>> MVCC or the WAL or something and there are probably some things
>> I can do to tweak the conf file to make them go faster but out
>> of the box running an update on a table with lots of rows is
>> going to cost you a lot.

> Unlikely. Do you really think that a PostgreSQL installation
> typically runs 100 times slower on updates than inserts and every
> other user has just said "oh, that's ok then"? Or is it more
> likely that something peculiar is broken on your setup.

As someone who has managed hundreds of databases ranging up to over
3TB without seeing this without some very specific cause, I agree
that there is something wonky on Tim's setup which he hasn't told
us about. Then again, I'm not sure we've pushed hard enough for the
relevant details.

Tim, if you're still interested in resolving this, please post the
results from running the SQL code on this page:

http://wiki.postgresql.org/wiki/Server_Configuration

It might be worthwhile to read through this page:

http://wiki.postgresql.org/wiki/Slow_Query_Questions

... and try some of the ideas there. Base disk perfromance numbers
would help put the results in perspective.

The cause could be anything from table bloat due to inadequate
vacuuming to hardware problems.

-Kevin