PG中关于 pg_stat_statements

郑全

pg_stat_statements 是 PostgreSQL 中的一个扩展模块，用于跟踪 SQL 查询的执行情况和性能统计数据。通过启用和使用 pg_stat_statements，你可以详细了解哪些查询消耗了最多的资源（如时间、I/O 等），进而帮助你进行数据库性能优化。

功能概述

pg_stat_statements 可以记录以下信息：

• SQL 语句的文本（查询被标准化，例如将常量值替换为占位符）。
• 查询执行的次数（计数）。
• 查询的总执行时间和平均执行时间。
• 查询返回的行数。
• 执行中出现的错误数。
• 共享缓冲区命中、读写次数等 I/O 相关信息。
  

如何启用 pg_stat_statements

• 
  
  
• 修改 postgresql.conf 配置文件：
  首先，在 postgresql.conf 文件中启用扩展：
  
  shared_preload_libraries = 'pg_stat_statements'
  
  pg_stat_statements 需要在 PostgreSQL 启动时加载，所以必须将其加入 shared_preload_libraries。
  
• 重启 PostgreSQL：
  修改完配置文件后，需要重启 PostgreSQL：
  
  sudo systemctl restart postgresql
  
  
• 创建扩展：
  登录到 PostgreSQL 并创建 pg_stat_statements 扩展：
  
  CREATE EXTENSION pg_stat_statements;
  
  
  

查询统计数据启用后，可以通过查询系统视图 pg_stat_statements 来查看统计信息：

SELECT    query, -- 查询的文本（标准化）    calls, -- 查询被执行的次数    total_time, -- 查询的总执行时间（毫秒）    min_time, -- 查询的最短执行时间（毫秒）    max_time, -- 查询的最长执行时间（毫秒）    mean_time, -- 查询的平均执行时间（毫秒）    rows, -- 查询返回的行数    shared_blks_hit, -- 查询在共享缓冲区的命中次数    shared_blks_read -- 查询从磁盘读取的块数FROM    pg_stat_statementsORDER BY    total_time DESCLIMIT 10;

这个查询将返回执行时间最长的前 10 个 SQL 查询。
重要字段解释

• query: 查询的文本。常量被替换为占位符，因此相同的查询结构会被聚合在一起。
• calls: 查询执行的次数。
• total_time: 查询的总执行时间（所有调用的累计时间）。
• mean_time: 查询的平均执行时间。
• shared_blks_hit: 查询在共享缓冲区中命中的块数，表示从内存中读取的次数。
• shared_blks_read: 查询从磁盘读取的块数，表示 I/O 的负担。
  

清除统计数据你可以在需要时重置（清除）统计信息：

SELECT pg_stat_statements_reset();

这将清除 pg_stat_statements 中的所有统计数据，从而开始新的统计周期。
性能影响pg_stat_statements 由于需要跟踪大量的查询信息，确实会带来一些性能开销，但通常非常轻微。大多数生产环境中都可以安全启用。如果你有大量复杂查询并且需要对性能问题进行深入分析，启用 pg_stat_statements 是一个非常有用的做法。
实际应用场景

• 发现性能瓶颈：通过分析哪些查询占用了最多的资源，你可以识别出最需要优化的 SQL 语句。
• 监控和调优：你可以定期查看哪些查询执行得频繁或效率低下，并调整索引或重写查询。
• 识别长时间运行的查询：对于需要监控慢查询的场景，pg_stat_statements 提供了平均执行时间、最大执行时间等信息，帮助你快速定位问题。
  

总结pg_stat_statements 是 PostgreSQL 中非常强大且实用的扩展，用于收集和分析查询的统计信息。通过定期分析这些数据，你可以识别性能瓶颈、优化查询、提升数据库的整体性能。