pg_stat_user_indexes 索引使用统计

索引是数据库里最贵的「常备库存」：每个索引都在拖慢写入、占用磁盘与缓存，换取特定查询路径的加速。一个数据库跑上几年后，几乎必然积累出一批「当年为某个报表建的、后来没人用了」的僵尸索引。pg_stat_user_indexes 就是给每个索引发的「使用打卡表」——它记录每个索引自统计周期开始以来被扫描了多少次、扫过多少条目、真正取回多少行，是索引瘦身决策的第一手依据。

与它配套的还有两个视图：pg_statio_user_indexes 提供索引层面的块 I/O 命中统计；pg_stat_user_tables 提供表层面的顺序扫描、增删改、HOT 更新等统计。三者合起来回答三个问题：索引有没有被用？索引在 I/O 上划不划算？表的写入路径健不健康？

需要提前说明：这些统计都是自实例启动（或统计重置）以来的累计值，并且是按数据库分别记录的。判断「未使用索引」必须在一个足够长的业务周期上观察，绝不能看一天的数字就下结论删索引。

前置知识

建议先阅读：pg_stat_statements SQL 统计

基础概念

PostgreSQL 的统计收集器会为每个「用户表/索引」维护一组计数器。这里的「用户」是相对于系统目录而言：pg_stat_user_indexes 只列用户表上的索引；系统表自身的索引在 pg_stat_sys_indexes，两者字段完全相同。类似的还有不带过滤的 pg_stat_all_indexes / pg_statio_all_indexes。

视图	观察对象	回答的问题
`pg_stat_user_indexes`	用户表上的每个索引	这个索引被用过多少次？扫过多少条目？
`pg_statio_user_indexes`	同上	这个索引的块读写在缓存与磁盘间怎么分布？
`pg_stat_user_tables`	每张用户表	表的顺序扫描、行变更、HOT 更新、清理与分析进行到什么程度？

统计计数由后端进程通过消息发给统计收集器（较新版本已改为共享内存 + 定期落盘，语义不变），存在秒级的汇总延迟，且在实例崩溃时可能丢失一小段。这些都是可接受的误差，不影响趋势判断。

三个索引计数的关系

pg_stat_user_indexes 的三个计数容易混淆，先建立直觉：

text

idx_scan     ：这个索引被「扫描」了多少次（每次计划用到它算一次）
idx_tup_read ：索引扫描过程中「读过的索引条目」总数
idx_tup_fetch：通过简单索引扫描「真正从堆表取回的存活行」数

计数	什么时候增长	反映什么
`idx_scan`	执行计划选择了这个索引，每启动一次索引扫描 +1	索引「是否被用」的唯一判据
`idx_tup_read`	索引扫描读过多少索引条目（含已被删除/尚未清理的死条目）	索引内部的扫描工作量
`idx_tup_fetch`	简单索引扫描从堆里实际取回的存活行数	真正「服务于查询结果」的行数

三个数字的典型解读：

idx_scan = 0：统计周期内从未被使用——未使用索引候选。
idx_scan 高但 idx_tup_fetch 低：大量扫描只取回很少的行，索引选择性可能有问题，或者扫描结果大量被过滤。
idx_tup_read 远大于 idx_tup_fetch：索引里死元组占比高（清理不足），或查询读了大量条目后过滤掉了。

语法与用法

sql

-- 1. 当前库的索引使用概况
SELECT schemaname,
       relname,
       indexrelname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 2. 索引块命中率
