PostgreSQL慢查询分析利器：auto_explain与pg_stat_statements深度对比及联合使用
PostgreSQL慢查询分析利器：auto_explain与pg_stat_statements深度对比及联合使用
1. 初识 auto_explain：实时捕获慢查询执行计划
1.1 auto_explain 的安装与配置
1.2 auto_explain 的使用示例
1.3 auto_explain 的优点与局限性
2. pg_stat_statements：统计SQL语句的执行情况
2.1 pg_stat_statements 的安装与配置
2.2 pg_stat_statements 的使用示例
2.3 pg_stat_statements 的优点与局限性
3. auto_explain 与 pg_stat_statements 的联合使用
4. 总结与建议
PostgreSQL慢查询分析利器：auto_explain与pg_stat_statements深度对比及联合使用
作为一名PostgreSQL DBA或者开发者，你肯定遇到过这样的场景：数据库突然变慢，应用响应时间增加，用户开始抱怨。这时，找出罪魁祸首——慢查询（Slow Query）就成了当务之急。PostgreSQL提供了多种工具来帮助我们分析慢查询，其中auto_explain和pg_stat_statements是两个非常强大的扩展。
但是，这两个扩展有什么区别？它们各自的优势是什么？又该如何结合使用它们来更全面地监控和优化PostgreSQL数据库性能呢？今天，咱们就来深入聊聊这个话题，让你对这两个工具了如指掌，从此告别慢查询的困扰。
1. 初识 auto_explain：实时捕获慢查询执行计划
auto_explain，顾名思义，就是自动解释（explain）的意思。它可以自动记录慢查询的执行计划，并将这些信息输出到PostgreSQL的日志文件中。这对于我们分析慢查询的原因非常有帮助，因为执行计划展示了数据库是如何执行这条SQL语句的，包括使用了哪些索引、扫描了哪些表、连接的顺序等等。
1.1 auto_explain 的安装与配置
auto_explain是PostgreSQL的一个contrib扩展，通常情况下已经随PostgreSQL一起安装。如果没有，可以使用以下命令安装：
CREATE EXTENSION auto_explain;
安装完成后，需要在postgresql.conf文件中进行配置。几个关键的参数：
shared_preload_libraries = 'auto_explain'：在PostgreSQL启动时加载auto_explain扩展。auto_explain.log_min_duration = '3s'：设置记录执行计划的最小执行时间阈值，这里设置为3秒，表示执行时间超过3秒的SQL语句才会被记录执行计划。你可以根据实际情况调整这个值。auto_explain.log_analyze = on：启用EXPLAIN ANALYZE，这会提供更详细的执行计划信息，包括实际的执行时间、扫描的行数等。auto_explain.log_buffers = on：记录缓冲区的使用情况，这对于分析I/O性能问题很有帮助。auto_explain.log_timing = on：记录每个节点的执行时间。auto_explain.log_nested_statements = on：记录嵌套语句（例如函数或存储过程中的SQL语句）的执行计划。
配置完成后，重启PostgreSQL服务使配置生效。
1.2 auto_explain 的使用示例
假设我们有一条SQL语句执行时间超过了3秒，auto_explain会在PostgreSQL的日志文件中输出类似下面的信息：
LOG: duration: 3500.123 ms plan: Query Text: SELECT * FROM my_table WHERE column1 = 'value' AND column2 > 100; -> Index Scan using my_table_idx on my_table (cost=0.43..8.45 rows=1 width=100) (actual time=0.123..3450.000 rows=10 loops=1) Index Cond: (column1 = 'value'::text) AND (column2 > 100) Buffers: shared hit=100 read=200 Planning Time: 0.123 ms Execution Time: 3500.000 ms
从日志中，我们可以看到：
duration：SQL语句的执行时间。Query Text：SQL语句的文本。plan：执行计划，包括使用的索引、扫描的行数、缓冲区的使用情况等。Planning Time：查询计划的生成时间。Execution Time：查询的执行时间。
通过分析这些信息，我们可以找出慢查询的原因，例如是否缺少索引、索引是否有效、是否扫描了过多的行等。
1.3 auto_explain 的优点与局限性
优点：
- 实时性：
auto_explain可以实时捕获慢查询的执行计划，无需手动执行EXPLAIN命令。 - 详细信息： 通过配置
log_analyze、log_buffers、log_timing等参数，可以获取非常详细的执行计划信息。 - 易于使用： 配置简单，只需修改几个参数即可。
局限性：
- 性能开销：
auto_explain会增加数据库的负载，尤其是在高并发的情况下。因此，不建议在生产环境中长时间开启auto_explain.log_analyze。 - 日志量大：
auto_explain会产生大量的日志信息，需要定期清理。 - 只能记录慢查询：
auto_explain只能记录执行时间超过阈值的SQL语句，无法记录所有SQL语句的执行情况。 - 无法聚合统计： 对于同一条SQL, 参数不同, 无法进行聚合分析。
2. pg_stat_statements：统计SQL语句的执行情况
pg_stat_statements是另一个非常有用的PostgreSQL扩展，它可以跟踪所有SQL语句的执行情况，包括执行次数、总执行时间、平均执行时间、最长执行时间、最短执行时间、I/O时间等。
与auto_explain不同，pg_stat_statements不会记录执行计划，而是提供了一种聚合的统计视图。这对于我们了解数据库的整体负载情况、找出执行频率最高或总执行时间最长的SQL语句非常有帮助。
2.1 pg_stat_statements 的安装与配置
pg_stat_statements也是PostgreSQL的一个contrib扩展，通常情况下已经随PostgreSQL一起安装。如果没有，可以使用以下命令安装：
CREATE EXTENSION pg_stat_statements;
安装完成后，需要在postgresql.conf文件中进行配置。几个关键的参数：
shared_preload_libraries = 'pg_stat_statements'：在PostgreSQL启动时加载pg_stat_statements扩展。pg_stat_statements.max = 10000：设置pg_stat_statements跟踪的SQL语句的最大数量，默认为1000，可以根据实际情况调整。建议根据数据库中SQL的数量调整, 避免因跟踪的SQL语句超过最大值, 导致信息丢失。pg_stat_statements.track = all：设置跟踪哪些SQL语句，all表示跟踪所有SQL语句，top表示只跟踪顶层SQL语句（不包括函数或存储过程中的SQL语句），none表示不跟踪任何SQL语句。建议设置为all。pg_stat_statements.track_utility = off： 是否跟踪实用命令, 例如:create table. 建议关闭。pg_stat_statements.save = on: 是否在数据库重启后保留统计信息。
配置完成后，重启PostgreSQL服务使配置生效。
2.2 pg_stat_statements 的使用示例
安装并配置好pg_stat_statements后，我们可以通过查询pg_stat_statements视图来获取SQL语句的执行情况。
SELECT * FROM pg_stat_statements;
pg_stat_statements视图包含很多列，这里列出一些常用的列：
queryid：SQL语句的唯一标识符。query：SQL语句的文本（经过规范化处理，例如将常量值替换为占位符）。calls：SQL语句的执行次数。total_time：SQL语句的总执行时间（毫秒）。mean_time：SQL语句的平均执行时间（毫秒）。max_time：SQL语句的最长执行时间（毫秒）。min_time：SQL语句的最短执行时间（毫秒）。rows：SQL语句返回的总行数。shared_blks_hit：共享缓冲区命中的次数。shared_blks_read：从磁盘读取的共享缓冲区的次数。shared_blks_dirtied：被SQL语句弄脏的共享缓冲区的次数。shared_blks_written：写入磁盘的共享缓冲区的次数。local_blks_hit、local_blks_read、local_blks_dirtied、local_blks_written：本地缓冲区的使用情况（仅当SQL语句使用了临时表时才会有值）。temp_blks_read、temp_blks_written：临时文件的读写次数。blk_read_time：块读取时间（毫秒）。blk_write_time：块写入时间（毫秒）。
通过这些统计信息，我们可以找出执行频率最高或总执行时间最长的SQL语句，然后针对性地进行优化。
例如，我们可以使用以下查询找出执行时间最长的10条SQL语句：
SELECT query, calls, total_time, mean_time, max_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
2.3 pg_stat_statements 的优点与局限性
优点：
- 全面统计：
pg_stat_statements可以跟踪所有SQL语句的执行情况，提供全面的统计信息。 - 聚合视图：
pg_stat_statements提供了一种聚合的统计视图，方便我们了解数据库的整体负载情况。 - 低开销：
pg_stat_statements的性能开销相对较低，可以在生产环境中长期开启。
局限性：
- 不记录执行计划：
pg_stat_statements不记录SQL语句的执行计划，无法直接用于分析慢查询的原因。 - 规范化处理：
pg_stat_statements会对SQL语句进行规范化处理，将常量值替换为占位符，这可能会导致一些信息的丢失。 - 需要手动查询： 需要手动执行SQL语句, 才能查看统计信息。
3. auto_explain 与 pg_stat_statements 的联合使用
auto_explain和pg_stat_statements各有优缺点，将它们结合使用可以发挥各自的优势，更全面地监控和优化PostgreSQL数据库性能。
一种常见的联合使用方式是：
- 使用
pg_stat_statements进行日常监控： 长期开启pg_stat_statements，定期查询pg_stat_statements视图，找出执行频率最高或总执行时间最长的SQL语句。 - 使用
auto_explain进行针对性分析： 当发现慢查询时，可以根据pg_stat_statements提供的queryid，在postgresql.conf临时开启auto_explain, 并设置auto_explain.log_min_duration为一个较小的值（例如0ms），然后重现慢查询，auto_explain会记录下详细的执行计划，帮助我们分析慢查询的原因。 - 关闭
auto_explain: 完成分析后, 关闭auto_explain, 避免产生过多的日志。
更进一步，我们可以编写脚本或使用监控工具来实现自动化：
- 定期查询
pg_stat_statements视图，将统计信息存储到数据库中。 - 设置阈值，当某个SQL语句的执行次数、总执行时间或平均执行时间超过阈值时，触发报警。
- 报警触发后，自动开启
auto_explain，并设置auto_explain.log_min_duration为一个较小的值。 - 等待一段时间（例如几分钟），让
auto_explain捕获到慢查询的执行计划。 - 自动关闭
auto_explain，并将日志信息发送给DBA或开发者。
通过这种方式，我们可以实现对慢查询的自动监控、自动分析和自动报警，大大提高数据库的运维效率。
4. 总结与建议
auto_explain和pg_stat_statements是PostgreSQL慢查询分析的两个强大工具，它们各有优缺点，联合使用可以发挥各自的优势，更全面地监控和优化数据库性能。
总结一下：
auto_explain：实时捕获慢查询的执行计划，适用于针对性分析。pg_stat_statements：统计所有SQL语句的执行情况，适用于日常监控和找出Top N慢查询。
建议：
- 在生产环境中长期开启
pg_stat_statements。 - 在需要分析慢查询时，临时开启
auto_explain。 - 结合使用
auto_explain和pg_stat_statements，实现自动化慢查询监控和分析。 - 定期清理
auto_explain产生的日志信息。 - 根据业务情况, 合理设置
pg_stat_statements.max。
希望这篇文章能帮助你更好地理解和使用auto_explain和pg_stat_statements，从此告别慢查询的困扰！如果你还有其他问题，欢迎留言讨论。
最后, 祝你的PostgreSQL数据库永远快如闪电！