PostgreSQL pg_stat_statements性能监控

pg_stat_statements安装与配置

安装pg_stat_statements扩展

pg_stat_statements是PostgreSQL的一个内置扩展，用于跟踪SQL语句的执行统计信息。安装步骤如下：

修改postgresql.conf配置文件：

txt

-- 启用pg_stat_statements扩展
shared_preload_libraries = 'pg_stat_statements'

-- 设置pg_stat_statements的配置参数
pg_stat_statements.track = all                 -- 跟踪所有SQL语句
pg_stat_statements.max = 10000                -- 最多存储10000条语句
pg_stat_statements.track_utility = on         -- 跟踪工具语句
pg_stat_statements.save = on                 -- 重启后保留统计信息
pg_stat_statements.track_planning = off       -- 不跟踪计划阶段

重启PostgreSQL服务：

bash

# 重启PostgreSQL服务
systemctl restart postgresql

在数据库中创建扩展：

sql

-- 在目标数据库中创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 验证扩展是否创建成功
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

核心配置参数

pg_stat_statements提供了多个配置参数，可以根据实际需求进行调整：

参数名	默认值	说明
pg_stat_statements.max	5000	存储的最大语句数
pg_stat_statements.track	top	跟踪级别：top（仅顶层语句）、all（所有语句）、none（不跟踪）
pg_stat_statements.track_utility	off	是否跟踪工具语句（如CREATE、ALTER等）
pg_stat_statements.save	on	重启后是否保留统计信息
pg_stat_statements.track_planning	off	是否跟踪计划阶段的统计信息
pg_stat_statements.normalize	on	是否标准化SQL语句（替换常量值）

使用pg_stat_statements进行性能监控

基本查询

pg_stat_statements扩展提供了一个同名视图，用于查看SQL语句的执行统计信息：

sql

-- 查看所有SQL语句的执行统计
SELECT * FROM pg_stat_statements;

-- 查看执行次数最多的前10条SQL
SELECT queryid, query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- 查看总执行时间最长的前10条SQL
SELECT queryid, query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 查看平均执行时间最长的前10条SQL
SELECT queryid, query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

关键统计字段说明

pg_stat_statements视图包含多个关键字段，用于分析SQL性能：

字段名	说明
queryid	SQL语句的唯一标识符
query	标准化后的SQL语句
calls	执行次数
total_time	总执行时间（毫秒）
mean_time	平均执行时间（毫秒）
min_time	最小执行时间（毫秒）
max_time	最大执行时间（毫秒）
stddev_time	执行时间标准差（毫秒）
rows	总返回行数
shared_blks_hit	共享缓冲区命中次数
shared_blks_read	共享缓冲区读取次数
shared_blks_dirtied	共享缓冲区修改次数
shared_blks_written	共享缓冲区写入次数
local_blks_hit	本地缓冲区命中次数
local_blks_read	本地缓冲区读取次数
local_blks_dirtied	本地缓冲区修改次数
local_blks_written	本地缓冲区写入次数
temp_blks_read	临时缓冲区读取次数
temp_blks_written	临时缓冲区写入次数
blk_read_time	块读取时间（毫秒）
blk_write_time	块写入时间（毫秒）

高级查询示例

sql

-- 计算每条SQL的平均缓冲区读取次数
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    shared_blks_read / calls AS avg_shared_blks_read,
    shared_blks_hit / calls AS avg_shared_blks_hit
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_time DESC
LIMIT 10;

-- 查找扫描行数远大于返回行数的SQL（可能存在全表扫描）
SELECT 
    queryid,
    query,
    calls,
    rows / calls AS avg_rows,
    shared_blks_read / calls AS avg_blks_read
FROM pg_stat_statements
WHERE rows / calls < 0.1 AND calls > 10
ORDER BY avg_blks_read DESC
LIMIT 10;

-- 查看最近执行的SQL语句
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    now() - pg_stat_statements_reset_timestamp() AS elapsed_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

性能分析与优化

识别慢查询

sql

-- 查找平均执行时间超过100毫秒的SQL
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY total_time DESC;

