调试慢速 PostgreSQL 查询的系统指南

当你不再把慢速 PostgreSQL 查询当作谜团时，它们就更容易修复。数据库通常可以向你展示它选择的路径、它预期的行数、它实际触及的行数、它是从缓存还是磁盘读取，以及它是否在等待另一个会话。

我最常见的错误是从“这个端点很慢”直接跳到“创建一个索引”。有时这有效。有时索引被忽略，因为谓词的编写方式无法使用它。有时查询本身没问题，但被一个已经打开二十分钟的事务阻塞。系统性的检查可以节省时间，因为它将查询形状、规划器估计、I/O、内存和锁定分离开来。

理解查询性能瓶颈

在深入工具之前，必须认识到 PostgreSQL 查询性能不佳的常见原因。这些问题通常属于几个关键类别：

缺失或低效的索引： 当索引本可以提供快速访问时，数据库被迫对大表进行顺序扫描。
次优的查询结构： 复杂的连接、不必要的子查询或函数使用不当可能会混淆规划器。
过时的统计信息： PostgreSQL 依赖统计信息来构建高效的执行计划。如果统计信息过时，规划器可能会选择低效的路径。
资源争用： 高 I/O 等待时间、过多的锁定或分配给 PostgreSQL 的内存不足等问题。

第一步：识别慢速查询

在修复慢速查询之前，你必须准确识别它。依赖用户投诉效率低下；你需要来自数据库本身的经验数据。

使用 `pg_stat_statements`

在生产环境中跟踪资源密集型查询的最有效方法是使用 pg_stat_statements 扩展。该模块跟踪针对数据库执行的所有查询的执行统计信息。

启用扩展（需要超级用户权限和配置重载）：

-- 1. 确保它在 postgresql.conf 中列出
-- shared_preload_libraries = 'pg_stat_statements'

-- 2. 连接到数据库并创建扩展
CREATE EXTENSION pg_stat_statements;

查询主要问题：

要查找消耗总时间最多的查询，请使用以下查询：

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

在较旧的 PostgreSQL 版本上，这些列可能命名为 total_time 和 mean_time。请使用你的服务器暴露的名称。

总时间和平均时间回答不同的问题。一个平均 20 毫秒但运行一百万次的查询可能是你最大的数据库成本。一个每小时运行一次、耗时 40 秒的查询可能对单个用户很痛苦，但对整个系统来说不那么重要。两者都要关注。

如果你需要当前慢速的查询，而不是历史上昂贵的查询，请检查活动会话：

SELECT pid, now() - query_start AS age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

第二步：使用 `EXPLAIN ANALYZE` 分析执行计划

一旦隔离了慢速查询，下一步关键步骤是理解 PostgreSQL 如何执行它。EXPLAIN 命令显示预期的计划，但 EXPLAIN ANALYZE 实际运行查询并报告每个步骤的实际耗时。

语法和用法

始终使用 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 包装你的慢速查询，以获得最详细的输出。BUFFERS 选项至关重要，因为它显示磁盘 I/O 活动。

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM large_table lt 
JOIN other_table ot ON lt.id = ot.lt_id
WHERE lt.status = 'active' AND lt.created_at > NOW() - INTERVAL '1 day';

解释输出

输出从底部向上和从右到左读取，因为最内层的节点首先执行。需要关注的关键指标包括：

cost=：规划器的估计成本，不是挂钟时间。用它来比较计划选择，而不是作为毫秒。
rows=：该节点处理的估计行数。
actual time=：此特定操作实际花费的时间（毫秒）。
rows=（实际）：此节点返回的实际行数。
loops=：此节点执行的次数（在嵌套循环中通常很高）。

发现低效：

大表上的顺序扫描： 如果大表访问使用 Seq Scan 而不是 Index Scan 或 Bitmap Index Scan，你可能需要更好的索引。
估计行数与实际行数之间存在巨大差异： 如果规划器估计了 10 行，但节点实际处理了 1,000,000 行，则统计信息过时，或者规划器做出了糟糕的选择。
连接/排序上的高 actual time： 在 Hash Join、Merge Join 或 Sort 操作上花费过多时间通常表明内存不足（work_mem）或无法有效使用索引。

还要关注 Buffers 行。shared hit 表示 PostgreSQL 在缓存中找到了页面。shared read 表示它必须从存储中读取页面。查询可能因为计划糟糕而慢，或者因为计划合理但读取了大量来自磁盘的冷数据。

提示： 对于复杂计划，使用在线工具如 explain.depesz.com 或 pgAdmin 的可视化解释计划查看器来图形化解释结果。

第三步：解决常见瓶颈

根据你的 EXPLAIN ANALYZE 发现，应用有针对性的修复。

索引优化

如果 Seq Scan 主导一个大表且查询具有选择性，考虑在 WHERE、JOIN 和 ORDER BY 子句中使用的列上创建索引。顺序扫描并不自动是坏事；当需要大部分行时，PostgreSQL 可能会正确选择它。

示例： 如果查询按 status 过滤然后按 user_id 连接：

