This page in other versions: Unsupported versions: 7.1

7.3. Multi-Column Indices

An index can be defined on more than one column. For example, if you have a table of this form:

  major int,
  minor int,
  name varchar
(Say, you keep you your /dev directory in a database...) and you frequently make queries like
SELECT name FROM test2 WHERE major = constant AND minor = constant;
then it may be appropriate to define an index on the columns major and minor together, e.g.,
CREATE INDEX test2_mm_idx ON test2 (major, minor);

Currently, only the B-tree implementation supports multi-column indices. Up to 16 columns may be specified. (This limit can be altered when building Postgres; see the file config.h.)

The query optimizer can use a multi-column index for queries that involve the first n consecutive columns in the index (when used with appropriate operators), up to the total number of columns specified in the index definition. For example, an index on (a, b, c) can be used in queries involving all of a, b, and c, or in queries involving both a and b, or in queries involving only a, but not in other combinations. (In a query involving a and c the optimizer might choose to use the index for a only and treat c like an ordinary unindexed column.)

Multi-column indexes can only be used if the clauses involving the indexed columns are joined with AND. For instance,

SELECT name FROM test2 WHERE major = constant OR minor = constant;
cannot make use of the index test2_mm_idx defined above to look up both columns. (It can be used to look up only the major column, however.)

Multi-column indices should be used sparingly. Most of the time, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are almost certainly inappropriate.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group