13.1. 使用 EXPLAIN

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

查询规划的结构是一个规划节点的树。 最底层的节点是表扫描节点：它们从表中返回原始数据行。 不同的表访问模式有不同的扫描节点类型：顺序扫描，索引扫描，以及位图索引扫描。 如果查询需要连接，聚集，排序，或者是对原始行的其它操作， 那么就会在扫描节点"之上"有其它额外的节点。 并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。 EXPLAIN 的输出给规划树里面的每个节点都有一行输出， 显示基本的节点类型和规划器为执行这个规划节点计算出来的预计的开销值。 第一行（最上层的节点）是对该规划的总的执行开销的预计；这个数值就是规划器试图最小化的数值。

这里是一个简单的例子，只是用来显示输出会有些啥： [1]

EXPLAIN SELECT * FROM tenk1;

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

EXPLAIN 引用的数据是：

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

• 预计的总开销（如果所有的行都被检索的话， 不过很可能不是这样：比如带有 LIMIT 子句的查询将会在 Limit 规划节点的输入节点里很快停止。）。

• 预计的这个规划节点输出的行数。 （同样，只执行到完成为止）。

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

开销是以磁盘页面的存取为单位计算的。也就是，定义上 1.0 等于一次顺序的磁盘页面抓取。 （同时也计算了 CPU 的开销；它们被用一些非常随意的捏造的权值被转换成磁盘页面单位。 如果你想试验这些东西，请参阅在 Section 17.6.2 里的运行时参数列表。）

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

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

回到我们的例子：

EXPLAIN SELECT * FROM tenk1;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

这个例子就象例子本身一样直接了当。如果你做一个

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

你会发现 tenk1 有 358 磁盘页面和 10000 行。 因此开销计算为 358 次页面读取，定义为每块 1.0， 加上 10000 * cpu_tuple_cost，通常是 0.01（用命令 SHOW cpu_tuple_cost 查看）。

现在让我们修改查询并增加一个WHERE条件：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7033 width=244)
   Filter: (unique1 < 7000)

请注意 EXPLAIN 输出显示 WHERE 子句当作一个 "filter" 应用； 这意味着规划节点为它扫描的每一行检查该条件，并且只输出通过条件的行。 预计的输出行数降低了，因为有WHERE子句。 不过，扫描仍将必须访问所有 10000 行，因此开销没有降低； 实际上它还增加了一些以反映检查WHERE条件的额外 CPU 时间。

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

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

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
         Index Cond: (unique1 < 100)

这里，规划器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置， 然后上层规划节点真实地从表里面抓取出那些行。独立地抓取数据行比顺序地读取它们的开销高很多， 但是因为并非所有表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。 （使用两层规划的原因是因为上层规划节点把索引标识出来的行位置在读取它们之前按照物理位置排序， 这样可以最小化独立抓取的开销。节点名称里面提到的"位图（bitmap）"是进行排序的机制。

如果 WHERE 条件有足够的选择性，规划器可能会切换到一个"简单的"索引扫描规划：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.00 rows=2 width=244)
   Index Cond: (unique1 < 3)

在这个例子理，表的数据行是以索引顺序抓取的，这样就令读取它们的开销更大， 但是这里的行少得可怜，因此额外的行位置的排序并不值得。你最常见的就是看到这种规划类型只抓取一行， 以及是那些要求一个 ORDER BY 条件匹配索引顺序的查询。

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

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';

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

新增的条件 stringu1 = 'xxx' 减少了预计的输出行， 但是没有减少开销，因为我们仍然需要访问相同的行。 请注意 stringu1 子句不能当做一个索引条件施用 （因为这个索引只是在 unique1 列上有）。 它是当做一个从索引中检索出的行的过滤器来用的。 因此开销实际上略微增加了一些以反映这个额外的检查。

如果在 WHERE 里面使用的好几个字段上有索引， 那么规划器可能会使用索引的 AND 或者 OR 的组合：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
 
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=11.27..49.11 rows=11 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=11.27..11.27 rows=11 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..8.65 rows=1042 width=0)
               Index Cond: (unique2 > 9000)

但是这么做要求访问两个索引，因此与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。 如果你改变涉及的范围，你会看到规划器相应地发生变化。

让我们试着使用我们上面讨论的字段连接两个表：

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
  
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=2.37..553.11 rows=106 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
         Recheck Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
               Index Cond: (unique1 < 100)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)
         Index Cond: ("outer".unique2 = t2.unique2)

在这个嵌套循环里，外层的扫描是和我们前面看到的同样的位图索引， 因此其开销和行计数是一样的，因为我们在该节点上附加了 WHERE 子句 unique1 < 100。 这个时候 t1.unique2 = t2.unique2 子句还没有什么关系， 因此它不影响外层扫描的行计数。对于内层扫描，当前外层扫描的数据行的 unique2 被插入内层索引扫描生成类似 t2.unique2 = constant 这样的索引条件。因此，我们拿到和从 EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 那边拿到的一样的内层扫描计划和开销。然后，以外层扫描的开销为基础设置循环节点的开销， 加上每个外层行的一个重复（这里是 106 * 3.01），然后再加上连接处理需要的一点点 CPU 时间。

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

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

SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
  
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=232.61..741.67 rows=106 width=488)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 width=244)
   ->  Hash  (cost=232.35..232.35 rows=106 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                     Index Cond: (unique1 < 100)

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

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

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
  
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
               Index Cond: (unique1 < 100)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 14.452 ms

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

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

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

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