From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Reid Thompson <Reid(dot)Thompson(at)ateb(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: If an index is based on 3 columns will a query using two of the columns utilize the index? |
Date: | 2005-09-12 14:38:57 |
Message-ID: | 21457.1126535937@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> 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."
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
I missed the fact that the documentation said it wouldn't work though.
Will fix...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | John D. Burger | 2005-09-12 14:43:51 | Re: SQL - planet redundant data |
Previous Message | Alvaro Herrera | 2005-09-12 14:31:41 | Re: If an index is based on 3 columns will a query using two of the columns utilize the index? |