“加了索引为什么还慢？”——这是 DBA 和后端工程师被问最多的问题。答案通常不是“索引没用”，而是“用错了索引类型”或“索引没被命中”。本文把 PostgreSQL 索引体系拆开来讲，给出一份可落地的选型与调优指南。

一、PostgreSQL 的七种索引类型

类型	适用场景	备注
B-Tree	默认，等值/范围/排序	大多数场景的默认选择
Hash	仅等值	不支持范围，PG 10+ 支持 WAL
GiST	几何、全文、范围	通用搜索树
GIN	数组、JSONB、全文	多值字段，但索引大
BRIN	大表、自然有序	块级范围索引，超小
SP-GiST	空间分区	不平衡树（如 IP 路由）
BLOOM	多列等值	实验性，少用

绝大多数业务用 B-Tree + GIN + BRIN 三种就够。

二、B-Tree：不是“加了就快”

B-Tree 默认就创建，但有三个常见误区：

1. 索引列顺序决定能否命中

-- 复合索引 (a, b, c)
CREATE INDEX idx_user ON users(dept, status, created_at);

-- ✅ 命中
SELECT * FROM users WHERE dept = 'eng' AND status = 1;
SELECT * FROM users WHERE dept = 'eng';
SELECT * FROM users WHERE dept = 'eng' AND status = 1 AND created_at > '2026-01-01';

-- ❌ 不命中（跳过了 dept）
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE created_at > '2026-01-01';

最左前缀原则：复合索引必须从最左列开始连续使用。索引列顺序应按“过滤性从高到低”排，即 dept（部门枚举值少但选择性好）放前面，created_at（范围过滤）放后面。

2. 函数会让索引失效

-- ❌ 索引失效：对列做了运算
SELECT * FROM users WHERE DATE(created_at) = '2026-07-01';

-- ✅ 改写为范围
SELECT * FROM users
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02';

如果业务确实需要按函数查询，用表达式索引：

CREATE INDEX idx_users_date ON users((DATE(created_at)));
SELECT * FROM users WHERE DATE(created_at) = '2026-07-01';  -- 现在能命中

3. LIKE 模式与 pg_trgm

-- 默认 B-Tree 不支持前缀以外的 LIKE
SELECT * FROM articles WHERE title LIKE '%数据库%';  -- 全表扫描

-- 启用 pg_trgm 扩展 + GIN 索引
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops);

SELECT * FROM articles WHERE title LIKE '%数据库%';  -- 命中 trgm 索引

中文场景下 gin_trgm_ops 对单字搜索效果好，对短词（2-3 字）效果一般，需要测试。

三、GIN：JSONB 与数组的最佳搭档

CREATE TABLE events (
  id BIGSERIAL,
  payload JSONB,
  tags TEXT[]
);

-- JSONB GIN 索引
CREATE INDEX idx_events_payload ON events USING GIN(payload);

-- 数组 GIN 索引
CREATE INDEX idx_events_tags ON events USING GIN(tags);

-- 查询
SELECT * FROM events WHERE payload @> '{"type": "click"}';
SELECT * FROM events WHERE tags && ARRAY['login', 'signup'];

@>（包含）、?（存在键）、&&（数组交集）这些操作符都会走 GIN 索引。

jsonb_path_ops：更小更快但操作符少

CREATE INDEX idx_events_payload_path ON events USING GIN(payload jsonb_path_ops);

-- 只支持 @> 操作符，但索引大小约为默认的 1/3

如果只用 @>，jsonb_path_ops 是更好的选择。

四、BRIN：大表的“几乎免费”索引

BRIN（Block Range Index）只存储每个数据块的“最小/最大值”，索引大小可以小到 B-Tree 的 1/1000。

-- 时间序列日志表，按时间自然有序
CREATE TABLE logs (
  id BIGSERIAL,
  created_at TIMESTAMP NOT NULL,
  level TEXT,
  msg TEXT
);

-- BRIN 索引，几乎不占空间
CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at);

-- 查询：BRIN 帮助跳过大部分块
SELECT * FROM logs
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02';

适用条件：

• 表非常大（>1亿行）
• 列与物理顺序强相关（时间序列、自增 ID）
• 容忍“略多一点”的扫描（BRIN 是有损索引）

不适合：随机写入、列值与物理位置无关的场景。

五、EXPLAIN ANALYZE：读不懂就调不了

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;

关键字段：

• cost=0.42..8.45：启动成本..总成本（无量纲，相对值）
• rows=1：预估行数
• actual time=0.05..0.07：实际耗时（ms）
• rows=1 (loops=1)：实际行数
• Buffers: shared hit=5 read=2：缓存命中情况

重点看的几个信号：

信号	含义	优化方向
Seq Scan	全表扫描	加索引或改写查询
actual rows 远大于 estimated rows	统计信息过期	ANALYZE 表
Sort 节点开销大	排序未走索引	加 ORDER BY 列的索引
Hash Join 而 Hash 很大	哈希表溢出	增加 work_mem
Bitmap Heap Scan	多行定位后回表	检查是否可改成 Index Scan

定期更新统计信息

-- 手动 analyze
ANALYZE orders;

-- 或自动（autovacuum 一般已开启）
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.05);

统计信息过期会让优化器选错执行计划——这是“昨天还快、今天突然慢”的常见原因。

六、部分索引：只索引你关心的行

-- 反例：把所有订单都索引
CREATE INDEX idx_orders_unpaid ON orders(user_id) WHERE status = 'unpaid';

-- 正例：只索引未支付订单（通常占少数）
CREATE INDEX idx_orders_unpaid ON orders(user_id) WHERE status = 'unpaid';

部分索引在“状态字段有少数活跃值”的场景效果拔群：索引小、写放大低、查询快。

七、覆盖索引：避免回表

-- 包含额外列，避免回表查询
CREATE INDEX idx_orders_user_covering ON orders(user_id, created_at)
  INCLUDE (amount, status);

-- 这个查询可以完全在索引里完成
SELECT user_id, created_at, amount, status
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;

INCLUDE 列不参与索引排序，只存值，更新成本低。配合 Index Only Scan 性能极好。

八、索引的代价：写放大

每加一个索引，写入就要多更新一棵 B-Tree。监控索引使用情况：

-- 找出从未被使用的索引（基于统计）
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

长期 idx_scan = 0 的索引可以删掉，省维护成本。

九、实战案例：从 8s 到 50ms

业务场景：1 亿行的订单表，按 user_id + status 查询最近 20 条。

-- 原始查询
SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- 8.2s，Seq Scan + Sort

-- 优化 1：复合索引（顺序错了！）
CREATE INDEX idx_orders ON orders(status, user_id, created_at DESC);
-- 还是 6s，因为先按 status 过滤再按 user_id 排序

-- 优化 2：调整顺序 + 部分索引
CREATE INDEX idx_orders_paid ON orders(user_id, created_at DESC)
  WHERE status = 'paid';
-- 50ms，Index Scan + Limit

关键洞察：status = 'paid' 是常量条件，放进 WHERE 让索引只包含 paid 订单；user_id 在前让等值查询直接定位；created_at DESC 让 ORDER BY 走索引顺序。

结语

索引优化不是玄学，是建立在“理解数据分布 + 会读 EXPLAIN + 知道每种索引适用场景”上的工程活。掌握三个原则：

1. 先 EXPLAIN ANALYZE 看真实计划，不靠猜
2. 索引列顺序遵循“等值-范围-排序”的左前缀原则
3. 大表用 BRIN，JSONB/数组用 GIN，长尾用部分索引

把这三条做到位，80% 的“慢查询”都能在不改业务代码的前提下解决。