Re: If an index is based on 3 columns will a query using two of the columns utilize the index? - Mailing list pgsql-general

From Alvaro Herrera
Subject Re: If an index is based on 3 columns will a query using two of the columns utilize the index?
Date
Msg-id 20050912143141.GA8185@surnet.cl
Whole thread Raw
In response to Re: If an index is based on 3 columns will a query using two of the columns utilize the index?  ("Reid Thompson" <Reid.Thompson@ateb.com>)
Responses Re: If an index is based on 3 columns will a query using two of the columns utilize the index?
List pgsql-general
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)

pgsql-general by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: If an index is based on 3 columns will a query using two of the columns utilize the index?
Next
From: Tom Lane
Date:
Subject: Re: If an index is based on 3 columns will a query using two of the columns utilize the index?