11.3. 多列索引

可以在一个表的多个列上定义索引。例如,如果您具有这种形式的表:

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

(例如,您将/dev目录保存在数据库中...),并且经常发出以下查询:

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

那么可能需要在majorminor列上一起定义索引,例如:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

当前,仅 B 树,GiST,GIN 和 BRIN 索引类型支持多列索引。最多可以指定 32 列。 (在构建 PostgreSQL 时可以更改此限制;请参见文件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无约束的查询-但是必须扫描整个索引,因此在大多数情况下,计划者宁愿使用 Sequences 表扫描,也不愿使用索引。

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

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

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

当然,每一列都必须与适合于索引类型的运算符一起使用。涉及其他运算符的条款将不予考虑。

多列索引应谨慎使用。在大多数情况下,单列索引就足够了,并且可以节省空间和时间。除非表的使用风格极高,否则具有三列以上的索引不太可能有帮助。另请参阅Section 11.5Section 11.11,以了解有关不同索引配置的优点的一些讨论。