可以在表的多个列上定义索引。例如，如果你有一个如下形式的表：

1
2
3
4
5
CREATE TABLE test2 (
    major int,
    minor int,
    name varchar
);

假设经常发出如下查询：

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

那么，可能适合在 major 和 minor 列上一起定义一个索引，例如：

1
CREATE INDEX test2_mm_idx ON test2 (major, minor);

目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引。是否可以有多列键与是否可以添加 INCLUDE 列是独立的。索引最多可以有 32 列，包括 INCLUDE 列。（这个限制可以在构建 PostgreSQL 时进行修改；请参阅文件 pg_config_manual.h。）

一、多列 B-tree 索引

多列 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 索引可以与涉及索引列的任何子集的查询条件一起使用。附加列上的条件会限制索引返回的条目，但第一个列上的条件对于确定需要扫描的索引部分最为重要。如果 GiST 索引的第一个列只有少数几个不同的值，即使附加列有许多不同的值，该索引的效果也会相对较差。

三、多列 GIN 索引

多列 GIN 索引可以与涉及索引列的任何子集的查询条件一起使用。与 B-tree 或 GiST 不同，查询条件使用哪些索引列对索引搜索的有效性没有影响。

四、多列 BRIN 索引

多列 BRIN 索引可以与涉及索引列的任何子集的查询条件一起使用。与 GIN 类似，与 B-tree 或 GiST 不同，查询条件使用哪些索引列对索引搜索的有效性没有影响。在单个表上使用多个 BRIN 索引而不是一个多列 BRIN 索引的唯一原因是设置不同的 pages_per_range 存储参数。

当然，每个列必须使用适合索引类型的操作符；涉及其他操作符的子句将不会被考虑。

五、使用多列索引的注意事项

多列索引应谨慎使用。在大多数情况下，单列索引就足够了，并且可以节省空间和时间。超过三列的索引不太可能有帮助，除非表的使用极其特定化。

1、组合多个索引

单个索引扫描只能使用那些使用索引列及其操作符类中的操作符并通过 AND 连接的查询条件。例如，给定一个 (a, b) 上的索引，查询条件如 WHERE a = 5 AND b = 6 可以使用该索引，但查询条件如 WHERE a = 5 OR b = 6 不能直接使用该索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table z2huo_company  
(  
    id             bigserial                not null,  
    com_code       varchar(8)               not null,  
    com_name       varchar(255)             not null,
    constraint pk_z2huo_company_id primary key (id)
);

create index idx_z2huo_company_com_code_name on z2huo_company using btree (com_code, com_name);

explain  
select * from z2huo_company  
where com_code = 'hhhLLL' and com_name = '从业'  
;  
  
explain  
select * from z2huo_company  
where com_code = 'hhhLLL' or com_name = '从业'  
;

以下是上面两个使用联合索引的 SQL 的执行计划。可以看到对于联合索引来说 AND 使用了索引，而 OR 语句没有使用索引。

1
2
3
4
5
6
7
Index Scan using idx_z2huo_company_com_code_name on z2huo_company  (cost=0.42..8.44 rows=1 width=380)
  Index Cond: (((com_code)::text = 'hhhLLL'::text) AND ((com_name)::text = '从业'::text))

Gather  (cost=1000.00..10079.48 rows=3 width=380)
  Workers Planned: 2
  ->  Parallel Seq Scan on z2huo_company  (cost=0.00..9079.18 rows=1 width=380)
        Filter: (((com_code)::text = 'hhhLLL'::text) OR ((com_name)::text = '从业'::text))

而如果使用两个字段的单独索引，执行计划则如下：

1
2
3
4
5
6
7
8
9
10
11
12
Index Scan using idx_z2huo_company_com_code on z2huo_company  (cost=0.42..8.44 rows=1 width=380)
  Index Cond: ((com_code)::text = 'hhhLLL'::text)
  Filter: ((com_name)::text = '从业'::text)

