Multicolumn indexes

Consider a database table that is storing a category and a subcategory for a series of data. In this case, you don't expect to ever specify a subcategory without also providing a category. This is the sort of situation where a multi-column index can be useful. B-tree indexes can have to up 32 columns they index, and anytime there's a natural parent/child relationship in your data this form of index might be appropriate.

After creating an index for that sort of situation:

CREATE INDEX i_category ON t (category,subcategory);

The index could be used for queries that look like the following:

SELECT * FROM t WHERE category='x' and subcategory='y';
SELECT * FROM t WHERE category='x';

But it is unlikely to be useful for the following form:

SELECT * FROM t WHERE subcategory='y';

Had you instead created two indexes for both category and subcategory, those could satisfy either individual type of search, as well as the...