Re: Query optimization and indexes

Gregory Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.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