11.1. 使用 EXPLAIN

PostgreSQL 为给它的每个 查询产生一个 查询规划 ． 为匹配查询结构和数据属性选择正确的规划对性能绝对有关键性的影响． 你可以使用 EXPLAIN 命令察看系统为每个查询生成的 查询规划是什么．阅读查询规划是一门值得写一个相当长的教程的学问， 而我这份文档可不是这样的教程，但是这里有一些基本的信息．

目前被 EXPLAN 引用的数字是：

• 预计的启动开销（在输出扫描开始之前消耗的时间， 也就是，在一个 SORT 节点里做排续的时间）．

• 预计的总开销（如果所有的记录都被检索的话， 不过很可能不是这样 --- 比如 LIMIT 将在总开销的一小部分就停止）．

• 预计的这个规划节点输出的行数． （同样，没有考虑 LIMIT）．

• 预计的这个规划节点的行的平均宽度（以字节计算）．

开销是以磁盘页面的存取为单位计算的． （预计的 CPU 处理用一些非常随意的捏造的权值被转换成磁盘页面单位。 如果你想试验这些东西，请参阅在 管理员手册 里的运行时参数列表．）

有一点很重要：那就是一个上层节点的开销包括它的所有子节点的开销。 还有一点也很重要：就是这个开销只反映规划器/优化器关心的东西。 尤其是开销没有把结果记录传递给前端的时间考虑进去 --- 这个时间可能在真正的总时间里面占据相当重要的分量， 但是被规划器忽略了，因为它无法通过修改规划来改变之。 （每个正确的规划都将输出同样的记录集。）

输出的行数有一些小处理，因为它 不是 查询处理/扫描过的行数 --- 通常会少一些， 反映对应用于此节点上的任意 WHERE 子句约束的选择性估计． 通常而言，顶层的行预计会接近于查询实际返回，更新，或删除的行数

下面是几个例子（用的是经过 vacuum analyze 后的蜕变测试数据库以及 接近 7.2 的开发代码）：

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=1003 width=148)
    

预计的输出行数降低了，因为有 WHERE 子句。 不过，扫描仍将必须访问所有 10000 行，因此开销没有降低； 实际上它还增加了一些以反映检查 WHERE 条件的额外 CPU 时间．

这条查询实际选择的行数是 1000，但是预计的数目只是个大概． 如果你试图重复这个试验，那么你很可能得到有些不同的预计； 还有，这个预计会在每次 ANALYZE 命令之后 改变，因为 ANALYZE 生成的统计是从该表中 随机抽取的样本计算的．

把查询修改为限制条件更严格：

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..173.32 rows=47 width=148)
    

这时你会看到，如果我们把 WHERE 条件变得足够有选择性， 规划器将最终决定一次索引扫描将比一次顺序扫描快。 因为有索引，这个规划将只需要访问 50 条记录， 因此尽管每条记录单独的抓取比顺序读取整个磁盘页面的开销大， 它（这个查询规划）还是胜出。

向条件里面增加另外一个条件：

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

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

新增的子句 stringu1 = 'xxx' 减少了预计的输出行， 但是没有减少开销，因为我们仍然需要访问相同的元组集。

让我们试着使用我们上面讨论的数据域连接两个表：

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

Nested Loop  (cost=0.00..269.11 rows=47 width=296)
  ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..173.32 rows=47 width=148)
  ->  Index Scan using tenk2_unique2 on tenk2 t2
               (cost=0.00..2.01 rows=1 width=148)
    

在这个嵌套循环联接里，外层扫描和我们前一个例子是一样的， 因此它的开销和行数是一样的，因为我们对那个节点应用了 "unique1 < 50" WHERE 子句。 "t1.unique2 = t2.unique2" 这时还不相关， 因此它没有影响外层扫描的行计数。 对于内层扫描， 目前的外层扫描元组的 unique2 值被插入到内层索引扫描以生成一个象 "t2.unique2 = constant " 这样的索引条件。这样我们就得到与我们想要的和查询 EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 同样的内层扫描规划和开销。 然后再以外层扫描的开销为基础设置循环节点的开销， 加上一个为每个外层扫描重复的内层扫描（这里是 47 * 2.01）， 再加上一点点处理联接的 CPU 时间。

