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

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