- 从找出慢查询开始
- 按索引类型的选择标准
- EXPLAIN ANALYZE 的读法：实战场景
- Partial Index 与 Covering Index 的活用
- 用 hypopg 做索引的虚拟实验
- 未使用索引的检测与清理
- VACUUM 与索引性能的关系
- 索引 Bloat 的测量与 REINDEX
- PostgreSQL 17 中与索引相关的变更
- 故障排查：真实的错误与应对
- 索引调优检查清单
- 测验
- 参考资料
从找出慢查询开始
索引调优的第一步不是创建索引，而是定量地弄清楚究竟哪些查询有问题。在 PostgreSQL 17/18 环境下最值得信赖的工具是 pg_stat_statements。
-- 启用 pg_stat_statements 扩展（postgresql.conf 或 ALTER SYSTEM）
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- 修改后需要重启 PostgreSQL
-- 前 20 个慢查询：按总执行时间排序
SELECT
queryid,
substring(query, 1, 80) AS query_preview,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((stddev_exec_time)::numeric, 2) AS stddev_ms,
rows,
round((shared_blks_hit * 100.0 /
NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
这个查询里值得关注的列有三个。
- mean_ms：平均执行时间超过 50ms，在 OLTP 负载中就是危险信号。
- stddev_ms：标准差比平均值还大，意味着执行计划不稳定。可以怀疑统计信息漏更新或者参数嗅探。
- cache_hit_pct：低于 99% 时要检查 shared_buffers 的大小，或者确认索引是不是比表还宽。
按索引类型的选择标准
PostgreSQL 提供 B-tree、Hash、GiST、SP-GiST、GIN、BRIN 六种索引类型。实务中 90% 以上的场景都是在 B-tree 与 GIN 之间做决定。
| 索引类型 | 最佳使用场景 | 写入成本 | 大小 | 注意事项 |
|---|---|---|---|---|
| B-tree | 等值/范围检索、ORDER BY、UNIQUE 约束 | 低 | 一般 | 默认值。大多数情况下的最佳选择 |
| GIN | 数组、JSONB、全文检索（tsvector） | 高 | 大 | fastupdate=off 时写入延迟下降 |
| BRIN | 时序数据、append-only 表 | 非常低 | 非常小 | 物理顺序与逻辑顺序一致时才有效 |
| GiST | 地理数据、范围类型、邻近检索 | 一般 | 一般 | 与 PostGIS 搭配使用 |
| Hash | 只使用纯等值检索的场景 | 低 | 小 | PG 10 之后支持 WAL。无法做范围检索 |
复合索引的列顺序决定法
复合索引的列顺序由 WHERE 子句的选择性（selectivity）决定。一般把选择性高的列（唯一值多的列）放在前面，但也要看查询模式。
-- 确认各列的选择性
SELECT
attname,
n_distinct,
most_common_vals,
most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
AND attname IN ('status', 'user_id', 'created_at');
-- 结果示例：
-- status | n_distinct = 5 (选择性低 - 放在后面)
-- user_id | n_distinct = 50000 (选择性高 - 放在前面)
-- created_at | n_distinct = -0.95 (几乎唯一 - 若做范围检索则放最后)
以这些统计信息为基础来设计索引。
-- 模式 1: user_id 等值 + created_at 范围（最常见的 OLTP 模式）
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);
-- 模式 2: status 过滤 + user_id 检索（status 基数较低时）
-- Partial index 比复合索引更高效
CREATE INDEX CONCURRENTLY idx_orders_pending_user
ON orders (user_id)
WHERE status = 'pending';
-- 模式 3: JSONB 字段内部检索
CREATE INDEX CONCURRENTLY idx_orders_metadata
ON orders USING gin (metadata jsonb_path_ops);
EXPLAIN ANALYZE 的读法：实战场景
读 EXPLAIN 输出时最重要的，是“预估成本”与“实际时间”之间的落差。
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.user_id = 42
AND o.created_at >= '2026-02-01'
AND o.created_at < '2026-03-01'
ORDER BY o.created_at DESC
LIMIT 50;
执行计划结果的读法示例：
Limit (cost=0.56..125.43 rows=50 width=52)
(actual time=0.089..0.342 rows=50 loops=1)
-> Nested Loop (cost=0.56..4521.12 rows=1812 width=52)
(actual time=0.087..0.334 rows=50 loops=1)
-> Index Scan using idx_orders_user_created on orders o
(cost=0.43..3890.21 rows=1812 width=28)
(actual time=0.071..0.205 rows=50 loops=1)
Index Cond: ((user_id = 42) AND (created_at >= ...))
Buffers: shared hit=12
-> Index Scan using users_pkey on users u
(cost=0.13..0.35 rows=1 width=24)
(actual time=0.002..0.002 rows=1 loops=50)
Index Cond: (id = o.user_id)
Buffers: shared hit=100
Planning Time: 0.245 ms
Execution Time: 0.398 ms
需要确认的要点：
- Buffers: shared hit vs shared read：
shared hit是缓存命中，shared read是磁盘访问。read 多就要怀疑 shared_buffers 不足或索引低效。 - rows 预估值 vs 实际值：虽然预估为
rows=1812，但托 LIMIT 的福实际只扫描了 50 行。预估值相差 10 倍以上时需要重新执行ANALYZE。 - loops 值：Nested Loop 的 inner 侧出现
loops=50，说明重复执行了 50 次。显示的时间要乘上 loops 才是实际的总时间。
Partial Index 与 Covering Index 的活用
Partial Index：只索引数据的一部分
整张表有 1 亿行，而 status = 'pending' 的行只占 0.1% 时，建全量索引就是浪费。
-- 坏例子: 全量索引（1 亿行全部索引）
CREATE INDEX idx_orders_status ON orders (status, created_at);
-- 索引大小: ~2.1GB
-- 好例子: Partial index（只索引 10 万行）
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';
-- 索引大小: ~2.4MB（小 875 倍）
Covering Index (INCLUDE)：诱导 Index-Only Scan
PostgreSQL 11 起支持 INCLUDE 子句。把并非检索条件、但 SELECT 需要的列放进索引，从而消除对表堆的访问。
-- 频繁出现用 user_id 检索并返回 email、total_amount 的查询时
CREATE INDEX CONCURRENTLY idx_orders_user_covering
ON orders (user_id, created_at DESC)
INCLUDE (total_amount, status);
-- 有了这个索引就能走 Index Only Scan
-- 但前提是 visibility map 必须被更新，所以 VACUUM 很重要
用 hypopg 做索引的虚拟实验
在生产环境创建索引之前，可以用 hypopg 扩展创建虚拟索引，再用 EXPLAIN 验证效果。既不占用真实磁盘空间，也不会给表加锁。
-- 安装 hypopg
CREATE EXTENSION IF NOT EXISTS hypopg;
-- 创建虚拟索引
SELECT * FROM hypopg_create_index(
'CREATE INDEX ON orders (user_id, created_at DESC) INCLUDE (total_amount)'
);
-- 结果: indexrelid = 14356, indexname = '<14356>btree_orders_user_id_created_at'
-- 确认应用了虚拟索引后的 EXPLAIN
EXPLAIN SELECT user_id, created_at, total_amount
FROM orders
WHERE user_id = 42
AND created_at >= '2026-02-01'
ORDER BY created_at DESC;
-- Index Only Scan using <14356>btree_orders_user_id_created_at
-- 删除虚拟索引
SELECT hypopg_drop_index(14356);
-- 一次性清理所有虚拟索引
SELECT hypopg_reset();
真正的索引，只在虚拟实验里确认成本确实下降之后才动手创建。
未使用索引的检测与清理
索引提升读取性能，却拖累写入性能。每一次 INSERT/UPDATE/DELETE 都要更新所有相关索引，因此用不到的索引就是纯粹的成本。
-- 检测 30 天以上未被使用的索引
-- （pg_stat_user_indexes 是自上次统计重置以来的累计值）
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS scan_count,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT indexrelid FROM pg_index WHERE indisunique
)
ORDER BY pg_relation_size(indexrelid) DESC;
注意事项：
- UNIQUE 索引要排除在外：它承担着约束的角色，即使 scan 为 0 也不能删除。
- 刚重置完统计信息时不要下判断：
pg_stat_reset()之后至少收集 4 周的数据。 - 要考虑月末/季末的批处理：可能存在只在特定时期才被使用的索引。
-- 删除索引前务必先用 CONCURRENTLY 尝试
-- （普通的 DROP INDEX 会对表加 ACCESS EXCLUSIVE 锁）
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_old_status;
VACUUM 与索引性能的关系
Dead tuple 一旦堆积，索引性能就会急剧劣化。Index-Only Scan 依赖 visibility map，而 VACUUM 延迟会让 visibility map 得不到更新，于是发生堆访问。
-- 确认各表的 dead tuple 比例与最后一次 vacuum 的时间点
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2)
AS dead_pct,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
dead tuple 比例超过 20% 时，就必须检查 autovacuum 的配置。
-- 针对大表单独调优 autovacuum
ALTER TABLE orders SET (
autovacuum_vacuum_threshold = 1000,
autovacuum_vacuum_scale_factor = 0.01, -- 比默认值 0.2 敏感 20 倍
autovacuum_analyze_threshold = 500,
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 2 -- 默认 2ms，设为 0 则全速
);
索引 Bloat 的测量与 REINDEX
长期运行的索引会因为 page split 和删除而产生 bloat。PostgreSQL 17 默认启用了 B-tree 索引的 deduplication，bloat 有所减少，但定期检查依然必要。
-- 用 pgstattuple 扩展测量索引 bloat
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
indexrelid::regclass AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
round(100 - (avg_leaf_density)::numeric, 2) AS bloat_pct
FROM pg_stat_user_indexes,
LATERAL pgstatindex(indexrelid::regclass::text) AS s
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024 -- 只看 100MB 以上
ORDER BY bloat_pct DESC
LIMIT 10;
bloat 超过 30% 时就要考虑 REINDEX。
-- PostgreSQL 14+ : REINDEX CONCURRENTLY（不中断服务）
REINDEX INDEX CONCURRENTLY idx_orders_user_created;
-- PostgreSQL 12+ : 一次性重建整张表的索引
REINDEX TABLE CONCURRENTLY orders;
PostgreSQL 17 中与索引相关的变更
PostgreSQL 17 里对索引调优有影响的主要变更：
- B-tree multi-value lookup 改进：
IN (val1, val2, ..., valN)查询中，位于同一 leaf page 的值可以用一次扫描取回。以前需要 N 次独立探查。 - streaming I/O：Sequential scan 与 VACUUM 使用 Read Stream API，一次读取多个缓冲区。
effective_io_concurrency参数的影响力变得更大。 - VACUUM 内存管理改进：追踪 dead tuple 所需的内存最多减少 20 倍，
maintenance_work_mem得以被更高效地使用。 - WAL 并发吞吐提升 2 倍：在高并发写入环境下，索引的维护成本降低了。
-- 在 PostgreSQL 17 中效果变大的配置
ALTER SYSTEM SET effective_io_concurrency = 200; -- NVMe SSD 的话设为 200
ALTER SYSTEM SET maintenance_io_concurrency = 100; -- 供 VACUUM/CREATE INDEX 使用
ALTER SYSTEM SET huge_pages = try; -- shared_buffers 很大时减少 TLB miss
SELECT pg_reload_conf();
故障排查：真实的错误与应对
场景 1：明明有索引却走了 Seq Scan
-- 有问题的查询
EXPLAIN SELECT * FROM orders WHERE created_at::date = '2026-03-01';
-- 结果: Seq Scan on orders（因为套了函数而使索引失效）
-- 解决: 去掉函数，改为范围条件
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-03-01'
AND created_at < '2026-03-02';
-- 结果: Index Scan using idx_orders_created_at
其他原因：
- 检查
enable_indexscan = off设置（SHOW enable_indexscan;） - 统计信息陈旧：执行
ANALYZE orders; - 表太小，Seq Scan 反而更快的情况（正常）
场景 2：CREATE INDEX CONCURRENTLY 失败
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890;
blocked by process 23456.
CONCURRENTLY 建索引会做两阶段的表扫描，其间只要存在 long-running transaction 就会失败。
-- 确认 long-running transaction
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;
-- 必要时终止相应会话
SELECT pg_terminate_backend(12345);
-- 清理失败后残留的 INVALID 索引
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_orders_failed;
场景 3：升级后查询计划变化导致性能下降
PostgreSQL 大版本升级时，planner 的统计信息会被重置，cost 模型也会改变。PostgreSQL 18 加入了保留统计信息的功能，但 17 及以下需要手动应对。
# 升级之后立即重新收集整个数据库的统计信息
vacuumdb --analyze-in-stages --all --jobs=4
# 第 1 阶段: 收集最小统计信息 (default_statistics_target = 1)
# 第 2 阶段: 收集基本统计信息 (default_statistics_target = 10)
# 第 3 阶段: 收集完整统计信息 (default_statistics_target = 当前设置值)
索引调优检查清单
在生产环境做索引调优时务必确认的项目：
- 已用
pg_stat_statements识别出前 20 个慢查询 - 对每个目标查询执行
EXPLAIN (ANALYZE, BUFFERS)并找出瓶颈 - 新索引候选已用
hypopg做过虚拟验证 - 确认可以应用 Partial index 的条件（status 过滤等）
- 列出未使用索引的清单并制定删除计划
- 为 bloat 超过 30% 的项目预约 REINDEX CONCURRENTLY
- 检查 autovacuum 配置是否适合大表
- 使用
CREATE INDEX CONCURRENTLY保证服务不中断 - 升级前保存核心查询的执行计划快照
- 把回滚方案写成文档（删除索引 -> 性能下降时的恢复计划）
测验
Q1. pg_stat_statements 中 mean_exec_time 与 stddev_exec_time 分别为 30ms、150ms 时，
应该怀疑什么？
答案: ||标准差是平均值的 5 倍，说明执行计划不稳定。很可能是随参数不同而选中了不同的计划 （generic plan vs custom plan），或者统计信息 stale 导致 planner 的预估与实际相差很大。||
Q2. Partial index 比普通复合索引更有利的条件是什么？
答案: ||当 WHERE 条件中的特定值只占全部数据的少数（例如低于 5%）时，Partial index 更有利。
索引大小急剧变小，缓存效率随之提高，写入负担也下降。||
Q3. 使用 INCLUDE 子句的 Covering index 无法保证 Index-Only Scan 的情况是什么？
答案: ||是 visibility map 没有被更新的情况。VACUUM 延迟导致相应页面没有被标记为 all-visible 时， PostgreSQL 必须直接确认堆，于是从 Index-Only Scan 退化为 Index Scan。||
Q4. CREATE INDEX CONCURRENTLY 比普通 CREATE INDEX 慢的原因是什么？
答案: ||CONCURRENTLY 会扫描表两次。第一次扫描构建索引，第二次扫描反映第一次扫描之后发生变化的行。
而且它只持有 ShareUpdateExclusiveLock，所以还必须监视与其他事务之间的冲突。||
Q5. PostgreSQL 17 中 IN 子句查询的 B-tree 扫描得以改进的原理是什么？
答案: ||位于同一 leaf page 的多个值可以用一次页面访问取回。以前 IN 列表里的每个值都要从 root 到
leaf 独立探查，而 PG 17 会把共享同一 leaf page 的值打包处理。||
Q6. 测量索引 bloat 时，如果 pgstattuple 的 avg_leaf_density 为 60%，该如何解读？
答案: ||意味着 leaf 页面有 40% 是空的，也就是 bloat 为 40% 的状态。已经超过 30%，因此要考虑 REINDEX CONCURRENTLY。新建索引的正常 leaf density 通常在 90% 左右。||
Q7. 把 autovacuum_vacuum_scale_factor 从 0.2 改成 0.01 会有什么效果？
答案: ||dead tuple 只堆积到全部行的 1% 就会触发 autovacuum（比默认值 20% 敏感 20 倍）。 在大表上提前清理 dead tuple 的堆积，可以防止索引性能劣化与表 bloat。||
参考资料
현재 단락 (1/266)
索引调优的第一步不是创建索引，而是定量地弄清楚究竟哪些查询有问题。在 PostgreSQL 17/18 环境下最值得信赖的工具是 `pg_stat_statements`。