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