在这个例子里，循环的输出行数与两个扫描的行数的乘积相同， 但是通常并不是这样的，因为通常你会有提及两个关系的 WHERE 子句， 因此它只能应用于连接（join）点，而不能影响两个关系的输入扫描。 比如，如果我们加一条 "WHERE ... AND t1.hundred < t2.hundred"， 将减少输出行数，但是不改变任何一个输入扫描。

寻找另外一个规划的方法是通过设置每种规划类型的允许/禁止开关， 强制规划器抛弃它认为优秀的（扫描）策略． （这个工具目前比较原始，但很有用． 又见 Section 11.3 ．）

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

Hash Join  (cost=181.22..564.83 rows=49 width=296)
  ->  Seq Scan on tenk2 t2
               (cost=0.00..333.00 rows=10000 width=148)
  ->  Hash  (cost=181.09..181.09 rows=49 width=148)
        ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..181.09 rows=49 width=148)
    

这个规划仍然试图用同样的索引扫描从 tenk1 里面取出感兴趣的 50 行， 把它们藏在一个在内存里的散列（哈希）表里，然后对 tenk2 做一次顺序 扫描，对每一条 tenk2 记录检测上面的散列（哈希）表， 寻找可能匹配"t1.unique2 = t2.unique2" 的记录。 读取 tenk1 和建立散列表是此散列联接的全部启动开销， 因为我们在开始读取 tenk2 之前不可能获得任何输出记录。 这个联接的总的预计时间同样还包括相当重的检测散列（哈希）表 10000 次的 CPU 时间。不过，请注意，我们不需要对 181.09 乘 10000； 散列（哈希）表的在这个规划类型中只需要设置一次。

我们可以用 EXPLAIN ANALYZE 检查规划器的估计值的准确性． 这个命令实际上执行该查询然后显示每个规划节点内实际运行时间的和以及 单纯 EXPLAIN 显示的估计开销．比如，我们可以象下面这样获取一个结果∶

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

Nested Loop  (cost=0.00..330.41 rows=49 width=296) (actual time=1.31..28.90 rows=50 loops=1)
  ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..181.09 rows=49 width=148) (actual time=0.69..8.84 rows=50 loops=1)
  ->  Index Scan using tenk2_unique2 on tenk2 t2
               (cost=0.00..3.01 rows=1 width=148) (actual time=0.28..0.31 rows=1 loops=50)
Total runtime: 30.67 msec

请注意 "actual time" 数值是以真实时间的毫秒计的， 而 "cost" 估计值是以任意磁盘抓取的单元计的； 因此它们很可能不一致．我们要关心的事是两组比值是否一致．

在一些查询规划里，一个子规划节点很可能运行多次．比如，在上面的嵌套 循环的规划里，内层的索引扫描是对每个外层元组执行一次的． 在这种情况下， "loops" 报告该节点执行的总数目， 而显示的实际时间和行数目是每次执行的平均值．这么做的原因是令这些数字 与开销预计显示的数字具有可比性．要乘以 "loops" 值才能 获得在该节点时间花费的总时间．

EXPLAIN ANALYZE 显示的 "total runtime" 包括执行器启动 和关闭的时间，以及花在处理结果元组上的时间．它不包括分析，重写， 或者规划的时间．对于 SELECT 查询，总运行时间通常只是比从顶层规划节点 汇报出来的总时间略微大些．对于 INSERT，UPDATE，和 DELETE 查询， 总运行时间可能会显著增大，因为它包括花费在处理输出元组上的时间． 在这些查询里，顶层规划节点的时间实际上是花在计算新元组和/或定位旧 元组上的时间，但是不包括花在执行改动上的时间．

如果 EXPLAIN 的结果除了在你实际测试的情况之外不能推导出其它的情况， 那它就什么用都没有；比如，在一个小得象玩具的表上的结果不能 适用于大表．规划器的开销计算不是线性的，因此它很可能对大些或者 小些的表选择不同的规划．一个极端的例子是一个只占据一个磁盘页面的表， 在这样的表上，不管它有没有索引可以使用，你几乎都总是得到顺序 扫描规划．规划器知道不管在任何情况下它都要进行一个磁盘页面的读取， 所以再扩大几个磁盘页面读取以查找索引是没有意义的．