Re: Win2K Questions

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