Re: [PERFORM] Slow updates, poor IO

Scott Marlowe wrote:

On Fri, Sep 26, 2008 at 5:03 PM, John Huttley <John@mib-infotech.co.nz> wrote:   

Hi Andrew, There are two problems. The first is the that if there is a table with a index and an update is performed on a non indexed field, the index is still re indexed.     

 I assume you mean updated, not reindexed, as reindexed has a different meaning as regards postgresql.  Also, this is no longer true as of version 8.3.  If you're updating non-indexed fields a lot and you're not running 8.3 you are doing yourself a huge disservice.    

Yes sorry, I mean all indexes are updated even when the updated field is not indexed.
I'm running 8.3.3

this is part of the trade-offs of MVCC.     

 was...  was a part of the trade-offs.    

You are thinking of HOT?
I don't think it applies in the case of full table updates??

We should reasonably expect that the total amount of IO will go up, over a non-indexed table.  The second thing is that the disk IO throughput goes way down.  This is not an issue with MVCC, as such, except that it exposes the effect of a write to an indexed field.     

 It's really an effect of parallel updates / writes / accesses, and is always an issue for a database running on a poor storage subsystem.  A db with a two drive mirror set is always going to be at a disadvantage to one running on a dozen or so drives in a RAID-10    

Oh well, I'm forever going to be disadvantaged.