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

Get PostgreSQL 9.0 High Performance now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.