pg_track_optimizer

pig build pkg pg_track_optimizer         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

shared_preload_libraries = 'pg_track_optimizer';

CREATE EXTENSION pg_track_optimizer;

-- 生产环境仅追踪有问题的查询
SET pg_track_optimizer.mode = 'normal';

-- 调试时追踪所有查询
SET pg_track_optimizer.mode = 'forced';

-- 当误差超过阈值时记录 EXPLAIN
SET pg_track_optimizer.log_min_error = 2.0;

SELECT queryid, query,
       avg_avg, avg_min, avg_max,
       rms_avg, rms_min, rms_max,
       time_avg, blks_avg, nexecs
FROM pg_track_optimizer
ORDER BY avg_avg DESC
LIMIT 10;

-- 直接使用 RStats 类型
SELECT queryid, query,
       wca_error -> 'mean' AS avg_wca_error,
       blks_accessed -> 'mean' AS avg_blocks
FROM pg_track_optimizer()
WHERE blks_accessed -> 'mean' > 1000
ORDER BY wca_error -> 'mean' DESC;

-- 将统计信息保存到磁盘
SELECT pg_track_optimizer_flush();

-- 清除所有追踪的统计信息
SELECT pg_track_optimizer_reset();

-- 检查扩展状态
SELECT * FROM pg_track_optimizer_status;