An index can be defined on more than one column. For example, if
you have a table of this form:
CREATE TABLE test2 (
major int,
minor int,
name varchar
);
(Say, you keep 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 and GiST implementations support multicolumn
indexes. Up to 32 columns may be specified. (This limit can be
altered when building PostgreSQL; see the
file pg_config.h.)
The query optimizer can use a multicolumn 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.)
Multicolumn 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.)
Multicolumn indexes 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.