SELECT schemaname,
       relname,
       indexrelname,
       idx_blks_read,
       idx_blks_hit,
       round(100.0 * idx_blks_hit /
             nullif(idx_blks_hit + idx_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC
LIMIT 20;

-- 3. 表级扫描与写入概况
SELECT schemaname,
       relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       n_tup_ins,
       n_tup_upd,
       n_tup_del,
       n_tup_hot_upd,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

配合表结构信息，还能把「索引名」还原成「列与类型」，方便判断它的业务价值：

sql

SELECT i.indexrelid::regclass AS index_name,
       pg_get_indexdef(i.indexrelid) AS index_def,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;

详细说明

pg_stat_user_indexes 字段详解

字段	类型	说明
`relid`	oid	索引所属表的 OID
`indexrelid`	oid	索引自身的 OID
`schemaname`	name	模式名
`relname`	name	表名
`indexrelname`	name	索引名
`idx_scan`	bigint	索引扫描次数（自统计周期起）
`idx_tup_read`	bigint	扫描读过的索引条目数
`idx_tup_fetch`	bigint	简单索引扫描实际取回的堆表存活行数

注意 idx_tup_fetch 的限定词「简单索引扫描」：索引仅扫描（index-only scan）不从堆取行，不增长这个计数；位图扫描（bitmap scan）的取行也按内核的口径与简单扫描不同。所以「read 多、fetch 少」不一定是索引低效——要结合 EXPLAIN 看扫描方式。

pg_statio_user_indexes 与索引块命中率

字段	类型	说明
`idx_blks_read`	bigint	从存储读入的索引块数（shared_buffers 未命中）
`idx_blks_hit`	bigint	在 shared_buffers 中命中的索引块数

sql

-- 命中率低于 90% 的大索引值得警惕
SELECT relname,
       indexrelname,
       idx_blks_read,
       idx_blks_hit,
       round(100.0 * idx_blks_hit /
             nullif(idx_blks_hit + idx_blks_read, 0), 1) AS hit_pct,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_statio_user_indexes
WHERE idx_blks_hit + idx_blks_read > 0
  AND 100.0 * idx_blks_hit /
      nullif(idx_blks_hit + idx_blks_read, 0) < 90
ORDER BY idx_blks_read DESC;

解读要点：

命中率低 = 这个索引频繁穿透缓存，占用了磁盘带宽。
命中率低不等于该删——热写入表的小索引本来就会周期性穿透；先看它服务的查询是否关键。
索引块命中率整体低迷时，更可能的解法是加大 shared_buffers 或收窄索引，而不是删索引。

识别未使用索引

标准做法是「三重验证」：

sql

-- 未使用索引报告：一次扫描零使用 + 排除约束支撑索引
SELECT s.schemaname,
       s.relname,
       s.indexrelname,
       s.idx_scan,
       s.idx_tup_read,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       pg_get_indexdef(s.indexrelid) AS index_def,
       con.conname IS NOT NULL AS supports_constraint
FROM pg_stat_user_indexes s
LEFT JOIN pg_constraint con
       ON con.conindid = s.indexrelid
WHERE s.idx_scan = 0
  AND con.conname IS NULL            -- 排除主键/唯一约束支撑的索引
  AND s.indexrelid NOT IN (          -- 排除表达式索引中被函数依赖的部分（人工复核）
      SELECT indexrelid FROM pg_index WHERE indisprimary OR indisunique
  )
ORDER BY pg_relation_size(s.indexrelid) DESC;

三重验证的三个条件：

idx_scan = 0 且观察周期覆盖完整业务周期。 月末报表、季度结算索引，必须观察一个完整业务节拍，最好以「上次统计重置以来的时间」为准（配合 pg_stat_database.stats_reset 或 pg_postmaster_start_time()）。
排除约束支撑的索引。 主键与唯一索引即使从不被查询扫描，也承担着约束校验职责，删不得。
排除语义依赖。 ON CONFLICT（需要唯一索引）、外键引用、分区键索引、以及应用里的 SET enable_seqscan 之类计划干预，都可能让索引「低频但致命」。删除前用 grep 应用代码与迁移脚本复核索引名。

删索引的正确姿势

确认要删时，用 DROP INDEX CONCURRENTLY 避免拿全表排他锁阻塞业务；先在低峰删一个观察影响，而不是一口气清空名单。所有删除动作进入变更流程，保留 pg_get_indexdef() 的完整定义以便随时重建。

识别低效索引与高成本表扫描

pg_stat_user_tables 的 seq_scan / seq_tup_read 与索引统计对照，能发现「该建索引而没建」的表：

sql

-- 顺序扫描多、且每次扫描读很多行的表 —— 缺索引的典型画像
SELECT relname,
       seq_scan,
       seq_tup_read,
       round(seq_tup_read::numeric / nullif(seq_scan, 0)) AS rows_per_seqscan,
       idx_scan,
       n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND seq_tup_read / nullif(seq_scan, 0) > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;

rows_per_seqscan 巨大说明每次全表扫描都在读几十万行——如果这类扫描来自高频业务 SQL，就是明确的建索引信号。定位具体语句则回到 pg_stat_statements 找 seq scan 特征的查询。

pg_stat_user_tables 字段详解

字段	类型	说明
`relid` / `schemaname` / `relname`	oid / name / name	表标识
`seq_scan`	bigint	顺序扫描次数
`seq_tup_read`	bigint	顺序扫描读过的存活行数
`idx_scan`	bigint	索引扫描次数（该表所有索引 idx_scan 之和的口径）
`idx_tup_fetch`	bigint	索引扫描取回的存活行数
`n_tup_ins` / `n_tup_upd` / `n_tup_del`	bigint	累计插入/更新/删除的行数
`n_tup_hot_upd`	bigint	其中走 HOT（Heap-Only Tuple）路径的更新行数
`n_live_tup` / `n_dead_tup`	bigint	估计的存活行数 / 死元组数
`n_mod_since_analyze`	bigint	上次 ANALYZE 以来被修改的行数（较新版本引入）
`last_vacuum` / `last_autovacuum`	timestamptz	最近一次手动/自动 VACUUM（不含 VACUUM FULL）
`last_analyze` / `last_autoanalyze`	timestamptz	最近一次手动/自动 ANALYZE
`vacuum_count` / `autovacuum_count` / `analyze_count` / `autoanalyze_count`	bigint	对应操作的累计次数

n_live_tup 与 n_dead_tup 是估算值（来自统计信息，不是精确计数），用于判断表膨胀的粗略公式：

sql

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup /
             nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC
LIMIT 20;

死元组比例长期偏高的表，要么写入热点集中（需要更激进的 autovacuum 参数），要么存在阻碍清理的长事务。膨胀的精确测量与处理见《表膨胀检测与处理》一页。

HOT 更新：写入健康度的核心指标

HOT（Heap-Only Tuple）更新是 PostgreSQL 的关键优化：如果更新不涉及索引列、且新元组能放进同一个页面，则更新不需要为新值插入任何索引条目。n_tup_hot_upd / n_tup_upd 的比值就是 HOT 比例：

sql

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_live_tup
FROM pg_stat_user_tables
WHERE n_tup_upd > 10000
ORDER BY hot_pct ASC
LIMIT 20;

HOT 比例低的影响链条：每次更新都要维护全部索引 → 索引膨胀加速 → 索引扫描读更多页 → I/O 恶化。改善手段：把频繁更新的列与索引列解耦（别为低选择性列建索引）、加大 fillfactor 留出页内空间、控制死元组及时清理。这组参数的配置细节见《AutoVacuum 配置》一页。

较新版本（PG16+）还把「因页面放不下而跨页的非 HOT 更新」单独拆成了计数（n_tup_newpage_upd），可用于更精细地诊断页空间不足。

统计口径与常见陷阱

按数据库隔离：这些视图只显示当前数据库的对象与计数。跨库巡检要逐库执行。
统计周期：计数自实例启动或 pg_stat_reset() 起累计。看到 idx_scan = 0 先查「统计了多久」，别把刚重启的库误判成零使用。
重置会连坐：pg_stat_reset() 清空当前库全部统计计数；没有按表/按索引的重置（pg_stat_statements 有，表级统计没有）。做索引观察实验时，重置动作要纳入计划。
崩溃丢一小段：统计在共享内存中定期落盘，崩溃后恢复到最近一次落盘点。对「未使用索引」这类长周期判断无影响。
估算值不是精确值：n_live_tup / n_dead_tup 来自 ANALYZE 与内部采样，配合 pgstattuple 扩展才能精确测量。

与维护决策的联动

这套统计最终服务于三类决策：

决策	主要依据	配套动作
删除未使用索引	`idx_scan = 0` + 完整周期 + 约束排除	`DROP INDEX CONCURRENTLY`
重建膨胀索引	`idx_tup_read` 与行数比例失衡、命中率低	`REINDEX CONCURRENTLY`
建缺失索引	`seq_scan` 高、每次读大量行	用 EXPLAIN 验证候选索引收益

索引维护的整体方法论（含膨胀检测的精确工具）见《索引使用监控》与《索引膨胀检测》两页。

user / sys / all 三套视图家族

索引（与表）统计视图每个都有三个变体，按「观察对象的范围」划分：

后缀	范围	典型用途
`_user_`	仅用户表/索引	日常巡检（最常用）
`_sys_`	仅系统目录的表/索引	内核调优、诊断目录膨胀
`_all_`	全部（用户 + 系统）	全面审计、跨类目统计

对应的六个索引视图：pg_stat_user_indexes / pg_stat_sys_indexes / pg_stat_all_indexes，以及 pg_statio_ 前缀的三个块 I/O 版本。它们字段完全一致，只是对 pg_class 的过滤条件不同。表级统计同理（pg_stat_user_tables / pg_stat_sys_tables / pg_stat_all_tables）。巡检脚本统一用 _user_ 即可；排查「系统目录本身膨胀」这类深度问题时再切到 _sys_ / _all_。

分区表与继承树的统计聚合

分区表的每一层都独立计数：父表一行、每个分区一行、每个分区上的索引各自一行。直接查 pg_stat_user_indexes 会看到「索引名相同但分属不同分区」的一串行。做报表需要按「逻辑索引」聚合时，沿 pg_inherits 递归汇总：

sql

-- 按顶层父表聚合分区表的索引扫描
WITH RECURSIVE part AS (
    SELECT c.oid AS parent_oid, c.relname AS parent_name, i.inhrelid
    FROM pg_class c
    JOIN pg_inherits i ON i.inhparent = c.oid
    WHERE c.relkind IN ('p', 'r')            -- 分区父表
    UNION ALL
    SELECT p.parent_oid, p.parent_name, i.inhrelid
    FROM pg_inherits i
    JOIN part p ON i.inhparent = p.inhrelid
)
SELECT p.parent_name,
       s.indexrelname,
       sum(s.idx_scan)     AS idx_scan_total,
       sum(s.idx_tup_read) AS idx_tup_read_total,
       count(*)            AS partitions
FROM part p
JOIN pg_stat_user_indexes s ON s.relid = p.inhrelid
GROUP BY p.parent_name, s.indexrelname
ORDER BY idx_scan_total DESC
LIMIT 20;

解读分区统计的两个要点：

父表自身的统计行通常接近零——扫描都发生在分区上；idx_scan = 0 的判断要落在「聚合后」而不是单个分区上。
分区数量很多时，原始视图的「每分区一行」恰好可以用来发现数据倾斜：某分区 idx_scan 独高，说明查询的时间范围集中在那个分区。

idx_scan 的口径边界

把 idx_scan 用作决策依据前，明确它「计什么、不计什么」：

计：执行计划真正启动一次索引扫描（含 index-only scan）。规划阶段不计数，被放弃的计划不计数。
idx_tup_fetch 的边界：index-only scan 不回堆取行，idx_tup_read 增长而 idx_tup_fetch 不动——这是「read 高 fetch 低」的正常解释之一，别急着判索引低效。
按数据库隔离：同一集簇里不同数据库的统计互不相通；业务连到哪个库，统计就记在哪个库。
跟随对象生命周期：REINDEX 重建索引不丢统计（OID 不变）；DROP 后重建是新对象、从零累计。pg_stat_reset() 清空的是整个库的统计，没有索引级重置。

因此「未使用索引」的判定窗口要同时满足：完整业务节拍 + 覆盖所有库 + 排除约束支撑——上一节的三重验证流程就是这三条的具体化。

fillfactor、B-tree 去重与索引维护成本

索引的写入成本由几个内核特性共同决定，理解它们才能读懂统计数字背后的「为什么」：

fillfactor：索引页的填充因子决定更新时能否原地放入新条目。热更新表的索引保留页内空闲空间可以减少页分裂，CREATE INDEX ... WITH (fillfactor = 70) 设置。它与表级 fillfactor 一起影响 HOT 更新的成功率。
B-tree 去重（deduplication，PG13+）：PG13 起 B-tree 默认对重复键做页内去重，重复值多的索引（外键、状态列）膨胀显著减轻。评估这类索引的体积问题时，把这个特性考虑进去。
死元组的连带成本：索引里的死条目要等 VACUUM 才能回收；idx_tup_read 远大于 idx_tup_fetch 的索引往往伴随清理不足。表膨胀的治理见《表膨胀检测与处理》。

sql

-- 评估某索引的「读放大」：每取回一行要读多少索引条目
SELECT indexrelname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch,
       round(idx_tup_read::numeric / nullif(idx_tup_fetch, 0), 2) AS read_per_fetch
FROM pg_stat_user_indexes
WHERE idx_tup_fetch > 10000
ORDER BY read_per_fetch DESC
LIMIT 10;

比值极端偏大的索引（比如上百倍）值得单独检查：要么大量死条目（该 VACUUM 了），要么扫描方式特殊（位图扫描口径不同，用 EXPLAIN 确认）。

观察窗口与 stats_reset 的实操

所有索引统计都从「上次重置」起算，报告里必须写清窗口：

sql

-- 三个时间锚点：实例启动、统计重置、当前
SELECT pg_postmaster_start_time()           AS started_at,
       s.stats_reset                        AS stats_reset,
       now() - COALESCE(s.stats_reset, pg_postmaster_start_time()) AS window_len
FROM pg_stat_database s
WHERE datname = current_database();

text

        started_at         |         stats_reset         |    window_len
---------------------------+-----------------------------+-------------------
 2026-08-01 03:12:44+08    | 2026-08-15 09:00:01+08      | 52 days 14:22:11
(1 row)

实操规则：window_len 不足一个完整业务周期时，「未使用索引」类结论标注为待观察；stats_reset 出现在报告里，防止有人手动重置过统计却不知情。删除决策的窗口标准见《索引使用监控》。

把窗口信息与统计结果拼成一行，报告就不需要人工解释口径：

sql

SELECT s.indexrelname,
       s.idx_scan,
       now() - COALESCE(d.stats_reset, pg_postmaster_start_time()) AS window_len
FROM pg_stat_user_indexes s
CROSS JOIN (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()) d
WHERE s.indexrelname = 'orders_status_created_idx';

实战示例

示例一：月度索引瘦身报告

sql

-- 完整周期观察后的未使用大索引清单
WITH stats_age AS (
    SELECT now() - pg_postmaster_start_time() AS uptime
)
SELECT s.schemaname,
       s.relname,
       s.indexrelname,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan,
       s.idx_tup_read,
       pg_get_indexdef(s.indexrelid) AS index_def,
       round(extract(epoch FROM now() - pg_postmaster_start_time()) / 86400) AS observed_days
FROM pg_stat_user_indexes s
WHERE s.idx_scan = 0
  AND pg_relation_size(s.indexrelid) > 10 * 1024 * 1024   -- 只看 10MB 以上的
  AND NOT EXISTS (                                        -- 排除约束支撑索引
      SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid
  )
ORDER BY pg_relation_size(s.indexrelid) DESC;

text

 schemaname | relname |   indexrelname   |  size  | idx_scan | idx_tup_read |              index_def              | observed_days
------------+---------+------------------+--------+----------+--------------+-------------------------------------+---------------
 public     | logs    | logs_old_status_idx | 428 MB |        0 |            0 | CREATE INDEX ... ON logs (status)   |            45
 public     | events  | events_tag_gin      | 212 MB |        0 |            0 | CREATE INDEX ... ON events USING gin|            45
(2 rows)

处置：logs_old_status_idx 是旧代码遗留，确认代码不再引用后 DROP INDEX CONCURRENTLY；events_tag_gin 属于报表模块，先与报表团队确认业务节拍是否超过观察窗口。

示例二：HOT 比例与 fillfactor 优化验证

sql

-- 优化前基线
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'session_tokens';

text

    relname     | n_tup_upd | n_tup_hot_upd | hot_pct
----------------+-----------+---------------+---------
 session_tokens |    984510 |        122310 |    12.4
(1 row)

sql

-- 处置：调整 fillfactor 给 HOT 腾出页内空间，并确认索引列不含高频更新列
ALTER TABLE session_tokens SET (fillfactor = 80);
VACUUM FULL session_tokens;  -- 重建物理布局（低峰执行）
SELECT pg_stat_reset();      -- 重置统计观察新周期（低峰执行）

观察一个完整业务周期后再次读取，hot_pct 显著上升即证明有效。

示例三：顺序扫描大户 → 建索引闭环

sql

-- 1. 找到扫描大户
SELECT relname, seq_scan, seq_tup_read, n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 5;

text

   relname    | seq_scan | seq_tup_read | n_live_tup
--------------+----------+--------------+------------
 orders       |    18234 |    452189002 |     842000

sql

-- 2. 回到 pg_stat_statements 找到对应的高频查询，确认过滤条件
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements
WHERE query ILIKE '%FROM orders%'
ORDER BY calls DESC
LIMIT 5;

-- 3. 用 EXPLAIN 验证候选索引收益后建索引
CREATE INDEX CONCURRENTLY orders_customer_created_idx
    ON orders (customer_id, created_at DESC);

-- 4. 重置统计后观察 seq_scan 是否回落（配合业务方确认）

示例四：索引块命中率总览

sql

SELECT round(100.0 * sum(idx_blks_hit) /
             nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0), 2) AS index_hit_ratio,
       sum(idx_blks_read) AS total_idx_reads
FROM pg_statio_user_indexes;

text

 index_hit_ratio | total_idx_reads
-----------------+-----------------
           96.34 |       18402213
(1 row)

整体命中率健康（>95%），继续下钻到单索引找出拖后腿的个体即可。

示例五：分区表索引聚合报告

月度巡检的分区表索引报告，按父表聚合后一目了然：

sql

WITH RECURSIVE part AS (
    SELECT c.oid AS root_oid, c.relname AS root_name, i.inhrelid
    FROM pg_class c JOIN pg_inherits i ON i.inhparent = c.oid
    WHERE c.relname = 'events'
    UNION ALL
    SELECT p.root_oid, p.root_name, i.inhrelid
    FROM pg_inherits i JOIN part p ON i.inhparent = p.inhrelid
)
SELECT s.indexrelname,
       count(*) AS partitions,
       sum(s.idx_scan) AS idx_scan_total,
       sum(s.idx_tup_read) AS read_total,
       pg_size_pretty(sum(pg_relation_size(s.indexrelid))) AS total_size
FROM part p
JOIN pg_stat_user_indexes s ON s.relid = p.inhrelid
GROUP BY s.indexrelname
ORDER BY idx_scan_total DESC;

text

    indexrelname     | partitions | idx_scan_total | read_total | total_size
---------------------+------------+----------------+------------+------------
 events_time_idx     |         36 |        2940213 |  182044812 | 12 GB
 events_device_idx   |         36 |         104812 |   10481200 | 8.2 GB
 events_payload_gin  |         36 |              0 |          0 | 22 GB
(3 rows)

events_payload_gin 聚合后仍是 0，还占 36 个分区共 22GB——比单表场景更明确的瘦身候选（同样要走三重验证）。这份报告还暴露了另一个事实：events_time_idx 承接了绝大多数扫描，是分区裁剪的主力索引。

示例六：新索引上线验证工作流

给 orders(status, created_at) 建了新索引，两周后验证它「值不值」：

sql

-- 1) 确认观察窗口足够长
SELECT now() - stats_reset AS observed
FROM pg_stat_database WHERE datname = current_database();

