看到一条查询很慢时,不能只凭 SQL 长短猜原因。PostgreSQL 会根据 SQL、索引、统计信息和配置生成执行计划,再按照计划扫描表、使用索引、连接和排序。
先在当前 psql 会话中取得一个真实的项目 ID。这里假设已经执行前面章节的数据示例,tasks 中至少有一行未归档的待办任务。为了让后面的索引对比更有意义,示例优先选择待办任务最多的项目:
psql
tasks
1SELECT project_id 2FROM tasks 3WHERE status = 'todo' 4 AND archived_at IS NULL 5GROUP BY project_id 6ORDER BY COUNT(*) DESC, project_id 7LIMIT 1 8\gset
\gset 是 psql 元命令,它把查询返回的 project_id 保存为同名变量。接下来使用 EXPLAIN 查看数据库准备怎样执行查询;普通 EXPLAIN 只生成计划,不会真正执行被分析的语句:
\gset
project_id
EXPLAIN
1EXPLAIN 2SELECT id, title, created_at 3FROM tasks 4WHERE project_id = :project_id 5 AND archived_at IS NULL 6ORDER BY created_at DESC, id DESC 7LIMIT 20;
输出可能包含:
1Limit (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。不能为了让输出与文章一致就认定某种计划必须出现。
Seq Scan
缩进表示计划树的父子关系。这个简单计划中,Index Scan 是 Limit 的子节点:上层不断向子节点请求数据,取到 20 行或子节点没有更多结果时停止。因此可以先找到缩进最深的扫描节点,再顺着缩进向外理解数据如何被筛选、连接和返回。复杂计划可能有多个并列子节点,不能机械地把所有行按从下到上的顺序阅读。
Index Scan
Limit
括号中的估算信息分别是:
cost=0.14..8.16
rows=1
width=48
cost 是规划器比较候选方案的内部单位,不是毫秒。父节点的成本已经包含子节点成本,不能把每一行的 cost 再相加。遇到 LIMIT、EXISTS 这类可能提前停止的操作时,启动成本也会直接影响规划器的选择。
cost
LIMIT
EXISTS
ANALYZE 是 EXPLAIN 的一个选项。启用后,PostgreSQL 会真正执行 SQL,并把实际耗时和行数附加到原来的估算信息中:
ANALYZE
1EXPLAIN (ANALYZE, BUFFERS) 2SELECT id, title, created_at 3FROM tasks 4WHERE project_id = :project_id 5 AND archived_at IS NULL 6ORDER BY created_at DESC, id DESC 7LIMIT 20;
输出中的节点可能类似下面这样:
1Limit (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 6Planning Time: 0.120 ms 7Execution Time: 0.036 ms
actual time=a..b 表示节点每次执行时,产生第一行和结束执行的平均耗时,单位是毫秒;rows 是每次执行平均返回的行数,loops 是执行次数。节点被循环调用时,要把时间和行数与 loops 结合起来看,才能理解它累计完成了多少工作。
actual time=a..b
rows
loops
上层节点的时间通常包含子节点耗时,因此不能把所有节点的时间相加,也不能只因为顶层节点时间最大就认定问题发生在那里。EXPLAIN ANALYZE 还会增加采样计时开销,它反映的是带执行统计的数据库端运行情况,不应直接等同于接口完整响应时间。
EXPLAIN ANALYZE
BUFFERS 中的 shared hit 表示数据块已经在 PostgreSQL 共享缓冲区中,避免了读取;shared read 表示需要把数据块读入共享缓冲区,但操作系统缓存仍可能满足这次读取,所以它不等同于一次物理磁盘访问。父节点显示的缓冲区数量也包含子节点,不能跨层级直接累加。
BUFFERS
shared hit
shared read
必须注意:EXPLAIN 的 ANALYZE 选项会执行语句。对 UPDATE、DELETE、INSERT 或 MERGE 使用时会真的修改数据。需要分析写入语句,可以在事务中执行后回滚:
UPDATE
DELETE
INSERT
MERGE
1BEGIN; 2 3EXPLAIN (ANALYZE, BUFFERS) 4UPDATE tasks 5SET priority = 2 6WHERE project_id = :project_id 7 AND archived_at IS NULL; 8 9ROLLBACK;
即使最后回滚,语句执行期间仍然会获取锁、产生数据库负载并触发相关触发器。回滚只能撤销受事务控制的数据库修改,序列取值以及某些数据库外部副作用不一定能够撤销。生产环境分析写入前必须确认语句影响范围和触发器行为,不能把 ROLLBACK 当作绝对安全保证。
ROLLBACK
此段落已有评论,按 Enter 查看评论及回复列表。
连接 GitHub 后查看和发表评论,也可在 GitHub 查看公开讨论。
1. EXPLAIN
看到一条查询很慢时,不能只凭 SQL 长短猜原因。PostgreSQL 会根据 SQL、索引、统计信息和配置生成执行计划,再按照计划扫描表、使用索引、连接和排序。
先在当前
psql会话中取得一个真实的项目 ID。这里假设已经执行前面章节的数据示例,tasks中至少有一行未归档的待办任务。为了让后面的索引对比更有意义,示例优先选择待办任务最多的项目:\gset是psql元命令,它把查询返回的project_id保存为同名变量。接下来使用EXPLAIN查看数据库准备怎样执行查询;普通EXPLAIN只生成计划,不会真正执行被分析的语句:输出可能包含:
这只是一个可能的输出。计划会随数据量、数据分布和统计信息变化;练习表很小时,PostgreSQL 也可能选择
Seq Scan。不能为了让输出与文章一致就认定某种计划必须出现。缩进表示计划树的父子关系。这个简单计划中,
Index Scan是Limit的子节点:上层不断向子节点请求数据,取到 20 行或子节点没有更多结果时停止。因此可以先找到缩进最深的扫描节点,再顺着缩进向外理解数据如何被筛选、连接和返回。复杂计划可能有多个并列子节点,不能机械地把所有行按从下到上的顺序阅读。括号中的估算信息分别是:
cost=0.14..8.16rows=1width=48cost是规划器比较候选方案的内部单位,不是毫秒。父节点的成本已经包含子节点成本,不能把每一行的cost再相加。遇到LIMIT、EXISTS这类可能提前停止的操作时,启动成本也会直接影响规划器的选择。2. ANALYZE
ANALYZE是EXPLAIN的一个选项。启用后,PostgreSQL 会真正执行 SQL,并把实际耗时和行数附加到原来的估算信息中:输出中的节点可能类似下面这样:
actual time=a..b表示节点每次执行时,产生第一行和结束执行的平均耗时,单位是毫秒;rows是每次执行平均返回的行数,loops是执行次数。节点被循环调用时,要把时间和行数与loops结合起来看,才能理解它累计完成了多少工作。上层节点的时间通常包含子节点耗时,因此不能把所有节点的时间相加,也不能只因为顶层节点时间最大就认定问题发生在那里。
EXPLAIN ANALYZE还会增加采样计时开销,它反映的是带执行统计的数据库端运行情况,不应直接等同于接口完整响应时间。BUFFERS中的shared hit表示数据块已经在 PostgreSQL 共享缓冲区中,避免了读取;shared read表示需要把数据块读入共享缓冲区,但操作系统缓存仍可能满足这次读取,所以它不等同于一次物理磁盘访问。父节点显示的缓冲区数量也包含子节点,不能跨层级直接累加。必须注意:
EXPLAIN的ANALYZE选项会执行语句。对UPDATE、DELETE、INSERT或MERGE使用时会真的修改数据。需要分析写入语句,可以在事务中执行后回滚:即使最后回滚,语句执行期间仍然会获取锁、产生数据库负载并触发相关触发器。回滚只能撤销受事务控制的数据库修改,序列取值以及某些数据库外部副作用不一定能够撤销。生产环境分析写入前必须确认语句影响范围和触发器行为,不能把
ROLLBACK当作绝对安全保证。