PostgreSQL 多列索引

在本节中，我们将了解 PostgreSQL 多列索引 的创建，这些索引 指定在一张表的多个列上。

什么是 PostgreSQL 多列索引？

我们可以为表上的不同列创建索引；这类索引称为 多列索引。

换句话说，我们可以说 多列索引 是指通过表的多个列创建的索引。

多列索引 也称为 复合索引、连接索引和组合索引。

它最多可以包含一张表的 32 列。这个限制可以通过修改 pg_config_manual.h 在构建 PostgreSQL 时进行更改。

此外，多列索引仅支持 B-tree, GIST, GIN 和 BRIN 类型的索引。

PostgreSQL 多列索引的语法

PostgreSQL 多列索引的语法如下：

注意：在上面的语法中，p, q, r 被视为列名。

如果我们想指定一个多列索引，我们应该将经常用于 WHERE 子句 的列放在列列表的开头，然后是条件中重复使用的列。

在上图中，我们有以下情况，PostgreSQL 优化器 将考虑使用索引：

或

或

然而，在以下情况下，我们不会使用索引：

或

PostgreSQL 多列索引示例

为了理解 PostgreSQL 多列索引 的工作原理，我们将通过以下示例进行说明。

因此，我们将使用 CREATE 命令 创建一个新表，名为 Person，并使用 INSERT 命令 插入一些值。

我们使用 CREATE 命令在 Organization 数据库 中创建 Person 表。

Person 表包含各种列，例如 Person_id, First_name, Last_name 列，其中我们将 Person_id 用作 GENERATED ALWAYS AS IDENTITY 约束。

输出

执行上述命令后，我们将收到以下消息，表明 Person 表已成功创建到 Organization 数据库中。

成功创建 Person 表后，我们将使用 INSERT 命令向其中插入一些值。

输出

执行上述命令后，我们将收到以下消息窗口，显示指定的值已成功插入到 Person 表中。

在创建和插入 Person 表中的值后，我们将识别 last_name 为 Smith 的人员，如下面的命令所示：

输出

实现上述命令后，我们将得到以下输出，显示所有 last name 为 Smith 的 person。

在下面的命令中，我们将使用 EXPLAIN 命令对 Person 表执行 顺序扫描，这有助于我们识别等效行，因为没有为 last_name 列定义索引。

输出

成功执行上述命令后，我们将获得以下结果，显示 PostgreSQL 已在 Person 表上完成了顺序扫描。

目前，多列索引仅支持 B-tree, GiST, GIN 和 BRIN 索引类型。

PostgreSQL 多列索引与 B-tree 索引

在下面的命令中，我们将在 first_name 和 last_name 列上定义 B-tree 索引。

我们假设按 last name 搜索人员比按 first name 搜索人员更频繁。

因此，在下面的命令中，我们将按照以下列顺序指定索引：

输出

实现上述命令后，我们将得到以下输出，显示 Idex_person_names 索引已成功为 Person 表创建。

PostgreSQL 优化器将在我们搜索 last name 为 Smith 的人员时使用该索引，如下面的命令所示：

输出

执行上述命令后，我们将获得以下输出

在上图中，我们将看到 QUERY PLAN 只使用了 Sequential Scan，而不是索引，因为我们的表中数据量不足以让规划器使用索引。

因此，要查看特定表的索引，我们需要使用以下命令：

输出

执行上述命令后，我们将收到以下消息窗口：特定命令已成功设置。

执行 SET 命令后，我们将再次执行 EXPLAIN 命令，并将获得以下输出，显示查询计划中的索引：

在下一个命令中，我们将识别 last name 为 Smith 且 first name 为 John 的人员：

输出

执行上述命令后，我们将收到以下消息，显示 first name 为 John 且 last name 为 Smith 的人员：

之后，PostgreSQL 优化器使用了上述命令的索引，因为 WHERE 子句中的两个列（first_name 和 last_name）都属于该索引。

输出

实现上述命令后，我们将得到以下结果：PostgreSQL 优化器使用了 Person 表的 first_name 和 last_name 列的索引。

但是，如果我们搜索 first name 为 John 的人员，PostgreSQL 将执行表的 顺序扫描，而不是使用索引，如下面的命令所示：

输出

执行上述命令后，我们将获得以下输出，显示 PostgreSQL 优化器执行了该表的顺序扫描。

注意：即使 first_name 列与索引相关，PostgreSQL 也无法强制使用它。

PostgreSQL 多列索引与 GIN 索引

• PostgreSQL 多列 GIN 索引 可与查询设置一起使用，包括索引列的任何子集。
• 与 B-tree 或 GiST 不同，索引搜索效率相似，并且与查询条件使用的索引列无关。

PostgreSQL 多列索引与 GiST 索引

• PostgreSQL 多列 GiST 索引也与包含索引列任何子集的查询条件结合使用。
• 并且附加列上的条件会检查由索引检索的条目。
• 要定义索引需要扫描多少，第一列 上的条件应该是主要的。
• 如果其第一列只包含有限的唯一值，即使其他列有多个不同的值，GiST 索引 的效率也会相对较低。

PostgreSQL 多列索引与 BRIN 索引

• PostgreSQL 多列 BRIN 索引 可与包含索引列任何子类别的查询条件一起使用。
• 与 GIN 类似，与 B-tree 或 GiST 不同，索引搜索效率相似，并且与查询条件使用的索引列无关。
• 最重要的目的之一是采用多个 BRIN 索引 而不是单个多列 BRIN 索引来在一张表上具有不同的 page_per_range 存储约束。

注意：指定多列索引时，应始终使用业务环境来确定哪些列经常用于查找，并在指定索引时将它们放在列列表的开头。

概述

• 在本节中，我们了解了 PostgreSQL 多列索引以及列顺序的重要性。
• PostgreSQL 多列索引 需谨慎使用。在大多数情况下，单列索引就足够了，可以节省空间和时间。
• 我们还了解到 PostgreSQL 多列索引仅支持 B-tree, GiST, GIN 和 BRIN 类型的索引。