Re: Optimizer not using index on 120M row table

On Tue, Apr 08, 2003 at 02:04:30AM -0400, Neil Conway wrote:
> 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? :-)

Sure, if it can be done strictly in SQL. :)
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"