PostgreSQL 性能分析与优化使用指南（pg_stat_statements）

背景：写完需求功能，被测试告知，效率太低。

数据库：postgresql

在软件开发中，及时分析和优化SQL语句对于确保系统性能至关重要。

本文将介绍如何使用 PostgreSQL 的 `pg_stat_statements` 模块进行SQL性能分析，并提供了一些建议用于更好地理解和优化SQL查询。

1. 启用 pg_stat_statements 模块：

在 PostgreSQL 的配置文件中启用 pg_stat_statements 模块。配置文件通常位于 PostgreSQL 数据目录下的 postgresql.conf 文件。

打开 postgresql.conf 文件，找到并修改以下配置项：

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

• shared_preload_libraries 指定了要预加载的模块，将 pg_stat_statements 添加到其中。
• pg_stat_statements.max 指定了保存的最大查询数量，你可以根据需要进行调整。
• pg_stat_statements.track 指定了要跟踪的查询类型，all 表示跟踪所有查询。

保存配置文件并重新启动 PostgreSQL 服务。

2. 查询 pg_stat_statements 视图：

连接到 PostgreSQL 数据库，并执行以下 SQL 查询以检索收集到的统计信息：

SELECT * FROM pg_stat_statements;

这将返回一个包含查询统计信息的结果集，包括查询文本、执行计划、执行次数、执行时间等。

pg_stat_statements 视图提供了关于数据库中执行的SQL语句的统计信息。以下是视图中一些重要字段的含义：

1. userid: 执行SQL语句的用户的ID。

2. dbid: 执行SQL语句的数据库的ID。

3. queryid: 用于标识具体查询的ID。

4. query: 实际执行的SQL查询文本。

5. calls: SQL查询被执行的次数。

6. total_time: 执行该查询所花费的总时间（以毫秒为单位）。

7. rows: 查询返回的总行数。

8. shared_blks_hit: 共享缓冲区中的块的数量，这是由该查询的所有实例共享的块数。

9. shared_blks_read: 从磁盘读取的共享缓冲区块的数量。

10. shared_blks_dirtied: 由该查询修改的共享缓冲区中的块数。

11. shared_blks_written: 由该查询写入磁盘的共享缓冲区块的数量。

12. local_blks_hit: 本地缓冲区中的块的数量，这是由该查询的所有实例专用的块数。

13. local_blks_read: 从磁盘读取的本地缓冲区块的数量。

14. local_blks_dirtied: 由该查询修改的本地缓冲区中的块数。

15. local_blks_written: 由该查询写入磁盘的本地缓冲区块的数量。

16. temp_blks_read: 从磁盘读取的临时文件中的块数。

17. temp_blks_written: 写入磁盘的临时文件中的块数。

3. 进一步分析：

你可以使用 pg_stat_statements 视图的结果进一步分析数据库性能。例如，你可以按照执行次数或执行时间对查询进行排序，以找到最频繁或最耗时的查询。

以下是一些示例查询：

-- 按照执行次数降序排列查询 
SELECT * FROM pg_stat_statements ORDER BY calls DESC;

-- 按照总执行时间降序排列查询 
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;

根据实际需求，你可以调整查询条件和排序方式。

4. 清空 pg_stat_statements 统计信息

SELECT pg_stat_statements_reset();

这将清空已收集的查询统计信息，以便重新开始收集新的信息。