1. EXPLAIN

看到一条查询很慢时，不能只凭 SQL 长短猜原因。PostgreSQL 会根据 SQL、索引、统计信息和配置生成执行计划，再按照计划扫描表、使用索引、连接和排序。

先在当前 psql 会话中取得一个真实的项目 ID。这里假设已经执行前面章节的数据示例，tasks 中至少有一行未归档的待办任务。为了让后面的索引对比更有意义，示例优先选择待办任务最多的项目：

1
SELECT project_id
2
FROM tasks
3
WHERE status = 'todo'
4
  AND archived_at IS NULL
5
GROUP BY project_id
6
ORDER BY COUNT(*) DESC, project_id
7
LIMIT 1
8
\gset

\gset 是 psql 元命令，它把查询返回的 project_id 保存为同名变量。接下来使用 EXPLAIN 查看数据库准备怎样执行查询；普通 EXPLAIN 只生成计划，不会真正执行被分析的语句：

1
EXPLAIN
2
SELECT id, title, created_at
3
FROM tasks
4
WHERE project_id = :project_id
5
  AND archived_at IS NULL
6
ORDER BY created_at DESC, id DESC
7
LIMIT 20;

输出可能包含：

1
Limit  (cost=0.14..8.16 rows=1 width=48)
2
  ->  Index Scan using tasks_active_project_created_idx on tasks  (cost=0.14..8.16 rows=1 width=48)
3
        Index Cond: (project_id = 1)

这只是一个可能的输出。计划会随数据量、数据分布和统计信息变化；练习表很小时，PostgreSQL 也可能选择 Seq Scan。不能为了让输出与文章一致就认定某种计划必须出现。

缩进表示计划树的父子关系。这个简单计划中，Index Scan 是 Limit 的子节点：上层不断向子节点请求数据，取到 20 行或子节点没有更多结果时停止。因此可以先找到缩进最深的扫描节点，再顺着缩进向外理解数据如何被筛选、连接和返回。复杂计划可能有多个并列子节点，不能机械地把所有行按从下到上的顺序阅读。

括号中的估算信息分别是：

cost 是规划器比较候选方案的内部单位，不是毫秒。父节点的成本已经包含子节点成本，不能把每一行的 cost 再相加。遇到 LIMIT、EXISTS 这类可能提前停止的操作时，启动成本也会直接影响规划器的选择。

2. ANALYZE

ANALYZE 是 EXPLAIN 的一个选项。启用后，PostgreSQL 会真正执行 SQL，并把实际耗时和行数附加到原来的估算信息中：

1
EXPLAIN (ANALYZE, BUFFERS)
2
SELECT id, title, created_at
3
FROM tasks
4
WHERE project_id = :project_id
5
  AND archived_at IS NULL
6
ORDER BY created_at DESC, id DESC
7
LIMIT 20;

输出中的节点可能类似下面这样：

1
Limit  (cost=0.14..8.16 rows=1 width=48) (actual time=0.018..0.019 rows=1.00 loops=1)
2
  Buffers: shared hit=2
3
  ->  Index Scan using tasks_active_project_created_idx on tasks  (cost=0.14..8.16 rows=1 width=48) (actual time=0.017..0.018 rows=1.00 loops=1)
4
        Index Cond: (project_id = 1)
5
        Buffers: shared hit=2
6
Planning Time: 0.120 ms
7
Execution Time: 0.036 ms

actual time=a..b 表示节点每次执行时，产生第一行和结束执行的平均耗时，单位是毫秒；rows 是每次执行平均返回的行数，loops 是执行次数。节点被循环调用时，要把时间和行数与 loops 结合起来看，才能理解它累计完成了多少工作。

上层节点的时间通常包含子节点耗时，因此不能把所有节点的时间相加，也不能只因为顶层节点时间最大就认定问题发生在那里。EXPLAIN ANALYZE 还会增加采样计时开销，它反映的是带执行统计的数据库端运行情况，不应直接等同于接口完整响应时间。

BUFFERS 中的 shared hit 表示数据块已经在 PostgreSQL 共享缓冲区中，避免了读取；shared read 表示需要把数据块读入共享缓冲区，但操作系统缓存仍可能满足这次读取，所以它不等同于一次物理磁盘访问。父节点显示的缓冲区数量也包含子节点，不能跨层级直接累加。

必须注意：EXPLAIN 的 ANALYZE 选项会执行语句。对 UPDATE、DELETE、INSERT 或 MERGE 使用时会真的修改数据。需要分析写入语句，可以在事务中执行后回滚：

1
BEGIN;
2

3
EXPLAIN (ANALYZE, BUFFERS)
4
UPDATE tasks
5
SET priority = 2
6
WHERE project_id = :project_id
7
  AND archived_at IS NULL;
8

9
ROLLBACK;

即使最后回滚，语句执行期间仍然会获取锁、产生数据库负载并触发相关触发器。回滚只能撤销受事务控制的数据库修改，序列取值以及某些数据库外部副作用不一定能够撤销。生产环境分析写入前必须确认语句影响范围和触发器行为，不能把 ROLLBACK 当作绝对安全保证。