PostgreSQL 9.0 High Performance

Advanced index use

Many PostgreSQL users will only ever use simple B-tree indexes on a single column. There are of course a few ways to build more complicated ones too though.

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 ...