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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
