14.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规划节点的输入节点里很快停止

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

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

开销是用规划器根据成本参数(参见Section 18.6.2)捏造的单位来衡量的，习惯上以磁盘页面抓取为单位。 也就是seq_page_cost将被按照习惯设为1.0(一次顺序的磁盘页面抓取)， 其它开销参数将参照它来设置。 本节的例子都假定这些参数使用默认值。

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

输出的行数有一些小技巧，因为它不是规划节点处理/扫描过的行数，通常会少一些， 反映对应用于此节点上的任意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次页面读取，每次页面读取将消耗（页面读取数*seq_page_cost）， 加上（扫描的行数*cpu_tuple_cost）。默认，seq_page_cost是1.0，cpu_tuple_cost是0.01， 因此将消耗(358 * 1.0) + (10000 * 0.01) = 458。

现在让我们修改查询并增加一个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: (t2.unique2 = t1.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 18.6.1里描述)， 强制规划器抛弃它认为优秀的(扫描)策略。 这个工具目前比较原始，但很有用。参阅Section 14.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: (t2.unique2 = t1.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 行， 把它们藏在一个内存 Hash 表里，然后对 tenk2做一次顺序扫描， 对每一条 tenk2记录检测上面的 Hash 表， 寻找可能匹配t1.unique2 = t2.unique2的行。 读取 tenk1 和建立 Hash 表是此散列连接的全部启动开销，因为我们在开始读取 tenk2之前不可能获得任何输出行。 这个连接的总预计时间同样还包括相当重的检测 Hash 表 10000 次的 CPU 时间。 不过，请注意，我们不需要对 232.35 乘 10000，因为 Hash 表的在这个规划类型中只需要设置一次。

我们可以用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: (t2.unique2 = t1.unique2)
 Total runtime: 14.452 ms

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

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

EXPLAIN ANALYZE显示的Total runtime包括执行器启动和关闭的时间， 以及花在处理结果行上的时间。它不包括分析、重写、规划的时间。 对于INSERT，UPDATE和DELETE命令， 总运行时间可能会显著增大，因为它包括花费在处理结果行上的时间。 （这个节点之下的计划节点代表定位旧行和/或计算新行）。如果存在， 在触发器之前的执行时间与相关的插入，更新或删除节点有关。在每个触发器（无论是之前还是之后）的时间 会单独的显示，并且是包含在总执行时间中的。然后，需要注意的是，延迟约束触发器在事务结束之前是不回执行的 ，因此不会被EXPLAIN ANALYZE命令显示。

有两个显著的原因使EXPLAIN ANALYZE测试的运行时间偏离运行相同查询时的正常结果。 一个是网络传输和I/O传输的花费，另一个是，EXPLAIN ANALYZE命令本身会增加开销， 特别是机器的gettimeofday()内核调用很慢时。

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