PostgreSQL 部分索引

PostgreSQL 允许您创建部分索引。部分索引由助于减少索引大小，因为它没有为列中的所有数据创建索引，它只为符合条件的行中的数据建立索引。

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

SELECT *
FROM table_name
WHERE column_name = constant_value;

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

\d customer

                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "customer_lower_idx" btree (lower(last_name::text))
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)

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

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

SELECT *
FROM customer
WHERE active = 0;

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

EXPLAIN
SELECT *
FROM customer
WHERE active = 0;

                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..15.49 rows=15 width=70)
   Filter: (active = 0)
(2 rows)

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

CREATE INDEX idx_customer_active
ON customer(active);

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

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

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

该语句定义了一个部分索引 idx_customer_inactive，只有符合 WHERE 条件 active = 0 中的 active 值才会被索引。相比较为所有 active 值建立的索引 idx_customer_active，此索引的大小会很小。

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

EXPLAIN
SELECT *
FROM customer
WHERE active = 0;

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Index Scan using idx_customer_active on customer  (cost=0.15..11.69 rows=15 width=70)
   Index Cond: (active = 0)
(2 rows)

结论

本文介绍了 PostgreSQL 部分索引以及如何使用它来指定应添加到索引中的行。

结论

本页目录