pg_stat_statements

CREATE EXTENSION pg_stat_statements;

-- 按总执行时间排序的热点查询
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 按共享缓冲区读取排序的 I/O 密集型查询
SELECT query, calls, shared_blks_read, shared_blks_hit,
       shared_blk_read_time
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

-- 扩展状态（释放次数、上次重置时间）
SELECT * FROM pg_stat_statements_info;

-- 重置所有统计信息
SELECT pg_stat_statements_reset();

-- 重置特定查询的统计
SELECT pg_stat_statements_reset(0, 0, queryid)
FROM pg_stat_statements
WHERE query LIKE '%my_table%';

-- 仅重置最小/最大值
SELECT pg_stat_statements_reset(0, 0, 0, true);

-- 不带文本查询（更少 I/O）
SELECT * FROM pg_stat_statements(showtext := false);