PostgreSQL 局部索引(Partial Index)教程

PostgreSQL 局部索引(Partial Index)教程

本文介绍局部索引，用于提升查询性能，减少索引大小。

1. 应用场景

局部索引一般应用在where条件中使用常来值得场景。

SELECT * 
FROM table_name
WHERE column_name = constant_value;

假设有customer表,字段如下：

customer(customer_id,first_name,last_name,email,address_id,create_date,last_update,active)

我们主要对不活跃客户感兴趣，希望及时找到并进行回访。下面查询不活跃客户：

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

如果执行该查询，使用explain进行分析：

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

因为没有索引，需要顺序扫描全表。因此我们对active字段创建索引：

CREATE INDEX idx_customer_active
ON customer(active);

这个索引可以达到目的，但包括所有客户，即也包括活跃客户。

2. 局部索引

这里定义索引仅包括非活跃用户记录，使用下面语句创建索引：

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;

执行解释后可以看到使用到局部索引。局部索引定义直接明了：

CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;

上面where子句指定那些行记录加入索引。

3. 总结

本文介绍了PostgreSQL局部索引，以及如何使用局部索引提升检索性能。