Re: Query optimization and indexes

Gregory Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> only the a = 5 clause would be used with the index. As of 8.1 it will
>> consider using nonconsecutive index columns

> Really? Is this the "skip scan" plan people were pining for?

No, there's no skip scan, it just applies all the indexable-column
checks within the index instead of making a trip to the heap.
For instance consider
WHERE a >= 4 AND a < 7 AND c > 5
with index entries

A B C

3 9 8
3 9 9
4 0 0 <- search starts here
4 0 1 reject
...
4 0 5 reject
4 0 6 accept (visit heap)
4 0 9 accept
4 1 0 reject
...
6 9 8 accept
6 9 9 accept
7 0 0 <- search ends when we reach here
7 0 1

If the condition on C is very selective then we might find ourselves
scanning over a lot of rejected entries within the possible bounds
for A. The problem is to guess whether re-descending the search tree
will win compared to just slogging forward, and if so to generate a
suitable search key for each intermediate descent.

regards, tom lane