Re: Insertion of index tuples are necessary ?

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Under current non-overwrite storage manager,PostgreSQL always has
> to insert index tuples corresponding to a new updated heap tuple even
> when the key values are invariant. This is a big pain for frequently
> updated tables.  Can we omit the insertion when the key values are
> invariant ?  We could follow update chain of heap tuples since MVCC
> was introduced.

Hmm ... I'm worried about whether the performance gain in that case
won't be lost in the case where the key values *do* change.  When
you look up a tuple using the index entry, and find it's been updated,
you would not know whether the updated version has the same key or not.
You'd have to chase the update pointer to find the current version,
and then check to see if its key fields are the same or not.  If they're
not, you just wasted a fetch cycle.  Whether you win overall depends
strongly on how many updates preserve the key fields vs change them.

> In addtion we may be able to update heap-TID member of an index
> tuple to the latest TID in the middle of index access without vacuuming.

You might be able to buy back the performance by also modifying index
tuples in-place to point to the latest version of their heap tuple,
so that the excess fetch only happens once.  But that introduces a
whole bunch of issues like locking of the index.  (I suspect that if
it were easy to do this, we'd already be marking dead index tuples
as invalid --- but we're not...)

> Comments anyone ?

WAL and the new storage manager will certainly change these
considerations quite a bit.  I'm inclined not to put much work into
optimizations like this that may be obsolete very soon.  Let's wait
and see how things look once the WAL stuff is done...
        regards, tom lane