On Sat, Jun 14, 2003 at 01:18:15PM -0400, Tom Lane wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > I don't think you are likely to see much gain from this as scanning
> > two indexes instead of one is likely to cost about as much as scanning
> > an index and looking at the tupples to see if they match the other
> > condition.
>
> We have speculated about this in the past. Several of the big
> commercial DBs can do it, so it seems that at least some people find
> it valuable.
>
> I'd be inclined to look at it in combination with decoupling heap and
> index scan order: that is, you traverse the index and make a list of
> heap tuple TIDs that the index says to visit, then you visit those
> tuples in heap storage order. This gets rid of a lot of the
> random-access overhead of the present indexscanning scheme, at the cost
> of not producing sorted-by-the-indexkey output (but presumably the
> planner could choose whether to do it this way or the old way).
>
> The way this fits with multiple indexes is that you could gather tuple
> TIDs from several indexes and intersect or union the lists before you
> visit the heap. I believe the common way to do this is to represent
> the TID lists as sparse bitmaps and AND or OR the bitmaps.
That's my understanding as well. As to how useful it is, you just have
to consider the case of a very wide table and being able to narrow
things down as much as possible before hitting the table itself. If TID
is 6 bytes and you have 2 indexes that are each on int fields, that's 10
bytes per row, per index. Compare that to a table that's 100 bytes wide
or more and it's very easy to come out way ahead on the index side.
--
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?"