利用pg_stat_statments分析业务瓶颈

1、查看系统负载

如果希望减少整个系统的负载，可以按照总时间来查看您的语句：

select
  (total_exec_time + total_plan_time)::int as total_time,
  total_exec_time::int,
  total_plan_time::int,
  mean_exec_time::int,
  calls,
  query
from
  pg_stat_statements
order by
  total_time desc
limit 50;

返回所有调用中花费时间最多的50个查询。这意味着频繁执行的快查询可能排在不经常执行的慢查询前面。这可能是查询使用最多系统资源的一个很好的方式。

如果您使用的是 Postgres 版本 12（或更早版本），您将无法访问planning time，并且您还需要分别用 total_time 和 mean_time 替换 total_exec_time 和 mean_exec_time。

如果您使用的是 Postgres 版本 13（或更高版本）并注意到您的 total_plan_time 列全为零，您可能需要查看pg_stat_statements.track_planning（默认情况下处于关闭状态）。

2、查看热门查询

如果您的客户抱怨性能太烂，而您的主要目标是加快最慢的查询，您可以同时查看您的热门查询，例如：

select
  (mean_exec_time + mean_plan_time)::int as mean_time,
  mean_exec_time::int,
  mean_plan_time::int,
  calls,
  query
from
  pg_stat_statements
--where
--  userid = 99999
--  and calls > 1
order by
  mean_time desc
limit 50;

这与上面的例子非常相似，关于版本 13 之前和之后的警告是一样的！

在注释掉的 where 子句中，您可以看到用于减少结果干扰的选项。对 userid 进行过滤可以帮助从用户那里移除那些无关紧要的慢速查询。类似地，如果您让人们执行您希望排除的一次性慢速查询，则限制查询执行次数超过最小次数会很方便。

3、减少IO

考虑系统资源使用的另一种方法是考虑缓冲区。缓冲区统计信息对查询优化非常有用，可以通过他们查询整体工作负载。

将所有缓冲区统计信息加在一起，以提供一个非常粗略的“完成工作”的代理：

select
  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 as total_buffers,
  (total_exec_time + total_plan_time)::int as total_time,
  calls,
  shared_blks_hit as sbh,
  shared_blks_read as sbr,
  shared_blks_dirtied as sbd, 
  shared_blks_written as sbw,
  local_blks_hit as lbh,
  local_blks_read as lbr,
  local_blks_dirtied as lbd,
  local_blks_written as lbw,
  temp_blks_read as tbr,
  temp_blks_written as tbr,
  query
from
  pg_stat_statements
order by
  total_buffers desc
limit 50;

您可能希望将这些缓冲区统计信息中的一些与上面的查询混合搭配，例如查看您按总时间排名的每个查询的 total_buffers。

在这种情况下，我更喜欢查看带有块号的列，但是如果您更喜欢以字节为单位查看它们中的任何一个，您可能会喜欢函数pg_size_pretty() — 如果这样做，请记住乘以您的块大小（默认为 8192）。

4、调整JIT设置

从 Postgres 15 开始，pg_stat_statements 中有了JIT编译统计（和 I/O 计时，但那些可以等到另一天）。我看到很多人遇到过早启动 JIT 编译的问题，这对他们的查询和/或工作负载造成了净损害。下面是一个示例查询，我们可以使用它来查看 JIT 编译时间最长的查询（占时间的百分比）：

select
  ((jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time)/(total_exec_time + total_plan_time)) as jit_total_time_percent,
  calls,
  jit_functions,
  jit_generation_time,
  jit_inlining_count,
  jit_inlining_time,
  jit_optimization_count,
  jit_optimization_time,
  jit_emission_count,
  jit_emission_time,
  query
from
  pg_stat_statements
order by
  jit_total_time_percent desc
limit 50;

即时编译花费总时间的百分比进行排序。

5、其他常用SQL

最耗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，单次调用最耗时 SQL TOP 5：

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

总最耗时 SQL TOP 5：

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;

响应时间抖动最严重 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;

原文

https://www.pgmustard.com/blog/queries-for-pg-stat-statements

https://blog.rustprooflabs.com/2023/05/pg-stat-statements-performance-differences