PostgreSQL 多列索引

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

什么是 PostgreSQL 多列索引？

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

换句话说，我们可以说多列索引就是那些用一张表的几列创建的索引。

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

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

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

PostgreSQL 多列索引的语法

PostgreSQL 多列索引的语法如下：

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

如果我们要指定一个多列索引，我们应该将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 多列索引示例

要了解PostgreSQL 多列索引的工作原理，我们将看到以下示例。

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

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

Person表包含各种列，例如Person_id、First_name、Last_name列，其中我们使用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表中创建并插入值后，我们将识别姓氏为Smith的人，如以下命令所示：

SELECT *  
FROM Person  
WHERE last_name = 'Smith';  

执行上述命令后，我们将得到以下输出，其中显示了姓氏属于Smith的所有人员。

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

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

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

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