PostgreSQL中如何查询使用了索引的具体SQL语句？

方法1：结合pg_stat_statements与索引统计信息 #

pg_stat_statements本身不会直接关联索引，但可以通过解析查询的执行计划来匹配。步骤如下：

• 先确认pg_stat_statements扩展已启用，未启用的话执行：

  CREATE EXTENSION pg_stat_statements;
  

• 从pg_stat_user_indexes获取目标索引的基本信息（比如索引名、关联表）：

  SELECT indexrelid, relname AS table_name, indexrelname AS index_name
  FROM pg_stat_user_indexes
  WHERE idx_scan > 0; -- 筛选有扫描记录的索引
  

• 接着查询pg_stat_statements，通过执行计划文本提取用到的索引：

  SELECT
    queryid,
    query,
    calls,
    total_time,
    unnest(regexp_matches(pg_stat_get_plan(queryid)::text, 'Index Scan using (\w+) on', 'g')) AS used_index
  FROM pg_stat_statements
  WHERE pg_stat_get_plan(queryid)::text LIKE '%Index Scan using%'
  ORDER BY total_time DESC;
  

  你可以在WHERE子句中添加AND used_index = '你的索引名'来精准过滤目标索引的使用记录。

方法2：实时捕获使用索引的查询 #

如果想查看当前正在运行的、用到索引的查询，可通过pg_stat_activity结合执行计划查询：

SELECT
  pid,
  query,
  unnest(regexp_matches(pg_get_plan(pid)::text, 'Index Scan using (\w+) on', 'g')) AS used_index
FROM pg_stat_activity
WHERE state = 'active'
  AND pg_get_plan(pid)::text LIKE '%Index Scan using%';

注意：pg_get_plan()需要PostgreSQL 12及以上版本，且需具备查看其他会话查询的权限。

方法3：通过日志记录查询与执行计划 #

修改PostgreSQL配置，记录包含执行计划的查询日志：

• 编辑postgresql.conf：

  log_statement = 'all' -- 可根据需求改为'mod'仅记录修改类语句
  log_min_duration_statement = 0 -- 记录所有语句的执行计划
  log_plan = on
  log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d '
  

• 重启PostgreSQL后，日志中会包含每条查询的执行计划，直接从中筛选包含目标索引的记录即可。

注意事项 #

• 正则匹配执行计划的方式可能因PostgreSQL版本不同出现格式适配问题，需根据实际执行计划文本调整正则表达式。
• pg_stat_statements的历史记录会受pg_stat_statements.max配置限制，超出数量后旧记录会被覆盖，如需长期留存建议定期导出数据。
• 部分操作（如查看其他会话执行计划、修改配置文件）需要超级用户权限。

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