-- 2) 新索引的累计成绩
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname = 'orders_status_created_idx';

-- 3) 代价侧：写入是否变慢（对照 pg_stat_statements 里 orders 的写语句）
SELECT calls, round(mean_exec_time::numeric, 3) AS mean_ms,
       round(wal_bytes::numeric / calls / 1024, 1) AS wal_kb_per_call,
       left(query, 60) AS query_head
FROM pg_stat_statements
WHERE query LIKE 'INSERT INTO orders%' OR query LIKE 'UPDATE orders%'
ORDER BY calls DESC LIMIT 5;

判定：idx_scan 稳定增长、读块收益明显，且写路径的耗时与 WAL 增量在可接受范围——索引保留；若 idx_scan 仍为零或写入代价超标，进入删除评估流程（三重验证 + DROP INDEX CONCURRENTLY，见《REINDEX 重建索引》所属的索引维护专题）。每个新索引都走一遍这个闭环，「僵尸索引」才不会越积越多。

注意事项

idx_scan = 0 不等于「可以删」。 约束校验、ON CONFLICT、外键、分区路由、计划锁定等场景都不依赖 idx_scan 计数。任何删除都要过「约束排除 + 代码检索 + 业务确认」三道关。

观察周期必须覆盖业务节拍。 统计是自启动/重置以来的累计值，重启当天看 idx_scan 毫无意义；月末、季度报表索引要看完整节拍。用 pg_postmaster_start_time() 或 pg_stat_database.stats_reset 把观察时长写进报告。

