赞
踩
文章目录
在 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 语句,查看执行计划:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。