On Mon, 2009-01-12 at 18:49 +0100, Jörg Kiegeland wrote:
> Hello,
>
> I created a multicolumn index on the columns c_1,..,c_n .
> If I do use only a true subset of these columns in a SQL query, is the
> index still efficient?
> Or is it better to create another multicolumn index defined on this subset?
>
> Thanks for any comments!
Why would you create a multicolumn index for all columns if that's not
what you actually query on?
The order of columns matter for multicolumn indexes. Multicolumn
indexes work best for queries that use all of the columns in the index,
but can also be helpful if at least the leftmost columns in the index
are specified in the query. So it depends on the order.
If the index is defined on (c_1, c_2, c_3, c_4) and your query includes:
"WHERE c_2=val AND c_3=val AND c_4=val", then the index is almost
certainly useless.
On the other hand, if you were to query "WHERE c_1=val" then if c_1 is
highly selective the index would still help.
See here:
http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html
-- Mark Lewis
