Re: Exact index overhead

[Richard Huxton <dev(at)archonet(dot)com>]

Gunther Mayer wrote:
> You see, all updates change most of the data fields but never ever touch 
> the time field. Assuming correct and efficient behaviour of postgresql 
> it should then also never touch the time index and incur zero overhead 
> in its presence, but is this really the case? If it somehow does update 
> the index too even though the value hasn't changed by some weird 
> implementation detail I'd rather not have that index and live with slow 
> queries for the few times a day that reporting is run.

Well, until 8.3 PG does indeed update the index. That's because with 
MVCC an update is basically a delete+insert, so you'll end up with two 
versions (the V in MVCC) of the row.

With 8.3 there's a new feature called HOT which means updates that don't 
change an index can be more efficient.

So - if you are running 8.3, I'd say try the index and see what 
difference it makes.

--
  Richard Huxton
  Archonet Ltd