数据库资源分为多个维度、CPU、内存、IO 等，为能够从各个维度层面查找最消耗数据库资源的 SQL，您可以使用 pg_stat_statements 插件统计数据库的资源开销和分析 Top SQL。

本文将通过示例介绍如何创建 pg_stat_statements 插件、如何分析 Top SQL 以及如何重置统计信息。

执行如下命令，在需要查询 TOP SQL 的数据库中，创建 pg_stat_statements 插件。

通过查询 pg_stat_statements 视图，您可以得到数据库资源开销的统计信息。SQL 语句中的一些过滤条件在 pg_stat_statements 中会被替换成变量，可以减少重复显示的问题。

pg_stat_statements 视图包含了一些重要信息，例如：

  • SQL 的调用次数，总耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描、返回或处理了多少行。

  • local buffer 的使用情况：命中、未命中、产生脏块、驱逐脏块。

  • temp buffer 的使用情况：读了多少脏块、驱逐脏块。

  • 数据块的读写时间。

下表列出了 pg_stat_statements 输出内容中各参数的含义。

最耗 IO SQL

执行如下命令，查询单次调用最耗 IO SQL TOP 5。

执行如下命令，查询总最耗 IO SQL TOP 5。

    执行如下命令，查询总最耗时 SQL TOP 5。

    响应时间抖动最严重 SQL

    执行如下命令，查询响应时间抖动最严重 SQL。

      1. select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;

    执行如下命令，查询最耗共享内存 SQL。

    最耗临时空间 SQL

    执行如下命令，查询最耗临时空间 SQL。

    pg_stat_statements 是累积的统计，如果要查看某个时间段的统计，需要打快照，建议您参见文档《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》。

    您也可以通过执行如下命令，来定期清理历史统计信息。

      1. select pg_stat_statements_reset();