Re: If an index is based on 3 columns will a query using two of the columns utilize the index?

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> http://archives.postgresql.org/pgsql-committers/2005-06/msg00156.php

> Did that patch actually implement "skip scanning"?

No, it just removed the planner's arbitrary assumption that the index
methods wouldn't cope. Skip scanning is actually something rather
different anyway.

> The comment seems to only describe removing the restriction from the planner.
> Which would make it theoretically possible but presumably the the cost
> estimator should ensure it essentially never gets chosen for btree indexes.

btcostestimate does understand this now.

> I guess I could see some corner cases where it would help. Very wide tables
> with an index on a few very selective relatively narrow columns. So the index
> could be scanned in its entirety much faster than a full table scan. But the
> index would have to be *much* narrower than the table and quite selective
> to overcome the random access penalty.

With a bitmap index scan the penalty wouldn't be so high.

> Also, I think Oracle has another scan method called a "fast index scan" that
> basically does a full sequential scan of the index. So the tuples come out
> unordered but the access pattern is sequential. Would that be a good TODO for
> Postgres? Is it feasible given the index disk structures in Postgres?

I think this would probably fail under concurrent update conditions: you
couldn't guarantee not to miss or multiply return index entries. There
is interlocking in an index-order scan that prevents such problems, but
I don't see how it'd work for a physical-order scan.

You could probably make it work if you were willing to lock out writers
for the duration of the scan, but that'd severely restrict the
usefulness I would think. I'm also not sure how we'd express such a
constraint within the system...

regards, tom lane