Bitmap Heap Scan on z2huo_company  (cost=8.86..20.73 rows=3 width=380)
  Recheck Cond: (((com_code)::text = 'hhhLLL'::text) OR ((com_name)::text = '从业'::text))
  ->  BitmapOr  (cost=8.86..8.86 rows=3 width=0)
        ->  Bitmap Index Scan on idx_z2huo_company_com_code  (cost=0.00..4.43 rows=1 width=0)
              Index Cond: ((com_code)::text = 'hhhLLL'::text)
        ->  Bitmap Index Scan on idx_z2huo_company_com_name  (cost=0.00..4.43 rows=2 width=0)
              Index Cond: ((com_name)::text = '从业'::text)

幸运的是，PostgreSQL 具有多索引组合的能力（包括多次使用同一个索引），以处理无法通过单个索引扫描实现的情况。系统可以在多个索引扫描之间形成 AND 和 OR 条件。例如，查询条件如 WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 可以分解为对 x 上的索引进行四次单独扫描，每次扫描使用一个查询条件。然后将这些扫描的结果通过 OR 组合在一起以生成最终结果。另一个例子是，如果我们分别在 x 和 y 上有索引，查询如 WHERE x = 5 AND y = 6 的一种可能实现是使用每个索引并应用相应的查询条件，然后将索引结果通过 AND 组合在一起以确定结果行。

为了组合多个索引，系统会扫描每个需要的索引，并在内存中准备一个位图，给出符合该索引条件的表行的位置。然后根据查询需要对这些位图进行 AND 和 OR 组合。最后，实际访问并返回表行。表行是以物理顺序访问的，因为位图就是这样布局的；这意味着原始索引的任何排序都会丢失，因此如果查询有 ORDER BY 子句，则需要单独的排序步骤。出于这个原因，以及每次额外的索引扫描都会增加额外的时间，查询规划器有时会选择使用简单的索引扫描，即使有其他可用的索引可以使用。

使用多个 OR 语句的示例如下：

1
2
3
4
explain  
select * from z2huo_company  
where com_code = 'hhhLLL' or com_code = 'aaaa' or com_code = 'bbbb'  
;

执行计划如下：

1
2
3
4
5
6
7
8
9
Bitmap Heap Scan on z2huo_company  (cost=13.28..25.16 rows=3 width=380)
  Recheck Cond: (((com_code)::text = 'hhhLLL'::text) OR ((com_code)::text = 'aaaa'::text) OR ((com_code)::text = 'bbbb'::text))
  ->  BitmapOr  (cost=13.28..13.28 rows=3 width=0)
        ->  Bitmap Index Scan on idx_z2huo_company_com_code  (cost=0.00..4.43 rows=1 width=0)
              Index Cond: ((com_code)::text = 'hhhLLL'::text)
        ->  Bitmap Index Scan on idx_z2huo_company_com_code  (cost=0.00..4.43 rows=1 width=0)
              Index Cond: ((com_code)::text = 'aaaa'::text)
        ->  Bitmap Index Scan on idx_z2huo_company_com_code  (cost=0.00..4.43 rows=1 width=0)
              Index Cond: ((com_code)::text = 'bbbb'::text)

在几乎所有复杂的应用中，都有各种可能有用的索引组合，数据库开发者必须权衡利弊以决定提供哪些索引。有时多列索引是最好的选择，但有时创建单独的索引并依赖索引组合特性会更好。例如，如果你的工作负载包括一些仅涉及列 x 的查询，一些仅涉及列 y 的查询，以及一些同时涉及两列的查询，你可能会选择在 x 和 y 上分别创建两个单独的索引，依靠索引组合来处理同时使用两列的查询。你也可以创建一个多列索引 (x, y)。这个索引通常对于涉及两列的查询比索引组合更有效，但是它对于仅涉及 y 的查询几乎无用，因此不应是唯一的索引。多列索引和 y 上的单独索引的组合通常表现得相当不错。对于仅涉及 x 的查询，可以使用多列索引，尽管它会比单独的 x 索引更大，因此速度稍慢。最后一个选择是创建所有三个索引，但这可能只有在表被搜索的频率远高于更新频率，并且三种类型的查询都很常见的情况下才合理。如果某一种类型的查询远不如其他类型常见，你可能会选择只创建最匹配常见类型的两个索引。

