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
You are thinking of HOT?this is part of the trade-offs of MVCC.was... was a part of the trade-offs.
I don't think it applies in the case of full table updates??
Oh well, I'm forever going to be disadvantaged.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