Re: Exact index overhead

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