当前位置：首页 > java >正文

PostgreSQL 索引使用分析2

java 2025/9/4 10:13:03

1. 背景：一次真实的性能调优案例

最近在对某张业务表 dma.dma_oprt_psi_item_detail 做性能调优时，遇到了一系列典型问题：

执行计划显示走了索引，但回表过滤掉了 99% 的数据
pg_stat_user_indexes 中大部分索引 idx_scan=0，但不敢轻易删除
试图手动重置统计信息，却报错 must be system admin
执行计划只有 Coordinator 层面的 Data Node Scan，无法确认 DN 层是否真正走索引

这些问题促使我深入研究了 PostgreSQL 索引使用分析的正确姿势，特此整理成文，供大家参考。

2. 索引使用分析的三大误区

2.1 误区一：`pg_stat_user_indexes` 是实时且历史累计的？

真相：

非实时：统计信息默认每 1s~1min 更新一次，取决于 stats_update_min_interval 和 autovacuum_naptime
非历史累计：计数器从 最近一次 pg_stat_reset() 或实例重启 开始累计

示例验证

-- 查看统计信息重置时间
SELECT stats_reset
FROM pg_stat_database
WHERE datname = current_database();

2.2 误区二：`idx_tup_fetch=0` 就说明索引无效？

真相：

idx_tup_fetch=0 只能说明 索引扫描后没有返回行，不代表索引没被使用
可能是 过滤条件太严格 或 数据分布问题
正确做法是结合 EXPLAIN (ANALYZE, BUFFERS) 一起看

2.3 误区三：Coordinator 层的执行计划能看到索引？

真相：

Coordinator 层的 Data Node Scan on "__REMOTE_FQS_QUERY__" 只是 SQL 下推，看不到索引细节
必须到 DN 层 执行 EXPLAIN (ANALYZE, BUFFERS) 才能确认是否走索引

3. 正确分析索引使用情况的四步法

3.1 第一步：用 EXPLAIN 初步验证

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM dma.dma_oprt_ps_detail
WHERE dealer_id = 693AND confirm_time BETWEEN '2025-04-01' AND '2025-10-01'AND inspect_item_result = '不合格'AND inspect_status = '待整改'AND not_rectify_cnt_flag = 1;

出现 Index Scan、Bitmap Index Scan、Index Only Scan 才算真正走索引
同时关注 Rows Removed by Filter，判断回表代价

3.2 第二步：查询统计信息

SELECT indexrelname,idx_scan,idx_tup_read,idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'dma'AND relname = 'dma_oprt_ps_detail'
ORDER BY idx_scan DESC;

观察 idx_scan 增长趋势，而非单次值
对 idx_scan=0 的索引，至少观察一个业务周期 再决定是否删除

3.3 第三步：手动刷新统计信息（可选）

sql

复制

-- 需要 superuser
SELECT pg_stat_reset_single_table_counters('dma.dma_oprt_ps_detail'::regclass);
-- 或全局重置（慎用）
SELECT pg_stat_reset();

普通账号会报错：must be system admin
无需刻意重置，统计信息会自动更新

3.4 第四步：长期监控

定期采样 pg_stat_user_indexes 到业务监控表
结合 EXPLAIN 结果，建立索引使用画像

4. 实战：如何优化低效索引

4.1 现状

索引名	idx_scan	是否有效
idx_confirm_time_dealer_id	18	⚠️ 低效
idx_fst_construction_pass_time_dealer_id	0	❌ 无效
...	...	...

4.2 优化方案

删除无效索引（观察一个周期后）

DROP INDEX idx_fst_construction_pass_time_dealer_id;

创建复合覆盖索引

CREATE INDEX idx_dma_covering
ON dma.dma_oprt_ps_detail
(dealer_id, confirm_time, inspect_item_result, inspect_status, not_rectify_cnt_flag);

验证效果
```
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
```

5. 一张图总结

+---------------------+
| 1. EXPLAIN 验证索引 |
+---------------------+|v
+-------------------------------+
| 2. pg_stat_user_indexes 观察  |
|    - 非实时                   |
|    - 非历史累计               |
+-------------------------------+|v
+---------------------+
| 3. 长期监控与优化   |
+---------------------+