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

On Mon, Sep 12, 2005 at 10:05:36AM -0400, Reid Thompson wrote:
> Alvaro Herrera wrote:
> > On Mon, Sep 12, 2005 at 09:43:57AM -0400, Reid Thompson wrote:
> >> Example:
> >> assume a table of 10 columns, three of which are fname, lname, and
> >> dob. If an index is created on (fname, lname, dob), will a query that
> >> utilizes two of the columns ( select 'data' from table where fname =
> >> 'X' and lname = 'Y') utilize the index?
> >
> > 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."
>
> Why is that? In order to use an index, does the query have to utilize
> the 'first' element of the index?

The "leftmost part." There's no way to scan an index if you don't know
the key. On a btree index, the key is ordered, and the columns at the
left are more significant than those at the right. If you don't provide
a value for the leftmost (first) column, there's no way to start
scanning the index because there's no starting point.

I don't think that was nearly clear enough, but OTOH I haven't had any
coffee today yet.

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"If you have nothing to say, maybe you need just the right tool to help you
not say it." (New York Times, about Microsoft PowerPoint)