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]

 



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)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[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