第 10 章 性能提示

PostgreSQL会为收到的每个查询制定一个查询计划。选择与查询结构和数据特性相匹配的正确计划，对获得良好的性能至关重要。可以使用EXPLAIN命令查看系统为任意查询生成的查询计划。读懂计划是一门值得专门教程详述的技艺，本节并非那样的教程，但这里会介绍一些基础知识。

EXPLAIN当前引用的这些数字是：

这些开销以磁盘页读取的次数为单位来衡量。（CPU 工作量估计会用一些相当任意的经验系数换算成磁盘页单位。如果你想试验这些系数，可以看PostgreSQL 7.3.21 管理员指南中的运行时配置参数列表。）

需要理解的一点是，上层节点的开销包含了其所有子节点的开销。还要注意，这个开销只反映规划器/优化器关心的内容。特别是，它没有考虑将结果行传输给前端所消耗的时间——这在实际耗时中可能是重要因素，但规划器会忽略这些代价，因为它无法通过改变计划来影响它们。（我们相信每个正确的计划都会输出相同的行集。）

rows 输出值有些容易误解，因为它不是查询处理或扫描的行数——这个数字通常更小，反映的是在该节点上应用的任何WHERE条件子句的估计选择率。理想情况下，顶层的行数估计应当接近查询实际返回、更新或删除的行数。

这里是一些例子（使用执行过VACUUM ANALYZE的回归测试数据库，以及 7.3 开发版源码）：

regression=# EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)

这大概是最简单的情况了。如果你执行

SELECT * FROM pg_class WHERE relname = 'tenk1';

就会发现tenk1有 233 个磁盘页和 10000 行。因此代价估计为 233 次页面读取（每次定义为 1.0），加上 10000 * cpu_tuple_cost（当前为 0.01；可以试试SHOW cpu_tuple_cost）。

现在修改查询，添加一个WHERE条件：

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..358.00 rows=1033 width=148)
   Filter: (unique1 < 1000)

由于有了WHERE子句，估计输出行数减少了。但扫描仍需访问全部 10000 行，因此代价没有下降；实际上还略有上升，以反映检查WHERE条件所消耗的额外 CPU 时间。

这条查询实际选出的行数是 1000，但估计值只是近似。如果重复这个实验，很可能会得到略有不同的估计值；此外，由于ANALYZE生成的统计信息来自该表的随机采样，这个估计值可能在每次执行ANALYZE命令之后发生变化。

进一步收紧查询条件：

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.33 rows=49 width=148)
   Index Cond: (unique1 < 50)

可以看到，如果WHERE条件的选择性足够强，规划器最终会判定索引扫描比顺序扫描更便宜。得益于索引，这个计划只需访问 50 行，因此尽管每次单独读取比顺序读取一个完整磁盘页更昂贵，它仍然是赢家。

再向WHERE条件添加另一个子句：

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50
regression-# AND stringu1 = 'xxx';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.45 rows=1 width=148)
   Index Cond: (unique1 < 50)
   Filter: (stringu1 = 'xxx'::name)

新增的子句stringu1 = 'xxx'降低了估计输出行数，却没有降低代价，因为仍然必须访问同一组行。注意，stringu1子句不能用作索引条件（因为该索引只建立在unique1列上）。它只能作为过滤条件，应用到通过索引取出的行上。因此，代价实际上略有增加，以反映这项额外检查。

使用之前讨论的字段，尝试连接两个表：

regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;

                               QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                                      (cost=0.00..179.33 rows=49 width=148)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                                      (cost=0.00..3.01 rows=1 width=148)
         Index Cond: ("outer".unique2 = t2.unique2)

在这个嵌套循环连接中，外层扫描就是前前一个例子中的那个索引扫描，其代价和行计数也相同，因为unique1 < 50这个WHERE子句正是在该节点上应用的。t1.unique2 = t2.unique2子句此时还无关，因此不会影响外层扫描的行计数。对于内层扫描，当前外层扫描行的unique2值被代入内层索引扫描，产生一个形如t2.unique2 = constant的索引条件。因此我们得到的内层扫描计划和代价，与执行EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42之类查询所得到的相同。循环节点的代价则建立在外层扫描代价之上，再加上每个外层行都要执行一次内层扫描的代价（这里是 49 * 3.01），以及少量连接处理的 CPU 时间。

本例中，循环的输出行数等于两个扫描行数的乘积，但并非所有情况都如此，因为可能还有同时涉及两个关系的WHERE子句，因此只能在连接处应用，不能应用到任一输入扫描。例如，如果我们加上WHERE ... AND t1.hundred < t2.hundred，它会减少连接节点的输出行数，但不会改变任何一个输入扫描。

查看不同计划的一种办法，是使用针对每种计划类型的启用/禁用开关，强制规划器放弃它认为的最优策略。（这是一种粗糙的工具，但很有用。另见第 10.3 节。）

regression=# SET enable_nestloop = off;
SET
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;

                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   ->  Hash  (cost=179.33..179.33 rows=49 width=148)
         ->  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 < 50)

这个计划打算用与之前相同的索引扫描取出tenk1中那 50 行感兴趣的行，把它们存入一个内存中的哈希表，然后对tenk2做顺序扫描，对每一行tenk2在哈希表中探测t1.unique2 = t2.unique2的可能匹配。读取tenk1并建立哈希表的代价完全是哈希连接的启动代价，因为在开始读取tenk2之前不会有任何输出。连接的总时间估计中还包含一笔不小的 CPU 时间开销，用于对哈希表探测 10000 次。但注意，我们不是按 10000 乘以 179.33 来计费的；在这种计划类型中，哈希表的建立只做一次。

可以使用EXPLAIN ANALYZE检查规划器代价估计的准确性。这个命令会实际执行查询，然后显示每个计划节点内累计的真实运行时间，以及普通EXPLAIN所显示的相同估计代价。例如，我们可能得到这样的结果：

regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.18..29.82 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.63..8.91 rows=50 loops=1)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.29..0.32 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.60 msec

注意，“actual time”值以真实时间的毫秒为单位，而“cost”估计则以任意磁盘读取单位表示，因此两者不太可能相互吻合。需要关注的是实际时间与估计代价之间的比值是否一致。

在某些查询计划中，一个子计划节点可能会执行多次。例如，上面那个嵌套循环计划中的内层索引扫描会对外层的每一行执行一次。在这种情况下，“loops”值报告的是该节点的总执行次数，而显示的 actual time 和 rows 值是每次执行的平均值。这样做是为了让这些数字更容易与代价估计的展示方式相比较。将它们乘以“loops”值，就能得到该节点实际消耗的总时间。

EXPLAIN ANALYZE显示的Total runtime包括执行器启动和关闭所需的时间，以及处理结果行所花费的时间。它不包括解析、重写和规划时间。对于SELECT查询，总运行时间通常只比顶层计划节点报告的总时间略大。对于INSERT、UPDATE和DELETE命令，总运行时间可能大得多，因为它包括了处理结果行所花费的时间。在这些命令中，顶层计划节点的时间本质上就是计算新行和/或定位旧行所花费的时间，但不包括实施更改所花费的时间。

值得注意的是，不应把EXPLAIN的结果外推到实际测试场景之外的情况；例如，不能假定在很小的表上得到的结果也适用于大型表。规划器的代价估计不是线性的，因此它可能会为更大或更小的表选择不同的计划。一个极端示例是：对于只占用一个磁盘页的表，无论索引是否可用，几乎总会得到顺序扫描计划。规划器认识到，无论如何处理该表都需要一次磁盘页读取，因此再额外读取页面去查看索引并没有价值。