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-tree、GiST、GIN 和 BRIN 索引类型支持多列索引。是否可以有多个关键列与INCLUDE列是否可以被添加到索引中无关。 索引最多可以有32列,包括INCLUDE列。(该限制可以在源代码文件pg_config_manual.h中修改,但是修改后需要重新编译PostgreSQL)。

一个B-tree索引可以用于条件中涉及到任意索引列子集的查询,但是当先导列(即最左边的那些列)上有约束条件时索引最为有效。确切的规则是:在先导列上的等值约束,加上第一个无等值约束的列上的不等值约束,将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查,这样它们适当节约了对表的访问,但它们并未减小索引被扫描的部分。例如,在(a, b, c)上有一个索引并且给定一个查询条件WHERE a = 5 AND b >= 42 AND c < 77,对索引的扫描将从第一个具有a = 5和b = 42的项开始向上进行,直到最后一个具有a = 5的项。在扫描过程中,具有c >= 77的索引项将被跳过,但是它们还是会被扫描到。这个索引在原则上可以被用于在b和/或c上有约束而在a上没有约束的查询,但是整个索引都不得不被扫描,因此在大部分情况下规划器宁可使用一个顺序的表扫描来替代索引。

一个多列GiST索引可以用于条件中涉及到任意索引列子集的查询。在其余列上的条件将限制由索引返回的项,但是第一列上的条件是决定索引上扫描量的最重要因素。当第一列中具有很少的可区分值时,一个GiST索引将会相对比较低效,即便在其他列上有很多可区分值。

一个GIN索引可以用于条件中涉及到任意索引列子集的查询。与B-tree和GiST不同,GIN的搜索效率与查询条件中使用哪些索引列无关。

多列 BRIN 索引可以被用于涉及该索引被索引列的任意子集的查询条件。和 GIN 相似且不同于 B-树 或者 GiST,索引搜索效率与查询条件使用哪个索引列无关。在单个表上使用多个 BRIN 索引来取代一个多列 BRIN 索引的唯一原因是为了使用不同的pages_per_range存储参数。

当然,要使索引起作用,查询条件中的列必须要使用适合于索引类型的操作符,使用其他操作符的子句将不会被考虑使用索引。

多列索引应该较少地使用。在绝大多数情况下,单列索引就足够了且能节约时间和空间。具有超过三个列的索引不太有用,除非该表的使用是极端程式化的。第 11.5 节以及第 11.9 节中有对不同索引配置优点的讨论。