PostgreSQL中是否可以查询索引的最后使用日期？

核心结论 #

PostgreSQL 原生的系统统计视图没有内置存储索引最后使用时间的字段，但可以通过以下两种方式实现该指标的采集：

实现方案 #

• 方案1：定期快照统计（最常用、成本最低）
  你可以定时执行你现有的索引查询语句，记录每次采集的时间和对应索引的idx_scan数值：
  • 若两次采集之间idx_scan数值上涨，说明该索引在这段时间被使用过，对应采集时间即可作为最后使用时间的参考
  • 若idx_scan数值为0，说明从上次统计重置/实例启动后，该索引从未被使用
    你可以在原有查询中加入统计重置时间参考字段，方便你判断计数的时间范围：

SELECT
  current_database()                             AS datname,
  t.schemaname,
  t.tablename,
  psai.indexrelname                              AS index_name,
  pg_relation_size(i.indexrelid)                 AS index_size,
  CASE WHEN i.indisunique THEN 1 ELSE 0 END      AS "unique",
  psai.idx_scan                                  AS number_of_scans,
  psai.idx_tup_read                              AS tuples_read,
  psai.idx_tup_fetch                             AS tuples_fetched,
  pg_stat_get_stats_reset_time()                 AS stat_reset_time, -- 统计数据上次重置时间
  pg_postmaster_start_time()                     AS instance_start_time -- 实例启动时间
FROM
  pg_tables t
  LEFT JOIN pg_class c ON t.tablename = c.relname
  LEFT JOIN pg_index i ON c.oid = i.indrelid
  LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
  t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

• 方案2：基于SQL日志/pg_stat_statements关联
  你可以开启log_statement参数记录所有执行的SQL，或者启用pg_stat_statements扩展收集全量SQL执行记录，通过解析SQL语句的执行计划关联用到的索引，记录对应的执行时间作为索引的最后使用时间。该方案准确度更高，但需要额外的日志存储、SQL解析开发成本，适合对指标精度要求高的场景。

注意事项 #

• 系统统计视图的计数会在执行pg_stat_reset()、实例重启后清零，你做快照统计时需要注意规避这些场景的影响
• 仅索引扫描会更新idx_scan计数，索引被用于约束检查（比如唯一约束校验）时不会触发计数更新，这类场景下的索引使用不会被统计到

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