PostgreSQL数据库是一款免费开源的数据库，目前行业发展中非常火爆。在数据库日常运维中，性能监控是非常重要的，这里我给大家介绍下“pg_stat_statements”扩展插件的安装和使用。
pg_stat_statements扩展它用于收集关于执行的 SQL 语句的统计信息。这可以帮助你分析查询性能，识别慢查询，并优化数据库。
我这里使用的是windows操作系统，所以下面的相关内容是基于windows操作系统，所以请大家注意！
一：📝 配置和启用扩展
1、安装数据库。具体如何安装这里就不讲解了，请大家自行百度搜索下。
下载地址：https://www.postgresql.org/download
2、检查是否安装了pg_stat_statements相关文件
打开PostgreSQL安装的目录，找到：….\PostgreSQL\17\share\extension，确认有以下：pg_stat_statements….sql文件即可，文件名称后面的是版本号，不影响。
3、连接数据库启用扩展
使用数据库连接工具或命令行，执行以下SQL语句，如果没有提示错误则表示成功。
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;4、配置pg_stat_statements扩展参数
打开PostgreSQL安装的目录，找到：….\PostgreSQL\17\data，找到：postgresql.conf 文件，然后使用记事本打开，进行以下修改。
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
#pg_stat_statements 配置
pg_stat_statements.max = 10000
pg_stat_statements.track = all
针对上面3个配置参数讲解。
#用于加载pg_stat_statements模块，需要重启postgres服务。
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
#是由该模块跟踪的语句的最大数目（即pg_stat_statements视图中行的最大数量）。如果观测到的可区分的语句超过这个数量，最少被执行的语句的信息将会被丢弃。默认值为 5000。这个参数只能在服务器启动时设置。
pg_stat_statements.max = 10000
#控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置。
pg_stat_statements.track = all
5、重启服务生效
以上的步骤配置完成以后，重启PostgreSQL服务，在系统的服务中找到“postgresql-x64-17”服务，然后重启即可。
二：📋测试结果
在数据库运行SQL查看结果
select * from pg_stat_statements如果以上SQL能出现查询结果，则表示配置成功。
关于查询返回的字段说明。
| 字段 | 类型 | 描述 |
| 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、常用的性能分析SQL
#调用次数较多的SQL
select * from pg_stat_statements stat order by calls desc limit 10;
#总执行时间较长的SQL
select * from pg_stat_statements order by total_time desc limit 10;
#平均执行时间较长的SQL
select * from pg_stat_statements order by mean_time desc limit 10;
#在读/写块上总执行时间最多的SQL
select * from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10;
#在读/写块上平均执行时间最多的SQL
select * from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10;
#查看时间抖动严重的SQL
select * from pg_stat_statements order by stddev_time desc limit 10;
#消耗共享内存较多的SQL
select * from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
#使用临时块较多的SQL
select * from pg_stat_statements order by temp_blks_written desc limit 10;
#缓冲池命中率较低的SQL
SELECT *,cast(100.0*shared_blks_hit/nullif(shared_blks_hit + shared_blks_read,0) as decimal(10,2)) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
发布者：IT柚子，转转请注明出处：https://ityouzi.com/archives/postgresql-17-pg_stat_statements.html
微信扫一扫 
支付宝扫一扫 