PostgreSQL如何查询长时间运行的SQL？详解pg_stat_activity系统视图

在 PostgreSQL 数据库的日常运维和性能调优中，一个常见需求是监控当前正在执行的查询，特别是那些执行时间过长、可能消耗大量资源或阻塞其他操作的“慢查询”。

那么，具体到这个问题：你希望查看当前正在运行的、执行时间超过10秒的查询语句，应该查询哪个系统视图？

我们逐一分析选项：

• A. pg_locks：此视图主要用于展示当前数据库中的锁信息。它可以告诉你哪些事务持有什么锁，哪些事务在等待锁，对于诊断锁等待和死锁问题至关重要。但它并不直接提供查询语句文本或执行时间，因此不符合本题需求。

• B. pg_stat_statements：这是一个极为强大的扩展，用于跟踪服务器执行的所有SQL语句的统计信息（如总执行时间、调用次数、读写行数等）。它主要用于分析历史累计的SQL性能瓶颈，而非实时查看当前正在运行的查询状态。所以，它也不是本题的答案。

• C. pg_stat_activity：这是正确答案。pg_stat_activity 视图是PostgreSQL中用于监控当前数据库活动状态的核心工具。它每一行代表一个服务器进程（如客户端连接），提供了丰富的信息，包括：

  • pid: 进程ID
  • usename: 用户名
  • client_addr: 客户端地址
  • state: 进程状态（如 active, idle）
  • query: 当前正在执行或最近执行的查询语句
  • backend_start: 进程开始时间
  • query_start: 当前查询开始时间
  • state_change: 状态最后一次变化的时间
    通过计算 now() - query_start，你可以轻松筛选出执行时间超过特定阈值（例如10秒）的活跃查询。

• D. pg_prepared_statements：此视图列出了当前会话中所有已准备的语句。预备语句通常用于提高执行效率，但该视图仅限于当前会话，且不提供跨所有会话的全局视图，也无法直接提供查询执行时间，因此不适用于本题场景。

因此，要查看当前正在运行的、执行超过10秒的查询，你应该查询 pg_stat_activity 系统视图。

查询示例：

SELECT pid, usename, client_addr, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' -- 筛选出活跃状态（正在执行查询）的进程
  AND query_start IS NOT NULL -- 确保查询已经开始
  AND now() - query_start > interval '10 seconds' -- 筛选执行时间超过10秒的
ORDER BY duration DESC;

这条SQL会列出所有活跃且已运行超过10秒的查询，并按照执行时长降序排列，帮助你快速定位最耗时的当前操作。

希望这个解析能帮助你更好地理解 PostgreSQL 的系统监控视图。如果你想深入探讨更多数据库运维或性能优化技巧，欢迎在 云栈社区 与其他开发者交流。