-- 创建复合索引以加快查找和连接
CREATE INDEX idx_large_table_status_user_id ON large_table (status, user_id);

对于生产系统，当你需要避免阻塞写入时，使用 CREATE INDEX CONCURRENTLY，并记住它不能在普通事务块内运行：

CREATE INDEX CONCURRENTLY idx_large_table_status_user_id
ON large_table (status, user_id);

更新统计信息（VACUUM ANALYZE）

如果规划器做出严重不准确的估计（估计行数与实际行数不匹配），强制更新表统计信息。

ANALYZE VERBOSE table_name;
-- 对于高度活跃的表，考虑降低每个表的自动清理/分析阈值。

内存调优

如果排序或哈希操作溢出到磁盘（通常由 BUFFERS 输出中的高 I/O 或慢速排序指示），增加 PostgreSQL 的可用工作内存。

-- 为特定查询测试增加会话级别的 work_mem
SET work_mem = '128MB'; 
-- 或在 postgresql.conf 中全局设置以获得持续的性能改进

警告： 如果许多复杂查询并发运行，全局增加 work_mem 过高可能会耗尽系统内存。根据服务器容量仔细调整。

在计划中查找排序或哈希溢出细节。较新的 PostgreSQL 输出可能显示排序方法，如 external merge Disk: ...，这是操作超出可用内存并写入临时文件的明确标志。

查询重写

有时，结构本身就是问题。避免非 SARGable 谓词（阻止索引使用的条件），例如在 WHERE 子句中对索引列应用函数：

低效（阻止索引使用）：

WHERE DATE(created_at) = '2023-10-01'

高效（允许索引使用）：

WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00'

另一个常见模式是选择比应用程序需要的多得多的列。SELECT * 使计划更难优化，增加内存使用，并可能强制额外的堆读取，而索引仅扫描可能有效。对于热路径，有意识地列出列。

锁检查

如果 EXPLAIN ANALYZE 在你的会话中很快，但应用程序很慢，则查询可能在获得有用执行时间之前就等待了。检查锁等待：

SELECT pid, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

然后找到阻塞者：

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.query AS blocked_query,
       blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN pg_locks blocker_locks
  ON blocker_locks.locktype = blocked_locks.locktype
 AND blocker_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocker_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocker
  ON blocker.pid = blocker_locks.pid;

修复可能是应用程序级别的：更短的事务、将慢速外部 API 调用移出事务、避免不必要的 SELECT ... FOR UPDATE，或更改表更新顺序，以便竞争事务不会死锁。

一个小例子：慢速仪表板查询

假设一个仪表板每隔几秒运行此查询：

SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

该表有数百万行。EXPLAIN ANALYZE 显示顺序扫描、大量行被过滤器移除以及排序。第一反应可能是索引 created_at，但谓词包装了列 DATE(created_at)，因此 created_at 上的普通索引不太有用。

将日期过滤器重写为范围：

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

然后考虑匹配过滤器和排序的索引：

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

这不是通用的索引配方。当 paid 是常见的仪表板过滤器，并且应用程序通常请求最新的已支付订单时，它有效。如果应用程序还大量按账户过滤，更好的索引可能以 account_id 开头。关键是围绕实际访问模式设计索引，而不是围绕查询中提到的单个列。

更改后，计划应显示扫描的行数更少，并理想地避免显式排序。如果计划仍然选择顺序扫描，请检查日期范围是否太宽、统计信息是否过时，或者生产中的查询参数是否与你的测试用例不同。

第四步：验证和监控

实施更改后，使用可比较的参数对完全相同的查询重新运行 EXPLAIN ANALYZE。目标不总是看到索引扫描。目标是看到更少的工作：过滤器移除的行更少、读取的缓冲区更少、没有磁盘溢出、更好的行估计，或在昂贵节点上花费的时间更少。

继续监控 pg_stat_statements 以确认修改后的查询不再出现在主要问题列表中，确保修复具有积极的全局影响。

还要在添加索引后关注写入成本。每个新索引必须在插入、更新和删除期间维护。一个仪表板的完美读取索引如果减慢高容量摄取路径，可能是一个糟糕的权衡。对于重要表，检查两方面：慢速查询是否改善，以及写入延迟或表膨胀是否随后恶化。

在真实事件中，另一个有用的习惯是使用现实参数值进行测试。PostgreSQL 可能为拥有十行的客户和拥有千万行的客户选择不同的计划。如果应用程序使用预准备语句，通用计划的行为也可能与你粘贴到 psql 中的一次性查询不同。当生产问题影响一个租户、一个账户或一个日期范围时，尽可能在安全环境中重现该形状。

如果查询具有破坏性或者使用 EXPLAIN ANALYZE 运行成本太高，请从普通 EXPLAIN 开始，在暂存环境运行，或将测试包装在你可以回滚的事务中。对于 UPDATE 和 DELETE，在进行任何生产更改之前，你仍然可以从计划的扫描和连接部分学到很多。

保留一个简短的注释，包含更改前后的计划、时间和原因。这个习惯可以防止以后意外的回归，并为下一个人提供真实的解释，而不是模式中神秘的索引名称。