Hey Tom,
Believe me I do not underestimate the impact. Scanning the index is
different from navigating the index down the tree.
I work with fairly wide tables -- 20 to 30 times the width of the
indexed field, so even if the index was to double in size, it would
still be an order of magnitude smaller than the table.
The primary reason to have an index at all is that, because of its
structure, it allows quick access to the underlying record.
JLL
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.)
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)