PostgreSQL索引类型详解
PostgreSQL 提供了丰富的索引类型，远超大多数关系型数据库。合理选择索引类型可以为不同的查询模式提供最优性能。本文详细介绍每种索引的原理、适用场景和创建方法。
索引类型概览
B-tree 索引
B-tree 是默认且最常用的索引类型，支持等值查询、范围查询和排序。
-- 创建 B-tree 索引（默认类型，可以省略 USING btree）
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_email ON users USING btree (email);
-- 复合索引
CREATE INDEX idx_users_name_age ON users (name, age);
-- 降序索引（用于 ORDER BY ... DESC）
CREATE INDEX idx_users_created_desc ON users (created_at DESC NULLS LAST);支持的操作符：<, <=, =, >=, >, BETWEEN, IN, IS NULL, IS NOT NULL
B-tree 特点：
- 平衡树结构，查询复杂度 O(log N)
- 叶子节点双向链表，支持正反向扫描
- 支持前缀匹配
LIKE 'abc%'，不支持后缀匹配LIKE '%abc'
Hash 索引
Hash 索引仅支持等值查询(=)，但在 PostgreSQL 10 之后已经支持 WAL 日志和复制。
CREATE INDEX idx_users_session ON sessions USING hash (session_id);
-- 适用场景：大量等值查询且不需要范围查询
SELECT * FROM sessions WHERE session_id = 'abc123';| 维度 | B-tree | Hash |
|---|---|---|
| 等值查询 | O(log N) | O(1) |
| 范围查询 | 支持 | 不支持 |
| 排序 | 支持 | 不支持 |
| 索引大小 | 较大 | 较小 |
| WAL 支持 | 是 | 是(PG 10+) |
GiST 索引 (Generalized Search Tree)
GiST 是通用搜索树框架，支持多种数据类型的复杂查询。
-- 几何查询（PostGIS）
CREATE INDEX idx_locations_geom ON locations USING gist (geom);
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(121.47, 31.23)::geography, 1000);
-- 范围类型查询
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INT,
period TSRANGE -- 时间段类型
);
CREATE INDEX idx_reservations_period ON reservations USING gist (period);
-- 查询重叠的预订
SELECT * FROM reservations
WHERE period && '[2025-05-14 10:00, 2025-05-14 12:00)';
-- 排他约束（GiST 独有功能）
ALTER TABLE reservations
ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, period WITH &&);
-- 全文搜索
CREATE INDEX idx_articles_fts ON articles USING gist (to_tsvector('english', content));SP-GiST 索引 (Space-Partitioned GiST)
SP-GiST 适合非平衡数据结构，如四叉树、k-d 树、前缀树。
-- 适合的数据分布：聚集但不均匀
CREATE INDEX idx_points ON points USING spgist (location);
-- 文本前缀搜索
CREATE INDEX idx_urls ON urls USING spgist (url text_ops);
SELECT * FROM urls WHERE url LIKE 'https://example.com/%';
-- IP 地址范围
CREATE INDEX idx_ips ON access_log USING spgist (client_ip inet_ops);GIN 索引 (Generalized Inverted Index)
GIN 是通用倒排索引，适合一个列中包含多个值的场景。
-- 数组查询
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[]
);
CREATE INDEX idx_articles_tags ON articles USING gin (tags);
-- 包含特定标签
SELECT * FROM articles WHERE tags @> ARRAY['database', 'postgresql'];
-- 与任一标签重叠
SELECT * FROM articles WHERE tags && ARRAY['redis', 'mongodb'];
-- JSONB 查询
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB
);
CREATE INDEX idx_events_data ON events USING gin (data);
-- 使用 jsonb_path_ops 操作符类（更紧凑，仅支持 @>）
CREATE INDEX idx_events_data_path ON events USING gin (data jsonb_path_ops);
-- 查询 JSONB
SELECT * FROM events WHERE data @> '{"type": "click", "page": "home"}';
-- 全文搜索（GIN 比 GiST 更快但更新更慢）
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('english', content));GIN vs GiST 全文搜索对比
| 维度 | GIN | GiST |
|---|---|---|
| 查询速度 | 快(精确匹配) | 较慢(可能有误匹配) |
| 索引大小 | 较大 | 较小(有损压缩) |
| 构建速度 | 慢 | 快 |
| 更新速度 | 慢(需更新倒排列表) | 快 |
| 适用场景 | 读多写少 | 写多或索引大小敏感 |
GIN 的 Fast Update
-- GIN 的 pending list 优化（延迟更新倒排索引）
ALTER INDEX idx_articles_tags SET (fastupdate = on);
-- 新数据先写入 pending list，后续批量合并到主索引
-- pending list 大小限制
ALTER INDEX idx_articles_tags SET (gin_pending_list_limit = 64); -- MBBRIN 索引 (Block Range Index)
BRIN 索引对表的物理块范围建立索引，适合数据与物理存储顺序高度相关的大表。
-- 时序数据（日志、监控数据，按时间顺序插入）
CREATE TABLE sensor_data (
id BIGSERIAL,
sensor_id INT,
reading FLOAT,
created_at TIMESTAMP DEFAULT NOW()
);
-- BRIN 索引（极小的存储开销）
CREATE INDEX idx_sensor_created ON sensor_data USING brin (created_at);
-- pages_per_range: 每个索引条目覆盖多少页
-- 默认 128 页，可调整
CREATE INDEX idx_sensor_created ON sensor_data
USING brin (created_at) WITH (pages_per_range = 64);BRIN 优势：索引大小极小（可能只有 B-tree 的 1/100），适合 TB 级别的时序大表。
BRIN 前提条件：数据在物理上有序。如果数据随机分布，BRIN 效果极差。
部分索引 (Partial Index)
只对满足条件的行建立索引，减小索引大小。
-- 仅对活跃用户建立索引
CREATE INDEX idx_users_active ON users (email)
WHERE status = 'active';
-- 仅对未处理的订单建立索引
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- 查询必须包含匹配的 WHERE 条件才能使用部分索引
SELECT * FROM users WHERE email = 'a@b.com' AND status = 'active';
-- 可以使用 idx_users_active
SELECT * FROM users WHERE email = 'a@b.com';
-- 不能使用 idx_users_active（缺少 status 条件）表达式索引 (Expression Index)
对计算表达式建立索引。
-- 大小写不敏感查询
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 日期部分
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM created_at));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025;
-- JSONB 字段
CREATE INDEX idx_data_type ON events ((data->>'type'));
SELECT * FROM events WHERE data->>'type' = 'click';覆盖索引 (Covering Index, INCLUDE)
PostgreSQL 11+ 支持 INCLUDE 子句，将额外的列存储在索引叶子节点中，实现 Index-Only Scan。
-- INCLUDE 的列不参与索引搜索，只是附带存储
CREATE INDEX idx_orders_user_include ON orders (user_id)
INCLUDE (amount, status);
-- 以下查询可以 Index-Only Scan，无需回表
SELECT user_id, amount, status FROM orders WHERE user_id = 1001;Index-Only Scan
即使不使用 INCLUDE，如果查询只需要索引中的列，也可能使用 Index-Only Scan。但需要可见性映射(Visibility Map)的支持。
-- 查看是否使用 Index-Only Scan
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM users WHERE id > 100;
-- 如果 Heap Fetches 很高，说明 VM 不够新
-- 运行 VACUUM 更新 VM
VACUUM users;并发创建索引
-- 普通创建（阻塞写入）
CREATE INDEX idx_name ON big_table (column);
-- 并发创建（不阻塞写入，但耗时更长）
CREATE INDEX CONCURRENTLY idx_name ON big_table (column);
-- 并发删除
DROP INDEX CONCURRENTLY idx_name;
-- 注意：CONCURRENTLY 不能在事务块中使用
-- 如果创建失败，索引会处于 INVALID 状态
-- 查看无效索引
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;
-- 删除无效索引
DROP INDEX idx_name;索引选择指南
总结
| 索引类型 | 最佳场景 | 索引大小 | 查询速度 | 写入开销 |
|---|---|---|---|---|
| B-tree | 通用查询 | 中等 | 快 | 中等 |
| Hash | 大量等值查询 | 较小 | 最快(等值) | 低 |
| GiST | 几何/范围/全文 | 较小 | 中等 | 低 |
| SP-GiST | 聚集数据/前缀 | 较小 | 快 | 低 |
| GIN | 多值列/JSONB/全文 | 较大 | 最快(搜索) | 高 |
| BRIN | 有序大表 | 极小 | 中等 | 极低 |
合理组合使用不同索引类型，配合部分索引、表达式索引和覆盖索引，可以充分发挥 PostgreSQL 的查询性能优势。
贡献者
更新日志
2026/3/14 13:09
查看所有更新日志
9f6c2-于