利用 PostgreSQL 部分索引提高查询语句的性能

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目录

大家好，我是只谈技术不剪发的 Tony 老师。

在 PostgreSQL 数据库中，部分索引（partial index）是指对表中满足特定条件的数据行进行索引。由于它不需要对全部数据进行索引，因此索引会更小，在特定场景下通过部分索引查找数据时性能会更好。本文就给大家介绍一下 PostgreSQL 中的部分索引功能。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁

PostgreSQL 在创建索引时可以通过一个 WHERE 子句指定需要索引的数据行，从而创建一个部分索引。例如，对于以下订单表 orders：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  status TEXT
);

INSERT INTO orders (id, customer_id, status)
SELECT
  i,
  (random()*10000)::INT,
  CASE (random() * 100)::int
    WHEN 0 THEN 'pending'
    WHEN 1 THEN 'shipped'
    ELSE 'completed'
  END
    FROM generate_series(1, 1000000) i;



该表中总共有 1000000 个订单，通常绝大部的订单都处于完成状态。一般情况下，我们只需要针对某个用户未完成的订单进行查询跟踪，因此可以创建一个基于用户编号和状态的部分索引：

CREATE INDEX full_idx ON orders (customer_id, status);



然后使用 EXPLAIN ANALYZE 命令查看 SELECT 语句的执行计划：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 5678
AND status != 'completed';

QUERY PLAN                                                                                                           |
---------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on orders  (cost=5.18..369.08 rows=1 width=17) (actual time=33.661..34.040 rows=1 loops=1)          |
  Recheck Cond: (customer_id = 5678)                                                                                 |
  Filter: (status <> 'completed'::text)                                                                              |
  Rows Removed by Filter: 109                                                                                        |
  Heap Blocks: exact=109                                                                                             |
  ->  Bitmap Index Scan on full_idx  (cost=0.00..5.17 rows=100 width=0) (actual time=33.526..33.526 rows=110 loops=1)|
        Index Cond: (customer_id = 5678)                                                                             |
Planning Time: 1.252 ms                                                                                              |
Execution Time: 34.180 ms                                                                                            |


输出结果显示利用索引 full_idx 扫描了 110 行，然后通过 status 过滤掉了 109 行，而不是直接通过索引扫描出所需的数据。

此时，我们可以查看一下索引 full_idx 占用的空间大小：

select pg_size_pretty(pg_table_size('full_idx'));

pg_size_pretty|
--------------|
30 MB         |


接下来我们再创建一个部分索引，只包含未完成的订单数据，从而减少索引的数据量：

CREATE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';


索引 partial_idx 中只有 customer_id 字段，不需要 status 字段。然后再次执行相同的 EXPLAIN ANALYZE 语句，查看执行计划：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 5678
AND status != 'completed';

QUERY PLAN                                                                                                         |
-------------------------------------------------------------------------------------------------------------------|
Index Scan using partial_idx on orders  (cost=0.29..8.30 rows=1 width=17) (actual time=0.246..0.249 rows=1 loops=1)|
  Index Cond: (customer_id = 5678)                                                                                 |
Planning Time: 0.397 ms                                                                                            |
Execution Time: 0.295 ms                                                                                           |



输出结果显示 PostgreSQL 执行计划选择了索引 partial_idx，而不是 full_idx；因为这样性能更好，只需要扫描 1 行记录就可以得到结果。

同样可以查看一下索引 partial_idx 占用的空间大小：

select pg_size_pretty(pg_table_size('partial_idx'));

pg_size_pretty|
--------------|
352 kB        |


索引只有 352 KB，而不是 30 MB，因为绝大多数订单都处于完成状态。

另外，部分索引还可以用于实现其他的功能。例如，我们可以将索引 partial_idx 定义为唯一索引，从而实现每个用户只能存在一个未完成订单的约束。

DROP INDEX partial_idx;
TRUNCATE TABLE orders;

CREATE UNIQUE INDEX partial_idx ON orders (customer_id)
WHERE status != 'completed';

INSERT INTO orders(id, customer_id, status) VALUES (1, 1, 'pending');

INSERT INTO orders(id, customer_id, status) VALUES (2, 1, 'pending');
SQL 错误 [23505]: 错误: 重复键违反唯一约束"partial_idx"
  详细：键值"(customer_id)=(1)" 已经存在



用户必须完成一个订单之后才能继续生成新的订单。

    📝更多关于 PostgreSQL 索引和优化的内容，可以参考这篇文章。