-- 查找执行时间波动较大的SQL（标准差大）
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    stddev_time,
    stddev_time / mean_time AS cv_time
FROM pg_stat_statements
WHERE calls > 10 AND stddev_time / mean_time > 1
ORDER BY cv_time DESC
LIMIT 10;

分析查询计划

结合EXPLAIN ANALYZE分析慢查询的执行计划：

sql

-- 1. 首先获取慢查询的query文本
SELECT query FROM pg_stat_statements WHERE queryid = '查询ID';

-- 2. 然后使用EXPLAIN ANALYZE分析执行计划
EXPLAIN ANALYZE 
-- 这里替换为实际的慢查询SQL
SELECT * FROM large_table WHERE condition = 'value';

优化示例

假设我们发现以下慢查询：

sql

-- 慢查询示例
SELECT * FROM orders WHERE customer_id = 12345;

通过pg_stat_statements发现该查询执行时间较长，我们可以：

查看表结构和索引：

sql

SELECT * FROM pg_indexes WHERE tablename = 'orders';

如果没有合适的索引，创建索引：

sql

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

重新分析表统计信息：

sql

ANALYZE orders;

验证优化效果：

sql

-- 重置统计信息（谨慎使用，会丢失所有历史统计）
-- SELECT pg_stat_statements_reset();

-- 执行优化后的查询，然后查看统计信息
SELECT * FROM orders WHERE customer_id = 12345;

-- 查看优化后的统计信息
SELECT query, calls, total_time, mean_time FROM pg_stat_statements
WHERE query LIKE '%SELECT * FROM orders WHERE customer_id%';

管理与维护

重置统计信息

sql

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

-- 重置指定数据库的统计信息（PostgreSQL 13+）
SELECT pg_stat_statements_reset('database_name');

监控pg_stat_statements自身性能

pg_stat_statements本身会带来一定的性能开销，需要监控其资源使用情况：

sql

-- 查看pg_stat_statements的内存使用情况
SELECT 
    pg_size_pretty(pg_stat_statements_memory()) AS memory_usage;

-- 查看pg_stat_statements的统计信息行数
SELECT COUNT(*) FROM pg_stat_statements;

定期清理旧统计信息

sql

-- 创建定期清理函数
CREATE OR REPLACE FUNCTION cleanup_old_statements()
RETURNS VOID AS $$
BEGIN
    -- 重置统计信息（根据实际需求调整频率）
    PERFORM pg_stat_statements_reset();
    RAISE NOTICE 'pg_stat_statements统计信息已重置';
END;
$$ LANGUAGE plpgsql;

-- 创建定时任务（需要pg_cron扩展）
-- 每天凌晨2点重置统计信息
SELECT cron.schedule('0 2 * * *', 'SELECT cleanup_old_statements();');

常见问题与解决方案

问题1：pg_stat_statements视图中没有数据

解决方案：

检查shared_preload_libraries是否包含pg_stat_statements
确认PostgreSQL服务已重启
检查pg_stat_statements.track参数是否设置为all或top
执行一些SQL语句后再查询

问题2：统计信息不准确

解决方案：

确保pg_stat_statements.normalize设置为on
定期运行ANALYZE更新表统计信息
避免频繁重置统计信息

问题3：pg_stat_statements占用过多内存

解决方案：

降低pg_stat_statements.max参数值
定期重置统计信息
调整pg_stat_statements.track参数为top

问题4：重启后统计信息丢失

解决方案：

确保pg_stat_statements.save设置为on
检查shared_preload_libraries配置是否正确

最佳实践

生产环境配置建议

合理设置参数：
- 根据数据库规模调整pg_stat_statements.max
- 生产环境建议pg_stat_statements.track = all
- 开启pg_stat_statements.track_utility以跟踪DDL语句
定期分析统计信息：
- 每周或每月生成性能报告
- 重点关注执行次数多、执行时间长的SQL
- 分析查询计划，优化索引
结合其他工具：
- 与pgBadger结合生成可视化报告
- 与Prometheus+Grafana结合实现实时监控
- 与pgAudit结合实现审计功能
注意性能开销：
- pg_stat_statements会带来约5-10%的性能开销
- 对于高并发系统，建议谨慎调整参数
- 定期重置统计信息，避免内存占用过大

