第 11 章 性能提示

Postgres会为收到的每个查询制定一个查询计划。选择与查询结构和数据特性相匹配的正确计划，对获得良好的性能至关重要。可以使用EXPLAIN命令查看系统为任意查询生成的查询计划。遗憾的是，读懂计划是一门值得专门教程详述的技艺，而我还没来得及写一篇。这里只做一些快速而粗略的解释。

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

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

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

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

平均宽度是相当不可靠的，因为系统其实并不知道变长列的平均长度。我在考虑将来改进这一点，但这可能不值得费那个麻烦，因为宽度的用处不大。

这里是一些例子（使用执行过 vacuum analyze 的回归测试数据库，以及接近 7.0 的源码）：

regression=# explain select * from tenk1;
NOTICE:  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）。

现在修改查询，添加一个限定子句：

regression=# explain select * from tenk1 where unique1 < 1000;
NOTICE:  QUERY PLAN:

Seq Scan on tenk1  (cost=0.00..358.00 rows=1000 width=148)
    

由于有了 WHERE 子句，估计输出行数减少了。（这个估计出奇地准确，因为 tenk1 是一个特别简单的情形——unique1 列有 10000 个从 0 到 9999 的不同值，所以估计器在列的最小值和最大值之间做的线性插值正中目标。）但扫描仍需访问全部 10000 个元组，因此代价没有下降；实际上还略有上升，以反映检查 WHERE 条件所消耗的额外 CPU 时间。

进一步收紧限定条件：

regression=# explain select * from tenk1 where unique1 < 100;
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..89.35 rows=100 width=148)
    

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

再向限定条件添加另一个条件：

regression=# explain select * from tenk1 where unique1 < 100 and
regression-# stringu1 = 'xxx';
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..89.60 rows=1 width=148)
    

新增的子句 "stringu1 = 'xxx'" 降低了估计输出行数，但没有降低代价，因为仍然必须访问同一组元组。

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

regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..144.07 rows=100 width=296)
  ->  Index Scan using tenk1_unique1 on tenk1 t1
             (cost=0.00..89.35 rows=100 width=148)
  ->  Index Scan using tenk2_unique2 on tenk2 t2
             (cost=0.00..0.53 rows=1 width=148)
    

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

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

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

regression=# set enable_nestloop = off;
SET VARIABLE
regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Hash Join  (cost=89.60..574.10 rows=100 width=296)
  ->  Seq Scan on tenk2 t2
               (cost=0.00..333.00 rows=10000 width=148)
  ->  Hash  (cost=89.35..89.35 rows=100 width=148)
        ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..89.35 rows=100 width=148)
    

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