Re: Win2K Questions

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Jean-Luc Lachance wrote:
>> The index has to be updated anyhow to reflect the new record. Doesn't
>> it?

> Actually no. Index scans can go from the index to the heap, see the
> tuple is dead, and move on to the next one.

More specifically: an UPDATE operation has to insert *new* index entries
pointing at the new version of the row. It does not presently have to
touch the index entries for the prior version of the row. Similarly,
DELETE need not modify index entries at all. To maintain version status
in index entries, both those operations would have to get slower.
(The eventual cleanup of the dead index entries is handled by VACUUM,
which we hope is not critical to interactive performance.)

I also think that Jean-Luc is underestimating the significance of the
index-bloat issue. The primary reason to have an index at all is that
it's much smaller than the table it indexes, and therefore is
considerably cheaper to scan. Increasing the size of index entries
is a fundamental blow to their usefulness.

regards, tom lane