PostgreSQL Partial Index局部索引

在本节中，我们将了解PostgreSQL 部分索引的工作原理，它用于通过减小索引大小来增强命令的性能。

什么是 PostgreSQL Partial Index部分索引？

到现在为止，我们已经了解了如何将各个列的值添加到索引中。Partial 索引是下一个命令，在 PostgreSQL 索引中使用。

它允许我们定义一个表的行，这些行可以被索引。因此，我们将使用部分索引来加快查询速度，同时降低索引的大小。

换句话说，我们可以说PostgreSQL 部分索引是一个索引，它是在表的一个子部分上构建的。部分索引的谓词是条件表达式定义明确的子集。

并且仅针对满足谓词的那些表行访问索引。

假设我们使用了具有常量值的 WHERE 条件，那么在这种情况下，我们可以使用PostgreSQL 部分索引，因为它非常有用。

SELECT *   
FROM table_name  
WHERE column_name = constant_value;

PostgreSQL Partial Index部分索引的语法

指定部分索引的语法如下：

CREATE INDEX index_name  
ON table_name(column_list)  
WHERE condition;  

在上图中，WHERE 子句用于定义那些添加到索引中的行。

PostgreSQL 部分索引示例

让我们看一个示例来了解PostgreSQL 部分索引的工作原理。

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

要将客户端创建到组织数据库中，我们使用CREATE命令：

CREATE TABLE Buyer (  
    Buyer_id int Primary key,  
    first_name character varying(35) NOT NULL,  
    last_name character varying(35) NOT NULL,  
    email_address character varying(50),  
   active integer  
);  

执行以上命令后，我们会得到如下消息窗口，显示Buyer表已经成功创建到Organization表中。

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

INSERT INTO Buyer( Buyer_id, first_name, last_name, email_address, active)  
VALUES (1001,'Mike', 'Ross','mike.ross11@hotmail.com',1),  
(1002,'Olivia', 'Smith','O.smith@gmail.com',0),  
(1003,'Kathy', 'James','kathy.james@gmail.com',0),  
(1004,'Annie', 'Russell','annie33@gmail.com',0),  
(1005,'Rachel', 'Barnes','barnes.rachel55@gmail.com',1);  

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

通常情况下，如果我们对不活跃的买家感兴趣并经常进行一些跟进以让他们回来购买更多商品。

在下面的命令中，我们试图识别所有不活跃的买家：

SELECT Buyer_id, first_name, last_name, email_address  
FROM Buyer  
WHERE active = 0;  

成功执行上述命令后，我们会得到如下结果，其中显示了所有不活跃的买家信息：

在下面的命令中，我们使用EXPLAIN 关键字来实现上述命令，因为查询计划器需要扫描Buyer表：

EXPLAIN  
SELECT Buyer_id, first_name, last_name, email_address  
FROM Buyer  
WHERE active = 0;  

执行上述命令后，我们将得到如下输出，其中显示了Buyer 表的 Query plan。

在这里，我们还可以通过为活动列创建索引来增强命令，如以下命令所示：

CREATE INDEX idex_buyer_active  
ON Buyer(active);  

执行上面的命令我们会得到如下信息：index has idex_buyer_active been created successfully

上面创建的索引满足了它的目的，但它还包含几行，这些行从未被搜索过，特别是所有活跃的买家。

在下面的命令中，我们指定了仅包括不活跃买家的索引：

CREATE INDEX idex_buyer_inactive  
ON Buyer(active)  
WHERE active = 0;  

执行上述命令后，我们会得到如下信息：idex_buyer_inactive索引已经创建成功。

因此，当WHERE子句出现在命令中时， PostgreSQL将使用部分索引，如下命令所示：

EXPLAIN  
SELECT Buyer_id, first_name, last_name, email_address  
FROM Buyer  
WHERE active = 0;  

我们将在执行上述命令时得到以下消息，它显示了 Buyer 表的查询计划，因为 PostgreSQL 优化器正在使用idex_buyer_inactive索引。