PostgreSQL 教程: 部分索引

九月 11, 2023

摘要：在本教程中，您将学习如何使用 PostgreSQL 部分索引来提高查询性能，同时减小索引大小。

到目前为止，您已经了解了如何将一列或多列的值添加到索引中。PostgreSQL 部分索引甚至允许您指定表中应建立索引的行。这种部分索引有助于加快查询速度，同时减少索引的大小。

如果您有使用常量值的常用WHERE条件，则部分索引非常有用，如下所示：

SELECT * 
FROM table_name
WHERE column_name = constant_value;

让我们看一下示例数据库中的customer表：

customer table

例如，您通常对不活跃的客户感兴趣，并经常进行一些后续行动以让他们回来购买更多东西。

以下查询查找所有不活动的客户：

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

要执行此查询，查询规划器需要扫描customer表，如以下EXPLAIN语句所示：

EXPLAIN SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

这是输出：

PostgreSQL Partial Index - Query Plan without index

您可以通过为active列创建索引来优化此查询，如下所示：

CREATE INDEX idx_customer_active
ON customer(active);

该索引实现了其目的，但是，它包含许多从未搜索过的行，即所有活跃客户。

要定义仅包含非活动客户的索引，请使用以下语句：

CREATE INDEX idx_customer_inactive
ON customer(active)
WHERE active = 0;

从现在开始，只要WHERE子句出现在查询中，PostgreSQL 就会考虑部分索引：

EXPLAIN SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM
    customer
WHERE
    active = 0;

下面显示了输出：

PostgreSQL Partial Index - Query Plan with an index

定义部分索引的语法非常简单：

CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;

在此语法中，WHERE子句指定应将哪些行添加到索引中。

在本教程中，您了解了 PostgreSQL 部分索引以及如何使用它来指定应添加到索引的行。

搜索

分类

标签