PostgreSQL中如何实现类似Oracle的SQL查询性能监控功能？

Oracle的dbms_sqltune.report_sql_monitor主要用于获取SQL的实时执行状态、资源消耗及详细执行统计，PostgreSQL中可以通过以下几种方式实现类似效果：

1. 实时监控运行中的查询 #

通过系统视图pg_stat_activity可以直接查看当前所有查询的运行状态、耗时、等待事件等信息，针对特定SQL或进程PID进行监控：

SELECT
  pid,
  now() - query_start AS duration,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT LIKE '%pg_stat_activity%'; -- 排除监控自身的查询

如果需要定位特定SQL，可添加AND query LIKE '%你的SQL特征片段%'过滤条件。

对于支持进度跟踪的操作（如CREATE INDEX、VACUUM、COPY），还可以使用对应的进度视图：

-- 监控CREATE INDEX的执行进度
SELECT * FROM pg_stat_progress_create_index;

-- 监控VACUUM的执行进度
SELECT * FROM pg_stat_progress_vacuum;

2. 生成已完成查询的详细执行报告 #

PostgreSQL没有内置的一键生成文本报告的函数，但可以通过auto_explain扩展自动记录慢查询的实际执行计划及统计数据，效果类似Oracle的SQL Monitor报告：

步骤1：启用auto_explain #

修改postgresql.conf配置文件（需重启数据库生效）：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '100ms' -- 记录执行时间超过100ms的查询
auto_explain.log_analyze = on -- 记录实际执行统计（如实际行数、耗时）
auto_explain.log_buffers = on -- 记录缓冲区使用情况
auto_explain.log_format = 'text' -- 输出文本格式报告
auto_explain.log_statements = 'all' -- 记录所有符合条件的语句

步骤2：查看报告 #

重启数据库后，符合条件的查询执行细节会被写入PostgreSQL日志文件，内容包含执行计划树、实际耗时、资源消耗等核心信息。

3. 统计历史查询的性能数据 #

使用pg_stat_statements扩展可以统计所有历史查询的调用次数、总耗时、平均耗时等聚合性能数据，便于分析长期的SQL性能：

步骤1：启用pg_stat_statements #

修改postgresql.conf并重启数据库：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'all'

步骤2：创建扩展并查询 #

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查询特定SQL的性能统计
SELECT
  queryid,
  query,
  calls,
  total_time / 1000 AS total_seconds,
  mean_time / 1000 AS avg_seconds,
  max_time / 1000 AS max_seconds,
  rows
FROM pg_stat_statements
WHERE query LIKE '%你的SQL特征片段%'
ORDER BY total_time DESC;

4. 交互式实时监控工具 #

• 使用psql的\watch命令定时刷新监控结果，比如每2秒刷新一次运行中的查询：

  SELECT pid, duration, state, query FROM pg_stat_activity WHERE state = 'active';
  \watch 2
  

• 使用pg_top工具（类似系统top）实时查看PostgreSQL进程的资源占用情况。

内容的提问来源于stack exchange，提问作者ananda