Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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.)
>
Also, consider how hard it is to find the index entries matching a given
heap row being updated. Being able to skip that step is a big win for
UPDATE and DELETE. The nice thing is that it is updated later when
someone accesses it.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073