常见问题（FAQ）

Q1：pg_stat_statements与auto_explain有什么区别？

A1：pg_stat_statements用于统计SQL语句的执行情况，包括执行次数、时间等；auto_explain用于自动记录慢查询的执行计划。两者结合使用可以更全面地分析性能问题。

Q2：如何查看pg_stat_statements的版本？

A2：

sql

SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_statements';

Q3：可以在只读副本上使用pg_stat_statements吗？

A3：pg_stat_statements可以在只读副本上使用，但需要在主库和副本上都配置shared_preload_libraries = 'pg_stat_statements'，并且副本上的统计信息是独立的。

Q4：如何导出pg_stat_statements的统计信息？

A4：

sql

-- 导出统计信息到文件
COPY (
    SELECT * FROM pg_stat_statements ORDER BY total_time DESC
) TO '/tmp/pg_stat_statements.csv' CSV HEADER;

Q5：pg_stat_statements会记录参数化查询吗？

A5：是的，pg_stat_statements会记录参数化查询，并将参数替换为占位符，便于统计相同查询的执行情况。

Q6：如何过滤特定数据库的统计信息？

A6：pg_stat_statements的统计信息是按数据库隔离的，在不同数据库中查询会显示对应数据库的统计信息。

Q7：pg_stat_statements支持哪些PostgreSQL版本？

A7：pg_stat_statements从PostgreSQL 9.1版本开始支持，不同版本的功能略有差异，建议使用PostgreSQL 12及以上版本以获得完整功能。

PostgreSQL pg_stat_statements性能监控 ​

pg_stat_statements安装与配置 ​

安装pg_stat_statements扩展 ​

核心配置参数 ​

使用pg_stat_statements进行性能监控 ​

基本查询 ​

关键统计字段说明 ​

高级查询示例 ​

性能分析与优化 ​

识别慢查询 ​

分析查询计划 ​

优化示例 ​

管理与维护 ​

重置统计信息 ​

监控pg_stat_statements自身性能 ​

定期清理旧统计信息 ​

常见问题与解决方案 ​

问题1：pg_stat_statements视图中没有数据 ​

问题2：统计信息不准确 ​

问题3：pg_stat_statements占用过多内存 ​

问题4：重启后统计信息丢失 ​

最佳实践 ​

生产环境配置建议 ​

常见问题（FAQ） ​

Q1：pg_stat_statements与auto_explain有什么区别？ ​

Q2：如何查看pg_stat_statements的版本？ ​

Q3：可以在只读副本上使用pg_stat_statements吗？ ​

Q4：如何导出pg_stat_statements的统计信息？ ​

Q5：pg_stat_statements会记录参数化查询吗？ ​

Q6：如何过滤特定数据库的统计信息？ ​

Q7：pg_stat_statements支持哪些PostgreSQL版本？ ​

PostgreSQL pg_stat_statements性能监控

pg_stat_statements安装与配置

安装pg_stat_statements扩展

核心配置参数

使用pg_stat_statements进行性能监控

基本查询

关键统计字段说明

高级查询示例

性能分析与优化

识别慢查询

分析查询计划

优化示例

管理与维护

重置统计信息

监控pg_stat_statements自身性能

定期清理旧统计信息

常见问题与解决方案

问题1：pg_stat_statements视图中没有数据

问题2：统计信息不准确

问题3：pg_stat_statements占用过多内存

问题4：重启后统计信息丢失

最佳实践

生产环境配置建议

常见问题（FAQ）

Q1：pg_stat_statements与auto_explain有什么区别？

Q2：如何查看pg_stat_statements的版本？

Q3：可以在只读副本上使用pg_stat_statements吗？

Q4：如何导出pg_stat_statements的统计信息？

Q5：pg_stat_statements会记录参数化查询吗？

Q6：如何过滤特定数据库的统计信息？

Q7：pg_stat_statements支持哪些PostgreSQL版本？