2、仅扫描索引和覆盖索引

PostgreSQL 中的所有索引都是二级索引，这意味着每个索引都单独存储在表的主要数据区域（在 PostgreSQL 术语中称为表的堆）之外。这表示在一个普通的索引扫描中，每行的检索都需要从索引和堆中获取数据。此外，虽然匹配给定可索引 WHERE 条件的索引条目通常在索引中是相邻的，但它们引用的表行可能位于堆中的任何位置。因此，索引扫描的堆访问部分涉及大量随机访问堆，这可能会很慢，尤其是在传统的旋转介质上。

为了解决这一性能问题，PostgreSQL 支持 index-only 扫描，即无需任何堆访问即可仅从索引回答查询。基本思想是从每个索引条目中直接返回值，而不是查询关联的堆条目。使用这种方法有两个基本限制：

  • 索引类型必须支持索引独扫描。B-tree 索引总是支持。GiST 和 SP-GiST 索引对某些操作符类支持索引独扫描，但对其他类不支持。其他索引类型则没有支持。基本要求是索引必须物理存储或能够重构每个索引条目的原始数据值。作为反例，GIN 索引不能支持索引独扫描，因为每个索引条目通常只包含原始数据值的一部分。

  • 查询必须引用索引中存储的列。例如，假设一个表有列 x 和 y，并且还有一个列 z，这些查询可以使用索引独扫描：

    1
    2
    SELECT x, y FROM tab WHERE x = 'key';
    SELECT x FROM tab WHERE x = 'key' AND y < 42;

    但这些查询不能：

    1
    2
    SELECT x, z FROM tab WHERE x = 'key';
    SELECT x FROM tab WHERE x = 'key' AND z < 42;

如果这两个基本要求得到满足，那么查询所需的所有数据值都可以从索引中获得，因此索引独扫描在物理上是可行的。但是，PostgreSQL 中的任何表扫描还有一个额外的要求：它必须验证每个检索到的行对查询的 MVCC 快照可见。可见性信息不存储在索引条目中，只存储在堆条目中；因此，乍一看似乎每行检索都需要访问堆。如果是最近修改的表行，这确实是如此。然而，对于很少变化的数据，有一种绕过这个问题的方法。PostgreSQL 跟踪表堆中每个页面上的所有行是否足够旧，以至于对所有当前和未来的事务都可见。这些信息存储在表的可见性映射的一个位中。索引独扫描在找到候选索引条目后，会检查相应堆页面的可见性映射位。如果该位已设置，行就是已知可见的，因此可以直接返回数据而无需进一步操作。如果该位未设置，则必须访问堆条目以确定其是否可见，因此不会比标准索引扫描获得性能优势。即使在成功的情况下，这种方法也是用可见性映射访问代替堆访问；但由于可见性映射比其描述的堆小四个数量级，因此访问它的物理 I/O 少得多。在大多数情况下，可见性映射始终缓存在内存中。

简而言之，虽然在满足两个基本要求的情况下索引独扫描是可行的，但只有当表的堆页面中有很大比例的全可见映射位被设置时，它才会带来性能优势。但在实践中，大部分行不经常变化的表非常常见，使得这种类型的扫描非常有用。

为了有效地利用索引独扫描功能，你可能会选择创建一个覆盖索引，即专门为频繁运行的特定类型查询设计的索引，该索引包含查询所需的列。由于查询通常需要检索的列不仅仅是搜索所用的列，PostgreSQL 允许你创建一些列仅作为“有效负载”而不作为搜索键的索引。这是通过添加一个 INCLUDE 子句列出额外的列来实现的。例如，如果你经常运行如下查询：

1
SELECT y FROM tab WHERE x = 'key';

传统的加速此类查询的方法是仅创建一个 x 列的索引。然而，定义如下索引：

1
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

