PostgreSQL Multi-column Indexes

在本节中，我们将了解PostgreSQL Multi-column Indexes的创建，并且这些索引被指定在一个表的多个列上。

什么是 PostgreSQL Multi-column Indexes？

我们可以在表的不同列上创建索引；此类索引称为Multi-column Indexes。

换句话说，我们可以说Multi-column Indexes是那些由一个表的多列创建的索引。

的Multi-column Indexes也被称为综合指数，级联索引，以及组合的索引。

一个表最多可以有32列。并且可以通过在构建PostgreSQL 时修改pg_config_manual.h来更改限制。

此外，Multi-column Indexes仅支持B-tree、GIST、GIN 和 BRIN类型的索引。

PostgreSQL Multi-column Indexes的语法

PostgreSQL Multi-column Indexes的语法如下：

CREATE INDEX index_name    
ON table_name (p, q,r,….);

注意：在上述语法中，p、q、r 被视为列名。

如果要指定Multi-column Indexes，应将WHERE子句中频繁使用的列放在列列表的开头，将重复使用的列放在后面的条件中。

在上图中，我们有以下场景PostgreSQL 优化器会考虑使用索引：

WHERE p = value1 and q = value2 and r = value3;

或者

WHERE p = value1 and q = value2;

或者

WHERE p = value1;

但是，在以下情况下，我们不会使用索引：

WHERE r = value3;

或者

WHERE q = value2 and r = value3;

PostgreSQL Multi-column Indexes示例

要了解PostgreSQL Multi-column Indexes的工作原理，我们将看到以下示例。

因此，我们使用CREATE命令的帮助创建一个新表作为Person\并使用INSERT命令插入一些值。

要将Person\表创建到Organization 数据库中，我们使用CREATE命令。

该人士\表包含各列如PERSON_ID，名字，姓氏栏，在这里我们使用PERSON_ID为GENERATED ALWAYS AS IDENTITY约束。

CREATE TABLE Person(  
    Person_id INT GENERATED BY DEFAULT AS IDENTITY,  
    first_name VARCHAR(30) NOT NULL,  
    last_name VARCHAR(30) NOT NULL  
);

输出

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

成功创建Person\表后，我们将在INSERT命令的帮助下向其中输入一些值。

INSERT INTO Person (First_name, Last_name)  
VALUES ('Mike', 'Ross'),  
('John','Smith),  
('Owen','Norman'),  
('Bruce','Willis'),  
('Douglas','Smith'),  
('Olivia', 'Smith');

输出

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

在Person\表中创建并插入值后，我们将识别那些last_name为Smith 的人，如以下命令所示：

SELECT *  
FROM Person  
WHERE last_name = 'Smith';

输出

执行上述命令后，我们会得到下面的输出，显示所有的人，其 last name属于Smith。

在下面的命令，我们将使用EXPLAIN命令来执行顺序扫描的人\表，它可以帮助我们确定的等效行，因为没有为定义的索引LAST_NAME列。

EXPLAIN  
SELECT *  
FROM Person  
WHERE last_name = 'Smith';

输出

成功执行上述命令后，我们将得到如下结果，显示 PostgreSQL 完成了对Person\表的顺序扫描。

目前，Multi-column Indexes仅支持B-tree、GiST、GIN 和 BRIN索引类型。

PostgreSQL Multi-column Indexes与 B 树索引

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

让我们假设我们按姓氏搜索此人比按他们的名字搜索更频繁。

因此，在以下命令中，我们使用以下列顺序指定索引：

CREATE INDEX idex_person_names   
ON Person (last_name, first_name);

输出

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

如果我们搜索姓氏为Smith的人，PostgreSQL 优化器将使用该索引，如下面的命令所示：

EXPLAIN   
SELECT *  
FROM Person  
WHERE last_name = 'Smith';

输出

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

在上面的屏幕截图中，我们将看到QUERY PLAN仅使用顺序扫描，而不使用索引，因为我们的表中没有足够的数据供规划器使用索引。

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

SET enable_seqscan = off;

输出

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

执行完SET命令后，我们再次执行EXPLAIN命令，会得到如下输出，其中显示了查询计划中的索引：

在下一个命令中，我们将识别姓氏为Smith且名字为John 的人：

SELECT *  
FROM Person  
WHERE last_name = 'Smith'  
AND first_name = 'John';

输出

我们将获得以下消息上执行上面的命令，这表明这些人，其名字是约翰和姓氏是史密斯：

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

EXPLAIN  
SELECT *  
FROM Person  
WHERE last_name = 'Smith'  
AND first_name = 'John';

输出

执行上述命令后，我们将得到以下结果：PostgreSQL 优化器将索引用于Person\表的first_name 和 last_name列。

但是，如果我们搜索名字是John 的人，那么 PostgreSQL 将对表执行顺序扫描，而不是使用索引，如以下命令所示：

EXPLAIN  
SELECT *  
FROM Person  
WHERE first_name = 'John';

输出

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

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

带有 GIN 索引的 PostgreSQL Multi-column Indexes

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

带有 GiST 索引的 PostgreSQL Multi-column Indexes

• PostgreSQL 多列 GiST 索引还与查询条件相结合，查询条件包含索引列的任何子集。
• 附加列上的条件检查索引检索到的条目。
• 要定义要扫描多少索引，第一列的条件应该是主要的。
• 如果它的第一列只包含有限的不同值，即使其他列有几个不同的值，GiST 索引也会比较不成功。

带有 BRIN 索引的 PostgreSQL Multi-column Indexes

• PostgreSQL 多列BRIN 索引可与包含索引列的任何子类别的查询条件一起使用。
• 一样GIN和不同于B-树或GiST的，索引搜索效率是类似的，而不管索引列的所述查询条件被使用。
• 最重要的目的之一是在单个表上采用多个BRIN 索引而不是一个多列 BRIN 索引是为了具有不同的page_per_range存储约束。

注意：当我们指定Multi-column Indexes时，我们应该始终根据业务环境来确定哪些列经常用于查找，并在指定索引时在列列表的开头使用它们。

概述

• 在本节中，我们已经了解了 PostgreSQL Multi-column Indexes和列顺序的意义。
• 在PostgreSQL的Multi-column Indexes可以谨慎使用。大多数情况下，单列上的索引就足够了，可以节省空间和时间。
• 并且我们还了解到，PostgreSQL Multi-column Indexes只能支持B-tree、GiST、GIN 和 BRIN类型的索引。