统计按数据库隔离。 跨库部署同一套表结构时，A 库零使用不代表 B 库零使用；瘦身报告要逐库生成。

idx_tup_fetch 对 index-only scan 与 bitmap scan 的口径不同。 拿它与 idx_tup_read 直接相除下「效率」结论之前，先用 EXPLAIN 确认扫描方式，避免误杀高效索引。

n_dead_tup 是估算值。 它随 ANALYZE 与内部采样更新，可能有滞后。做删除决策前用 pgstattuple 精确复核。

pg_stat_reset() 会清空全部表级统计。 没有按索引的细粒度重置；在生产库做重置实验要评估「丢失基线」的代价，并在变更记录里注明重置时刻。

VACUUM FULL 与 CLUSTER 会重排物理布局但不重置统计。 统计跟随对象（OID）持续累计，重建索引（REINDEX）不影响 idx_scan 连续性；但 DROP 后重建的索引统计从零开始——这是「重建索引后 idx_scan 归零」的常见误会来源。

autovacuum worker 也在消费这些视图。 清理与分析的时间戳（last_autovacuum 等）是诊断 autovacuum 是否正常工作的直接证据；长期没有 last_autovacuum 记录的热表要检查 autovacuum 配置与全局触发条件。

最佳实践

把索引使用统计纳入每月例行巡检。 输出三张表：零使用索引、HOT 比例最低的写入热表、顺序扫描大户。固定节奏比临时想起来更有效。
未使用索引的判定窗口至少一个月，且覆盖业务节拍。 宁可多等一个周期，也不要为了省几百 MB 空间删掉月末报表要用的索引。
删索引用 DROP INDEX CONCURRENTLY，一次只删一个。 低峰执行、单点验证、随时可回滚（保留 pg_get_indexdef() 输出）。激进批删是生产事故的经典来源。
区分「使用统计」与「I/O 统计」两个视角。 idx_scan 判断有没有用，pg_statio_user_indexes 判断用得划不划算；两个视角都要看再下结论。
为写入热表维护 HOT 比例指标。 高更新表（会话、库存、计数器）的 HOT 比例跌破 50% 就值得分析索引列构成与 fillfactor；索引维护成本会随时间复利放大。
建索引前先看 seq_scan / seq_tup_read 与 pg_stat_statements。 让每个新索引都有「服务哪条查询」的书面答案，避免再制造下一批僵尸索引。
跟踪 n_mod_since_analyze 防止统计信息过期。 大批量变更后若该数字巨大而 ANALYZE 时间戳很旧，主动 ANALYZE 该表，防止计划劣化。
监控 n_dead_tup 与 last_autovacuum 的组合信号。 死元组持续增长且 autovacuum 时间戳停滞的表是膨胀预警，及时按《表膨胀检测与处理》处理。
大版本升级后重跑一轮观察期。 升级前后统计计数会清零，把升级日标记为统计周期起点，重新积累基线再下判断。
给 DBA 之外的角色只读权限查看这些视图。 开发人员看到「自己建的索引从未被使用」往往比任何评审都有说服力；统计视图是最客观的架构反馈。
把索引增删记录与统计快照存档。 每次巡检导出 pg_stat_user_indexes 快照（含时间戳）到历史表，才能回答「这个索引上个月被用过吗」这类审计问题。
精确测量用 pgstattuple，趋势判断用统计视图。 统计视图便宜、实时、适合天天看；pgstattuple 有锁与 I/O 成本，适合做最终确认。

