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.
> I would actually be interested in hearing a comment from someone who
> knows more just how a index condition with an OR is handled when the
> two indexes aren't part of the same multicolumn index.
Right now, the way we handle it is to test each tuple retrieved by an
indexscan against the conditions associated with the previous indexscans
(ie, the earlier OR terms), so that we can detect whether we already
returned the tuple in a previous scan. This works but it's not
especially efficient, because if the OR terms overlap then each tuple in
the overlap is fetched multiple times. Plus you have all that
computation done to recheck the conditions.
regards, tom lane