postgresql-pg_stat_statements

最新推荐文章于 2025-05-31 23:46:47 发布

原创最新推荐文章于 2025-05-31 23:46:47 发布 · 424 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql

postgresql教程专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何通过修改postgresql.conf配置文件来启用pg_stat_statements扩展，以追踪和优化SQL语句执行情况。包括设置最大跟踪语句数、选择追踪级别，并提供SQL查询以找出执行最慢的SQL语句。

修改postgresql.conf文件，并重启postgreSQL

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000                   //跟踪的最大语句数量，缺省是1000条
pg_stat_statements.track = all                  //控制那些语句会被追踪，可选top（缺省），all和none

创建pg_stat_statements

create extension pg_stat_statements;     //创建pg_stat_statements extension
CREATE EXTENSION
\d pg_stat_statements;                   //查看视图结构
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers
---------------------+------------------+-----------
 userid              | oid              |
 dbid                | oid              |
 query               | text             |
 calls               | bigint           |
 total_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 |
 blk_write_time      | double precision |

查找执行最慢的语句（查最慢10条SQL语句）：

select query,calls,total_time,(total_time/calls) as average, rows,100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) as hit_percent 
from pg_stat_statements 
order by average desc limit 10;

为使输出精简，也可执行以下语句（最慢2条语句):

select query,(total_time/calls) as average, 100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) as hit_percent 
from pg_stat_statements 
order by average desc limit 2;