Composite indexes have these advantages:
A composite index provides opportunities for index covering.
If queries provide search arguments on each of the keys, the composite index requires fewer I/Os than the same query using an index on any single attribute.
A composite index is a good way to enforce the uniqueness of multiple attributes.
Good choices for composite indexes are:
Lookup tables
Columns that are frequently accessed together
Columns used for vector aggregates
Columns that make a frequently used subset from a table with very wide rows
The disadvantages of composite indexes are:
Composite indexes tend to have large entries. This means fewer index entries per index page and more index pages to read.
An update to any attribute of a composite index causes the index to be modified. The columns you choose should not be those that are updated often.
Poor choices are:
Indexes that are nearly as wide as the table
Composite indexes where only a minor key is used in the where clause
