Re: proposal: be smarter about i/o patterns in index scan

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> The main thing that unordered indexscan could do for us is extend the
> usefulness of indexscan plans into relatively-poor-selectivity cases
> where we presently tend to drop back to seqscans.

Well I'm sure Tom remembers this since he's mentioned it in the past. But for
the sake of completeness I'll remind others of the other big benefit a
heap-ordered indexscan could provide: Merging multiple index scans.

If you have something like

select * from a where x>? and (y=? or z=?)

and you have separate indexes on x, y, and z, then having a heap-ordered index
scan would allow the optimizer to do three independent scans of the three
indexes and only have to fetch the tuples that fit the entire boolean clause
from the heap.

In the case of expressions where any individual column is very non-selective
but the combination is very selective the result can be a big improvement.

(Actually in the special case of x=? and y=? and z=? you could do the same
without any special sorting step if the index tuples were kept in heap order
within each index key. I think that would be an interesting optimization to
try too)

--
greg