45 EXPLAIN 与查询计划

EXPLAIN 展示 PostgreSQL 计划执行查询的方式——它会使用哪些索引、采用哪些连接策略，以及预计每一步会得到多少行。EXPLAIN ANALYZE 会实际运行查询，并在估算值旁报告实际行数和耗时。估算值与实际值之间的差距能告诉你规划器的统计信息是否准确；要找出慢查询，就需要熟练阅读这些计划。

本章要点

• 自底向上阅读查询计划
• 估算行数/耗时与实际行数/耗时的区别
• 最常见的节点类型：Seq Scan、Index Scan、Bitmap Heap Scan、Nested Loop、Hash Join、Merge Join、Sort、Aggregate
• EXPLAIN ANALYZE、BUFFERS 及其他选项
• 估算值严重偏离时如何修复

示例

-- Plain EXPLAIN: estimates only
EXPLAIN SELECT * FROM orders_example WHERE customer_id = 1;

-- EXPLAIN ANALYZE: actually runs the query, shows real timing
EXPLAIN ANALYZE SELECT * FROM orders_example WHERE customer_id = 1;

-- With BUFFERS: how many pages came from cache vs. disk
EXPLAIN (ANALYZE, BUFFERS)
  SELECT c.name, count(o.id) AS orders
  FROM customers_example c
  LEFT JOIN orders_example o ON o.customer_id = c.id
  GROUP BY c.name
  ORDER BY orders DESC;

-- A more complex plan to study
EXPLAIN ANALYZE
  WITH spend AS (
    SELECT customer_id, sum(total) AS s FROM orders_example GROUP BY customer_id
  )
  SELECT c.name, s.s
  FROM customers_example c
  LEFT JOIN spend s ON s.customer_id = c.id
  ORDER BY s.s DESC NULLS LAST;

应从最内层、最深的节点向上读取计划。每一行都是一个步骤；子步骤的输出会供父步骤使用。每一行中的字段含义如下：

• cost=startup..total——规划器的估算值，使用任意单位。第一个数字表示第一行准备就绪的时间；第二个数字表示最后一行准备就绪的时间。
• rows=N——估算的行数。
• width=N——平均行宽，单位为字节。
• actual time=startup..total ms（使用 ANALYZE 时）——实际耗时，按每次循环分别计算。
• rows=N actual——实际行数。
• loops=N——该节点执行的次数（在嵌套循环连接中，内侧一方会针对外侧的每一行运行一次）。

常见的扫描类型：

• Seq Scan：读取整张表。对于小表没问题；对于大表，除非确实需要每一行，否则就不理想。
• Index Scan：遍历索引，从表中取出匹配的行。匹配行很少时最佳。
• Index Only Scan：索引包含所需的值，但可能仍需访问堆来检查可见性，除非可见性映射将堆页标记为全可见。
• Bitmap Heap Scan / Bitmap Index Scan：先构建匹配页的位图，然后读取这些页。匹配行较多时很有用。

连接策略：

• Nested Loop：对外侧的每一行，查找匹配的内侧行。外侧结果集较小且内侧有索引时速度很快。
• Hash Join：为较小的一侧构建哈希表，再用较大的一侧进行探测。中等规模连接速度很快。
• Merge Join：两侧都按连接键排序，并行遍历。两侧已经排序时最佳（例如由索引提供排序）。

如果估算的 rows=N 与实际的 rows=M actual 相差几个数量级，说明规划器使用的统计信息已经过时或具有误导性。运行 ANALYZE table 刷新统计信息。

运行示例

运行方式：

$ psql -f source/explain.sql postgres
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on orders_example  (cost=0.00..1.04 rows=2 width=10)
   Filter: (customer_id = 1)
(2 rows)
 ...

常见陷阱

• 从上到下而不是从下到上读取计划。叶节点才是实际数据源；上面的所有节点都在进行转换。
• 不要把 cost= 当成绝对值。这些数字没有单位——只有在同一计划内相互比较才有意义。
• EXPLAIN ANALYZE 会实际运行查询，包括任何 INSERT/UPDATE/DELETE。要预览写操作，请将其包在事务中并执行 ROLLBACK：BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;。
• 小表会让优化失去意义。请使用有代表性的数据进行测试；对 10 行执行 Seq Scan 说明不了任何问题。

下载

SQL 文件：explain.sql · 全部示例文件 · 准备工作