PostgreSQL 9.6中未使用索引的判定与元数据有效性检查

先还原下你的场景：你运行了这条SQL来排查public schema里的未使用索引：

select t.tablename AS "relation", indexname, c.reltuples AS num_rows, pg_relation_size(quote_ident(t.tablename)::text) AS table_size, pg_relation_size(quote_ident(indexrelname)::text) AS index_size, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename=c.relname LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, psai.idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename WHERE t.schemaname='public' and idx_scan = 0 ORDER BY --1,2 --6 5 desc ;

结果出来一堆number_of_scans、tuples_read、tuples_fetched全为0的索引，接下来我一步步给你解答疑问：

一、这些索引能直接删除吗？ #

绝对不能直接删！ 先踩刹车，得先排除几种关键情况：

• 主键/唯一约束索引：你的查询里虽然拉了indisunique字段，但没过滤掉这类索引。主键和唯一约束依赖的索引是用来保证数据完整性的——哪怕从来没被查询用到，删了会直接破坏数据约束，导致重复数据插入，绝对碰不得。
• 低频但必要的索引：有些索引只在每周报表、月度对账这类周期性任务里用，而PostgreSQL的统计计数器是从实例重启后开始累积的，如果你的数据库刚重启过，或者统计周期没覆盖到这些任务的执行时间，就会显示0扫描。
• 写操作的代价：索引会拖慢INSERT/UPDATE/DELETE的速度，要是确定真没用，删了确实能提升写性能，但前提是100%确认它没有任何业务场景依赖。

二、会不会是元数据过期导致统计不准？ #

当然有可能！PostgreSQL的pg_stat_all_indexes统计数据有几个“坑”：

• 实例重启清零：每次PostgreSQL重启，所有统计计数器都会被重置为0。如果你的数据库最近刚重启过，那这个查询结果完全没参考价值，得等业务跑过一个完整周期再看。
• 新创建的索引：如果索引是刚建的，自然还没被扫描过，显示0是正常的，得观察一段时间。
• 统计数据未持久化：PostgreSQL 9.6没有像新版本那样的持久化统计功能，统计数据只存在于内存中，重启就没了，所以如果你的实例运行时间短，统计数据也不完整。

三、怎么检查确认这些索引是否真的没用？ #

给你一套实操步骤，按顺序来：

1. 先筛选出非约束类索引 #

先修改你的查询，把主键、唯一约束的索引标出来，直接排除：

select 
  t.tablename AS "relation", 
  indexname,
  case when indisprimary then '主键索引' when indisunique then '唯一约束索引' else '普通索引' end as index_type,
  c.reltuples AS num_rows, 
  pg_relation_size(quote_ident(t.tablename)::text) AS table_size, 
  pg_relation_size(quote_ident(indexrelname)::text) AS index_size, 
  idx_scan AS number_of_scans, 
  idx_tup_read AS tuples_read, 
  idx_tup_fetch AS tuples_fetched 
FROM pg_tables t 
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname 
LEFT OUTER JOIN ( 
  SELECT 
    c.relname AS ctablename, 
    ipg.relname AS indexname, 
    x.indnatts AS number_of_columns, 
    psai.idx_scan, 
    idx_tup_read, 
    idx_tup_fetch, 
    indexrelname, 
    indisunique,
    indisprimary
  FROM pg_index x 
  JOIN pg_class c ON c.oid = x.indrelid 
  JOIN pg_class ipg ON ipg.oid = x.indexrelid 
  JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid 
) AS foo ON t.tablename = foo.ctablename 
WHERE t.schemaname='public' and idx_scan = 0 
ORDER BY index_size desc;

结果里标为“主键索引”“唯一约束索引”的直接跳过，不用考虑删除。

2. 查看数据库重启时间 #

先确认统计数据的起始点：

SELECT pg_postmaster_start_time();

如果这个时间离现在很近，比如才几天，那先等一周（覆盖你的业务周期）再重新查询，看看这些索引的扫描次数有没有变化。

3. 核对索引定义，排查特殊场景 #

有些索引看起来没用，但实际上是为特定查询设计的，比如表达式索引、部分索引，先查索引定义：

SELECT indexdef FROM pg_indexes WHERE schemaname='public' AND indexname='你的索引名';

比如看到CREATE INDEX idx_users_lower_email ON users (lower(email));，那这个索引是给WHERE lower(email) = 'xxx'这类查询用的，得确认业务里有没有这类查询，不能光看扫描次数就删。

4. 临时禁用索引测试（谨慎操作） #

对于拿不准的普通索引，可以先禁用它，观察业务情况：

ALTER INDEX 你的索引名 DISABLE;

接下来观察几天：有没有业务报错？有没有慢查询突然增多？如果一切正常，那这个索引就可以安全删除；如果出现问题，立刻启用：

ALTER INDEX 你的索引名 ENABLE;

⚠️ 注意：绝对不能禁用主键或唯一约束索引！会导致约束失效，数据完整性被破坏。

5. 检查慢查询日志 #

如果你的数据库开启了慢查询日志，可以搜索对应表和索引字段的查询，看看有没有查询计划用到过这个索引——有些低频查询可能没被统计到，但确实存在。

内容的提问来源于stack exchange，提问作者user2671057