Re: Optimizer not using index on 120M row table

On Tue, 2003-04-08 at 00:35, Jim C. Nasby wrote:
> On Tue, Apr 08, 2003 at 12:29:00AM -0400, Neil Conway wrote:
> > Storing visibility information in index entries (in addition to heap
> > tuples) doesn't strike me as a good idea: for one thing, an
> > UPDATE/DELETE would require touching both heap tuples and any index
> > entries that point to them. It would also bloat the size of indexes.
>
> True, though these could require touching the indexes anyway due to the
> data changes (or at least I assume so, given how other RDBMS's work).

Not in the case of DELETE (since you don't need to add a new index
entry, and the logic to determine whether a tuple is deleted is based
upon the visibility information stored in the heap). In the case of
UPDATE, chances are that the index entry you need to add for the new
version of the tuple isn't on the same page as the old one -- so the
fact that the two pages happen to belong to the same index doesn't
really help.

> It might be a useful option to allow

Indeed, I can see how some might find it useful. Perhaps you'd like to
implement it? :-)

Cheers,

Neil