Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom Lane <tgl@xxxxxxxxxxxxx> writes:

> Alvaro Herrera <alvherre@xxxxxxxxxxxxxx> writes:
>
> > Yes, if it is selective enough.  (It _can_ use the index, which does not
> > mean that it _will_ use it.)  Note that if your example query used the
> > columns (lname, dob), the answer would be "no."
> 
> Actually, that last point is not true anymore as of 8.1 --- see this
> thread:
> http://archives.postgresql.org/pgsql-hackers/2005-05/msg00939.php
> which led to this patch:
> http://archives.postgresql.org/pgsql-committers/2005-06/msg00156.php

Did that patch actually implement "skip scanning"? 

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.
The btree index would very very rarely help since it would require a complete
index scan.

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.

Skip scanning would make it much more likely to be helpful.

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?

-- 
greg


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux