掌握 EXPLAIN ANALYZE：PostgreSQL 查询计划优化指南

当 PostgreSQL 查询感觉缓慢，而常规猜测不再奏效时，EXPLAIN ANALYZE 就是我求助的工具。也许查询在应用代码中看起来无害。也许表有索引，每个人都假设数据库一定在使用它。也许查询在预发布环境很快，但在生产环境却很慢。计划就是这些假设要么成立要么被推翻的地方。

一个有用的习惯是将计划视为 PostgreSQL 完成工作的故事：它预期接触哪些行，实际接触了哪些行，在哪里连接，在哪里排序，是否留在内存中，以及是否必须从磁盘读取。在变得有用之前，你不需要记住每个计划节点。你需要做的是放慢速度，将估计值与实际情况进行比较。

理解 EXPLAIN 与 EXPLAIN ANALYZE

EXPLAIN 和 EXPLAIN ANALYZE 之间的区别很重要，因为一个是预测，另一个是测量。

EXPLAIN

当你运行一个以 EXPLAIN 为前缀的查询时，PostgreSQL 会生成预期的执行计划，而不会实际执行查询。这对于以下情况很有用：

• 预览计划： 你可以看到 PostgreSQL 预期运行查询的最廉价方式。
• 估算成本： 它提供计划中每个节点的成本估算，让你对资源使用情况有一个相对的概念。

示例：

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE 更进一步。它不仅显示计划的执行，还执行查询，然后报告实际的执行统计信息。这意味着你可以得到：

• 实际执行时间： 每个步骤真正花了多长时间。
• 实际行数： 每个节点实际处理了多少行。
• 确认估计值： 你可以将估计的行数与实际行数进行比较，看看 PostgreSQL 的规划器是否做出了准确的预测。

这使得 EXPLAIN ANALYZE 成为真正调优的更好工具，但它有一个尖锐的缺点：它会运行查询。一个 SELECT 仍然可能很昂贵，因为它可能扫描大量数据、获取锁或竞争缓存。一个 UPDATE、DELETE 或 INSERT 实际上会修改数据，除非你将其包装在事务中并回滚：

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

这种模式在维护窗口或预发布副本中很有用。但它不是在生产繁忙数据库上运行危险语句的免费通行证。

示例：

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

解码 EXPLAIN ANALYZE 的输出

EXPLAIN ANALYZE 的输出起初可能看起来很密集，但理解其关键组成部分是基础。

核心组成部分：

• 节点类型： 标识正在执行的操作（例如，Seq Scan、Index Scan、Hash Join、Nested Loop、Sort、Aggregate）。
• 成本： 表示为 (startup_cost .. total_cost)。
  • startup_cost：检索第一行的成本。
  • total_cost：检索所有行的成本。
  • 注意：成本是用于比较的任意单位，不直接表示时间或内存。
• 行数： 规划器预期从该节点返回的估计行数。
• 宽度： 该节点返回行的估计平均宽度（以字节为单位）。
• 实际时间： 表示为 (startup_time .. total_time)。这是执行该节点的实际时间（以毫秒为单位）。
  • startup_time：返回第一行的实际时间。
  • total_time：返回所有行的实际时间。
• 实际行数： 该节点返回的实际行数。
• 循环次数： 该节点执行的次数。对于顶层节点，通常为 1。对于嵌套操作，可能更高。

示例输出解读：

让我们考虑一个在大表上进行 Seq Scan（顺序扫描）的简化示例：

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

解读：

• Seq Scan on users：数据库正在读取 users 表中的每一行。
• cost=0.00..15000.00：规划器估计总成本约为 15000 单位。
• rows=1000000：规划器估计表中有 100 万行。
• actual time=0.020..150.500：实际花了 150.5 毫秒完成扫描和过滤。
• rows=950000：实际返回了 950,000 行（过滤后）。
• loops=1：此扫描执行了一次。
• Filter: (registration_date > '2023-01-01')：这是用于过滤行的条件。
• Rows Removed by Filter: 50000：过滤器丢弃了 50,000 行。

瓶颈识别： 不要只寻找最大的 actual time。还要寻找执行次数多的节点。一个耗时 0.2 毫秒的嵌套循环内层扫描可能看起来无害，直到 loops=50000。在这种情况下，实际成本大约是每次循环的时间乘以循环次数。

从内向外阅读

PostgreSQL 计划是树形的。顶层节点将最终结果返回给客户端，但工作通常从计划的更深处开始。当一个查询连接 orders、customers 和 order_items 时，最顶层可能是一个 Aggregate，但真正的痛点可能在于下面的扫描或连接。

我通常按以下顺序阅读计划：

1. 从最深的扫描节点开始，问：PostgreSQL 读取的行数是否远多于查询返回的行数？
2. 比较估计的 rows 和实际的 rows。
3. 检查昂贵的节点是否有高的 loops。
4. 查找溢出到磁盘的 Sort、Hash 或 Materialize 节点。
5. 使用 BUFFERS 来判断查询主要是 CPU/缓存工作还是磁盘 I/O。

这是一个常见的例子：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

如果你看到对数百万 orders 行进行顺序扫描，然后排序，再限制，那么数据库在返回你请求的 20 行之前做了太多工作。一个实用的索引可能是：

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

之后，一个好的计划可能会使用索引直接定位到该客户的最新订单，并在 20 行后停止。确切的计划取决于表大小、统计信息、PostgreSQL 版本和数据分布，但原则是稳定的：将索引与你实际使用的过滤和排序模式匹配。

常见查询计划节点及优化策略

理解不同类型的节点以及如何优化它们是掌握查询性能的关键。

1. 顺序扫描 (Seq Scan)

• 是什么： 读取表中的每一行。对于大表来说，这通常效率低下，尤其是在特定条件下进行过滤时。
• 何时可以接受： 对于小表，或者当你需要检索表的大部分行时。顺序扫描并不自动意味着不好。
• 优化： 在选择性过滤列上创建索引，但通过计划验证。如果一个谓词返回了表的大部分数据，PostgreSQL 可能会正确地继续使用顺序扫描。

2. 索引扫描 (Index Scan)

• 是什么： 使用索引查找与 WHERE 子句匹配的行。PostgreSQL 遍历索引，然后从表中获取相应的行。
• 优化： 确保索引与查询形状匹配。对于复合索引，列顺序很重要。一个在 (tenant_id, created_at) 上的索引有助于按 tenant_id 过滤并按 created_at 排序的查询；但对于仅按 created_at 过滤的查询，可能帮助不大。

3. 仅索引扫描 (Index Only Scan)

• 是什么： 一种优化的 Index Scan，其中查询所需的所有数据都直接在索引中可用。PostgreSQL 不需要访问表堆。
• 何时高效： 当所有选定的列都来自索引，并且可见性映射允许 PostgreSQL 避免许多堆检查时。
• 优化： 考虑为读取密集型路径使用带有 INCLUDE 的覆盖索引，但不要“以防万一”添加每一列。更大的索引在写入时维护成本更高。

4. 连接操作 (Nested Loop、Hash Join、Merge Join)

• Nested Loop： 对于外部关系中的每一行，PostgreSQL 扫描内部关系。适用于外部关系较小或内部关系可以通过索引快速访问的情况。
• Hash Join： 从一个关系（构建端）构建哈希表，并用另一个关系（探测端）的行进行探测。适用于大表且索引对连接条件无益的情况。
• Merge Join： 要求两个关系在连接键上排序。合并排序后的列表。适用于大型、已排序的输入。
• 优化：
  • 确保连接列上有索引。
  • 检查是否由于糟糕的行估计导致了错误的连接选择。PostgreSQL 不像某些数据库那样支持原生优化器提示，因此通常的修复方法是更好的统计信息、更好的索引或查询重写。
  • 检查 EXPLAIN ANALYZE 中连接节点上的高 loops 计数或高 actual time。

5. 排序 (Sort)

• 是什么： 对行进行排序。计算成本可能很高，尤其是在大数据集上。
• 优化：
  • 当查询具有足够的选择性时，添加一个列顺序与 ORDER BY 模式匹配的索引。
  • 通过添加更多限制性的 WHERE 子句来减少排序的行数。
  • 确保配置了足够的 work_mem，以便排序在内存中而不是磁盘上进行。

6. 聚合 (Aggregate)

• 是什么： 执行 COUNT()、SUM()、AVG()、GROUP BY 等操作。
• 优化：
  • 确保 WHERE 子句高效，减少聚合前的行数。
  • 如果聚合是频繁且缓慢的操作，考虑使用物化视图进行预聚合。
  • 为 GROUP BY 子句中使用的列创建索引。

使用带选项的 EXPLAIN ANALYZE

EXPLAIN ANALYZE 有几个有用的选项，可以提供更详细的信息。

VERBOSE

• 作用： 显示关于查询计划的额外信息，例如模式限定的表名和输出列名。

EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

• 作用： 在输出中包含估计成本。这是默认行为，但你可以显式关闭它。

EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

• 作用： 报告缓冲区使用情况（共享、临时和本地）。这有助于识别 I/O 瓶颈。
  • shared hit：在 PostgreSQL 共享缓冲区缓存中找到的块。
  • shared read：从磁盘读取到共享缓冲区的块。
  • temp read/written：读取/写入临时文件的块（通常用于超出 work_mem 的排序或哈希）。

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

• 作用： 包含每个节点的实际启动时间和总时间。这是 ANALYZE 的默认行为。

EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

组合选项

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

实用技巧和最佳实践

• 从 EXPLAIN ANALYZE 开始： 始终使用 EXPLAIN ANALYZE 进行实际性能分析。仅 EXPLAIN 是不够的。
• 关注 actual time： 优先优化具有最高 actual time 的节点。
• 比较 rows（估计值与实际值）： 大的差异表明 PostgreSQL 的查询规划器可能做出了不准确的假设。这通常可以通过使用 ANALYZE <table_name>; 更新表统计信息或创建适当的索引来修复。
• 使用 BUFFERS： 分析缓冲区使用情况，以了解你的查询是否受 I/O 限制。
• 使用真实数据进行测试： 在具有代表性数据量和与生产环境类似数据分布的数据库上运行 EXPLAIN ANALYZE。
• 分阶段优化： 不要试图一次优化所有内容。首先解决最大的瓶颈。
• 考虑 work_mem： 如果你在排序或哈希时看到大量的磁盘读取（BUFFERS 中的 temp read/written），增加 work_mem（每个会话或全局）可能会有所帮助，但要注意内存使用。
• 明智地创建索引： 只创建实际使用且有益的索引。过多的索引会减慢写入速度并消耗磁盘空间。
• 检查 PostgreSQL 版本： 较新的版本通常具有改进的查询规划器和可能影响性能的新功能。

一个实际的调优过程

考虑这个查询：

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

如果计划显示顺序扫描，仅靠 email 上的索引可能没有帮助，因为查询应用了 lower(email)。当查询中的表达式与索引值不同时，PostgreSQL 不能总是使用普通索引。一个更好的选择可能是表达式索引：

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

然后重新运行：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

你希望看到扫描的行数更少、读取的缓冲区更少以及执行时间更短。如果计划仍然不使用索引，请检查表是否很小、统计信息是否过时，或者查询是否没有按照你认为应用发送的方式编写。

另一个常见情况是 SQL 中看起来不错的连接，但在计划中却爆炸了：

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

有用的索引可能包括 orders(created_at)、order_items(order_id) 和 products(id) 上的主键。但如果过去七天包含了 orders 表的大部分数据，orders(created_at) 可能不是主要的修复方法。计划会告诉你真正的问题是日期过滤器、连接扇出还是子表上缺少索引。

好的 PostgreSQL 查询调优不是“添加索引直到计划改变”。它是一个循环：测量实际计划，做一个合理的更改，再次测量，并且只有当更改改善了你真正关心的工作负载时才保留它。