继续学习：pg_stat_database 与 pg_stat_user_tables

pg_stat_user_indexes 索引使用统计 ​

基础概念 ​

三个索引计数的关系 ​

语法与用法 ​

详细说明 ​

pg_stat_user_indexes 字段详解 ​

pg_statio_user_indexes 与索引块命中率 ​

识别未使用索引 ​

识别低效索引与高成本表扫描 ​

pg_stat_user_tables 字段详解 ​

HOT 更新：写入健康度的核心指标 ​

统计口径与常见陷阱 ​

与维护决策的联动 ​

user / sys / all 三套视图家族 ​

分区表与继承树的统计聚合 ​

idx_scan 的口径边界 ​

fillfactor、B-tree 去重与索引维护成本 ​

观察窗口与 stats_reset 的实操 ​

实战示例 ​

示例一：月度索引瘦身报告 ​

示例二：HOT 比例与 fillfactor 优化验证 ​

示例三：顺序扫描大户 → 建索引闭环 ​

示例四：索引块命中率总览 ​

pg_stat_user_indexes 索引使用统计

基础概念

三个索引计数的关系

语法与用法

详细说明

pg_stat_user_indexes 字段详解

pg_statio_user_indexes 与索引块命中率

识别未使用索引

识别低效索引与高成本表扫描

pg_stat_user_tables 字段详解

HOT 更新：写入健康度的核心指标

统计口径与常见陷阱

与维护决策的联动

user / sys / all 三套视图家族

分区表与继承树的统计聚合

idx_scan 的口径边界

fillfactor、B-tree 去重与索引维护成本

观察窗口与 stats_reset 的实操

实战示例

示例一：月度索引瘦身报告

示例二：HOT 比例与 fillfactor 优化验证

示例三：顺序扫描大户 → 建索引闭环

示例四：索引块命中率总览