Postgresql 中文操作指南

11.3. Multicolumn Indexes #

可以对表中多个列定义索引。例如，如果你的表格具有以下形式：

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

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

（比如说，你将 /dev 目录保留在数据库中……​）而且你经常发出诸如：

(say, you keep your /dev directory in a database…​) and you frequently issue queries like:

SELECT name FROM test2 WHERE major = constant AND minor = constant;

这样的查询，则将索引定义在 major 和 minor 列上可能是合适的，例如：

then it might be appropriate to define an index on the columns major and minor together, e.g.:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

当前，仅 B 树、GiST、GIN 和 BRIN 索引类型支持多关键列索引。是否存在多关键列与 INCLUDE 列是否可以添加到索引中无关。索引最多可以有 32 列，包括 INCLUDE 列。（可以在构建 PostgreSQL 时更改此限制；请参见文件 pg_config_manual.h。）

Currently, only the B-tree, GiST, GIN, and BRIN index types support multiple-key-column indexes. Whether there can be multiple key columns is independent of whether INCLUDE columns can be added to the index. Indexes can have up to 32 columns, including INCLUDE columns. (This limit can be altered when building PostgreSQL; see the file pg_config_manual.h.)

多列 B 树索引与涉及索引列的任何子集的查询条件一起使用，但当对最左侧（最左）列有约束时，索引最有效。确切的规则是，对最左侧列的相等性约束，加上对没有相等性约束的第一个列的任何不等性约束，将用于限制要扫描的索引部分。对这些列右侧的列的约束将在索引中检查，因此它们节省了对表的访问，但它们不会减少必须扫描的索引部分。例如，给定 (a, b, c) 上的索引和查询条件 WHERE a = 5 AND b >= 42 AND c < 77，则索引必须从 a = 5 且 b = 42 的第一个条目扫描到 a = 5 的最后一个条目。跳过 c >= 77 的索引条目，但仍必须对其进行扫描。原则上，此索引可用于对 b 和/或 c 有约束，但对 a 没有约束的查询——但必须扫描整个索引，因此在大多数情况下，规划器会优选使用索引来执行顺序表扫描。

A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they’d still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.

多列 GiST 索引可与涉及索引列的任何子集的查询条件一起使用。对其他列的条件会限制索引返回的条目，但对第一列的条件是确定需要扫描多少索引的最重要的条件。如果 GiST 索引的第一列只有几个不同的值，即使其他列中有许多不同的值，它也会相对无效。

A multicolumn GiST index can be used with query conditions that involve any subset of the index’s columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned. A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns.

多列 GIN 索引与涉及索引列的任何子集的查询条件一起使用。与 B 树或 GiST 不同，无论查询条件使用哪个索引列，索引搜索效率都是相同的。

A multicolumn GIN index can be used with query conditions that involve any subset of the index’s columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

多列 BRIN 索引与涉及索引列的任何子集的查询条件一起使用。与 GIN 类似，但与 B 树或 GiST 不同，无论查询条件使用哪个索引列，索引搜索效率都相同。在单个表上拥有多个 BRIN 索引而不是一个多列 BRIN 索引的唯一原因是拥有不同的 pages_per_range 存储参数。

A multicolumn BRIN index can be used with query conditions that involve any subset of the index’s columns. Like GIN and unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use. The only reason to have multiple BRIN indexes instead of one multicolumn BRIN index on a single table is to have a different pages_per_range storage parameter.

当然，每个列必须与适合索引类型的运算符一起使用；涉及其他运算符的子句将不被考虑。

Of course, each column must be used with operators appropriate to the index type; clauses that involve other operators will not be considered.

多列索引应谨慎使用。在大多数情况下，单列索引足够用，并且可以节省空间和时间。只有当表的用途极其特别时，索引才可能超过三列。有关不同索引配置优势的一些讨论，请参见 Section 11.5 和 Section 11.9。

Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized. See also Section 11.5 and Section 11.9 for some discussion of the merits of different index configurations.