PostgreSQL：pg_stat_statements
目录
一、安装 pg_stat_statements
1.1 确保已安装
1.2 加载共享库
1.3 重启服务
1.4 创建扩展
二、使用 pg_stat_statements
2.1 pg_stat_statements 视图列信息
2.2 常用查询资源消耗多的SQL
2.3 清除累积的统计数据
2.4 禁用 pg_stat_statements 收集数据
三、注意事项
pg_stat_statements 是 PostgreSQL 的一个扩展，它用于收集关于执行的 SQL 语句的统计信息。这可以帮助你分析查询性能，识别慢查询，并优化数据库。
一、安装 pg_stat_statements
1.1 确保已安装
在大多数情况下，`pg_stat_statements` 扩展是包含在标准的 PostgreSQL 分发中的，可通过以下语句查询插件是否可用。
SELECT * FROM pg_available_extensions;
1.2 加载共享库
修改`postgresql.conf` 文件，确保 `shared_preload_libraries` 参数中包含了 `'pg_stat_statements'`。
shared_preload_libraries = 'pg_stat_statements'
# 配置 pg_stat_statements
# pg_stat_statements.max：定义了跟踪的 SQL 语句的最大数量，默认值通常是 5000。
# pg_stat_statements.track：控制哪些语句被跟踪（选项包括 all, top, none）。
# pg_stat_statements.save：是否保存统计信息到磁盘（默认为 on），以便在服务器重启后仍然保留。
1.3 重启服务
更改此设置后，必须重启 PostgreSQL 服务以使更改生效。
1.4 创建扩展
通过 psql 或者其他可以执行 SQL 命令的客户端工具使用 SQL 命令来创建扩展。
# 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
#查看当前数据库实例中已安装和启用的扩展
SELECT * FROM pg_extension;
二、使用 pg_stat_statements
配置完成并启用了 `pg_stat_statements`，就可以使用它来获取统计信息：主要的视图是 `pg_stat_statements`，它提供了每个记录的查询文本、调用次数、总时间等信息。
2.1 pg_stat_statements 视图列信息
\d pg_stat_statements;
| 字段 | 类型 | 描述 |
|---|---|---|
| userid | oid | 执行该语句的用户 OID |
| dbid | oid | 数据库中执行语句的 OID |
| toplevel | boolean | 如果查询作为顶级语句执行（如果 pg_stat_statements.track 设置为 top ，则始终为真） |
| queryid | bigint | 哈希码用于识别相同的规范化查询。 |
| query | text | 文本示例语句 |
| plans | bigint | 语句被计划次数（如果启用了 pg_stat_statements.track_planning，否则为0） |
| total_plan_time | double precision | 总规划该语句所花费的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0） |
| min_plan_time | double precision | 最小用于规划语句的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0） |
| max_plan_time | double precision | 最大用于语句规划的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0） |
| mean_plan_time | double precision | 平均花费在语句规划上的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0） |
| stddev_plan_time | double precision | 语句规划时间的人口标准差，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0） |
| calls | bigint | 执行语句的次数 |
| total_exec_time | double precision | 执行该语句所花费的总时间，以毫秒为单位 |
| min_exec_time | double precision | 执行语句的最短时间，以毫秒为单位 |
| max_exec_time | double precision | 执行语句所花费的最大时间，以毫秒为单位 |
| mean_exec_time | double precision | 平均执行语句耗时，以毫秒为单位 |
| stddev_exec_time | double precision | 生成执行计划的标准偏差时间，单位为毫秒 |
| rows | bigint | 总行数，由语句检索或影响的行数 |
| shared_blks_hit | bigint | 语句引发的共享块缓存命中总数 |
| shared_blks_read | bigint | 语句读取的总共享块数 |
| shared_blks_dirtied | bigint | 语句导致的共享块脏化的总数 |
| shared_blks_written | bigint | 语句写入的共享块总数 |
| local_blks_hit | bigint | 语句导致的本地块缓存命中总数 |
| local_blks_read | bigint | 语句读取的本地块总数 |
| local_blks_dirtied | bigint | 语句导致的本地脏块总数 |
| local_blks_written | bigint | 该语句写入的本地块总数 |
| temp_blks_read | bigint | 语句读取的临时块总数 |
| temp_blks_written | bigint | 语句写入的临时块总数 |
| blk_read_time | double precision | 该语句读取数据文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0） |
| blk_write_time | double precision | 该语句写入数据文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0） |
| temp_blk_read_time | double precision | 该语句读取临时文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0） |
| temp_blk_write_time | double precision | 该语句写入临时文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0） |
| wal_records | bigint | 该语句生成的 WAL 记录总数 |
| wal_fpi | bigint | 该语句生成的 WAL 完整页面图像总数 |
| wal_bytes | numeric | 该语句生成的 WAL 总字节数 |
| jit_functions | bigint | 该语句总共 JIT 编译的函数数 |
| jit_generation_time | double precision | 该语句生成 JIT 代码所花费的总时间，以毫秒为单位 |
| jit_inlining_count | bigint | 函数内联次数 |
| jit_inlining_time | double precision | 该语句在内联函数上花费的总时间，以毫秒为单位 |
| jit_optimization_count | bigint | 语句被优化的次数 |
| jit_optimization_time | double precision | 该语句在优化上花费的总时间，以毫秒为单位 |
| jit_emission_count | bigint | 代码被发出次数 |
| jit_emission_time | double precision | 该语句在生成代码上花费的总时间，以毫秒为单位 |
2.2 常用查询资源消耗多的SQL
- 调用次数较多的SQL
select userid,dbid,query,calls,rows,total_exec_time,mean_exec_time from pg_stat_statements order by calls desc limit 10;
- 总执行时间较长的SQL
select userid,dbid,query,calls,rows,total_exec_time,mean_exec_time from pg_stat_statements order by total_exec_time desc limit 10;
- 平均执行时间较长的SQL
select userid,dbid,query,calls,rows,total_exec_time,mean_exec_time from pg_stat_statements order by mean_exec_time desc limit 10;
2.3 清除累积的统计数据
SELECT pg_stat_statements_reset();
2.4 禁用 pg_stat_statements 收集数据
如果只是想停止收集新的统计数据，但不想完全移除这个扩展，可以将配置参数 pg_stat_statements.track 设置为 none。这将禁用所有的统计信息收集。
ALTER SYSTEM SET pg_stat_statements.track = 'none';
如果不再需要这个扩展，并且希望彻底从数据库中删除它，可以通过运行下面的 SQL 命令来完成：
DROP EXTENSION IF EXISTS pg_stat_statements;
移除该扩展不会影响已经存储在磁盘上的统计数据；它只会阻止未来的数据收集。如果想清除所有现有的统计数据，可以在移除扩展之前清空相关的视图或表。
三、注意事项
- pg_stat_statements 可能会影响数据库性能，因为它需要跟踪额外的信息。在生产环境中使用时，应该谨慎并监控其影响。
- 统计信息是累积的，除非被重置，否则它们会一直增加。
- 为了更精确的性能分析，可能需要结合使用其他工具和日志，例如 EXPLAIN ANALYZE。