SQL优化之PostgreSQL的pg_stat_statements统计模块

 pg_stat_statements模块含义？

• pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的资源开销，分析TOP SQL。
• 如下所示表中所代表的字段：f放到

  • -- SQL统计
    SELECT 
    userid AS 执行者id ,
    dbid AS 执行数据库id ,
    query AS 执行的语句 ,
    calls AS 执行次数 ,
    total_time AS 执行总时间,
    total_time/calls AS 执行平均时间,
    rows AS 影响的总行数,
    min_time,
    max_time,
    mean_time AS 平均时间
    FROM pg_stat_statements
    

     

•  最耗时的5条数据，最后一列表示命中率，由此我们可以拿出效率慢的sql进行优化。

  • SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

• 各个字段代表的含义信息？
  • 

 

• 最耗时 SQL，单次调用最耗时 SQL TOP 5
  select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;  

• 最耗IO SQL，单次调用最耗IO SQL TOP 5
  select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;  

  

• 总最耗IO SQL TOP 5
  select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;  

  

• 用户也可以定期清理历史的统计信息，通过调用如下SQL
  select pg_stat_statements_reset();  

• 响应时间抖动最严重 SQL
  select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;  

• 最耗共享内存 SQL
  select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;  

• 最耗临时空间 SQL
  select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;  
  
  

• 重置统计信息
  pg_stat_statements是累积的统计，如果要查看某个时间段的统计，需要打快照

 

• 如何解决了您的问题，欢迎关注我！还希望来JAVA WEB开发交流群：958923746，有问题欢迎共享，共同提升！