From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Reid Thompson <Reid(dot)Thompson(at)ateb(dot)com> |
Cc: | 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:04:53 |
Message-ID: | 20050912140453.GA34471@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Sep 12, 2005 at 09:43:57AM -0400, Reid Thompson wrote:
> 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?
See "Multicolumn Indexes" in the "Indexes" chapter of the documentation.
http://www.postgresql.org/docs/8.0/interactive/indexes-multicolumn.html
You can use EXPLAIN to see whether the planner will use an index for
a particular query.
http://www.postgresql.org/docs/8.0/interactive/performance-tips.html#USING-EXPLAIN
Note, however, that the planner will ignore an index and use a
sequential scan if it thinks the latter will be faster, so if you
want to see whether the query *can* use an index (as opposed to
*will* use it) then you could execute "SET enable_seqscan TO off"
and then run EXPLAIN (don't forget to RESET enable_seqscan or SET
it back to "on" when you're done testing).
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Reid Thompson | 2005-09-12 14:05:36 | Re: If an index is based on 3 columns will a query using two of the columns utilize the index? |
Previous Message | Alvaro Herrera | 2005-09-12 14:00:41 | Re: If an index is based on 3 columns will a query using two of the columns utilize the index? |