可以处理这些查询作为索引独扫描，因为 y 可以直接从索引中获取而无需访问堆。

由于 y 列不是索引搜索键的一部分，因此它不必是索引可以处理的数据类型；它只是存储在索引中，而不被索引机制解释。另外，如果索引是唯一索引，即：

1
CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

唯一性条件仅适用于 x 列，而不适用于 x 和 y 的组合。（INCLUDE 子句也可以写在唯一性和主键约束中，提供设置此类索引的替代语法。）

谨慎地添加非键有效负载列到索引中，特别是宽列。如果索引条目超过索引类型允许的最大大小，数据插入将失败。无论如何，非键列会复制索引表中的数据并增加索引的大小，从而可能减慢搜索速度。并且请记住，除非表的变化足够缓慢，以至于索引独扫描很可能不需要访问堆，否则在索引中包含有效负载列几乎没有意义。如果无论如何都要访问堆条目，从那里获取列的值并不会增加额外的成本。其他限制是目前不支持将表达式作为包含列，而且只有 B-tree、GiST 和 SP-GiST 索引支持包含列。

在 PostgreSQL 拥有 INCLUDE 功能之前，人们有时通过将有效负载列写成普通索引列来创建覆盖索引，即写成：

1
CREATE INDEX tab_x_y ON tab(x, y);

即使他们从未打算在 WHERE 子句中使用 y。只要额外的列是尾随列，这种方法就很好用；将它们设为前导列是不明智的。然而，这种方法不支持需要索引在键列上强制唯一性的场景。

后缀截断总是从上层 B-Tree 中移除非键列。作为有效负载列，它们从不用于引导索引扫描。截断过程还会在剩余的键前列足以描述最低层 B-Tree 的元组时，移除一个或多个尾随键列。实际上，没有 INCLUDE 子句的覆盖索引通常会避免在上层存储实际上是有效负载的列。然而，将有效负载列显式定义为非键列可以可靠地保持上层元组的小巧。

原则上，索引独扫描可以与表达式索引一起使用。例如，假设有一个索引 f(x)，其中 x 是表的一个列，理论上可以执行如下查询：

1
SELECT f(x) FROM tab WHERE f(x) < 1;

作为索引独扫描，如果 f() 是一个计算成本高的函数，这非常吸引人。然而，PostgreSQL 的查询规划器目前在这方面并不聪明。它只在查询所需的所有列都可以从索引中获得时，才认为查询可能通过索引独扫描执行。在这个例子中，除了在 f(x) 的上下文中，x 并不需要，但规划器没有注意到这一点，因此得出结论认为索引独扫描是不可能的。如果索引独扫描足够有价值，可以通过将 x 添加为包含列来解决这个问题，例如：

1
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

另一个需要注意的问题是，如果目的是避免重新计算 f(x)，则规划器不一定能将不在可索引 WHERE 子句中的 f(x) 用法与索引列匹配。它通常会在简单的查询中正确处理这种情况，但在涉及连接的查询中则不行。这些缺陷可能会在未来版本的 PostgreSQL 中得到解决。

部分索引与索引独扫描也有有趣的交互作用：

1
2
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;

原则上，我们可以对这个索引进行索引独扫描以满足如下查询：

1
SELECT target FROM tests WHERE subject = 'some-subject' AND success;

但有一个问题：WHERE 子句引用了 success，而它不是索引结果列的一部分。尽管如此，索引独扫描仍然是可能的，因为计划在运行时不需要重新检查 WHERE 子句的这一部分：索引中找到的所有条目必然有 success = true，因此不需要在计划中显式检查这一点。PostgreSQL 9.6 及更高版本将识别这种情况并允许生成索引独扫描，但早期版本则不会。

相关链接

PostgreSQL: Documentation: 16: 11.5. Combining Multiple Indexes

PostgreSQL: Documentation: 16: 11.9. Index-Only Scans and Covering Indexes

PostgreSQL: Documentation: 16: Chapter 13. Concurrency Control

OB tags

#PostgreSQL