PostgreSQL7.0手册-用户手册-17. 理解性能
2019-09-08 23:33:07
供稿：网友
第十七章. 理解性能
查询性能可能受许多因素的影响．这些因素里面有一些是可以由用户控制的，而其它因素取决于他所用的（数据库）系统．
有一些性能因素，比如索引的创建和海量数据的装载在其他地方讨论．本章将讨论 EXPLAIN 命令，然后将展示一个查询的细节是如何影响查询规划进而影响整体性能的．
使用 EXPLAIN
作者：由 Tom Lane 写做，源于 2000-03-27 的电子邮件．
规划分析是一门值得写一个教程的学问，而我没有足够的时间写呢么一个．这里是我写的一些简短的不完善的解释．
目前被 EXPLAN 引用的数字是：
预计的启动开销（在输出扫描开始之前消耗的时间，比如，在一个 SORT 节点里做排续的时间）．
预计的总开销（如果所有的记录都被检索的话，不过很可能不是这样 --- 比如 LIMIT 将在总开销的一小部分就停止）．
预计的这个规划节点输出的行数．
预计的这个规划节点的行的平均宽度（以字节计算）．
开销是以磁盘页面的存取为单位计算的．（预计的 CPU 处理用一些非常随意的捏造的权值被转换成磁盘页面单位。如果你想试验这些东西，请参阅 SET 的手册页。）有一点很重要：那就是一个上层节点的开销包括它的所有子节点的开销。还有一点也很重要：就是这个开销只反映规划器/优化器关心的东西。尤其是开销没有把结果记录传递给前端的时间考虑进去 --- 这个时间可能在真正的总时间里面占据相当重要的位置，但是被规划器忽略了，因为它无法通过修改规划来改变之。（我们相信，每个正确的规划都将输出同样的记录集。）
输出行有一点小技巧，因为输出的行不是被查询处理/扫描过的行 --- 通常输出行会少一些，反映应用于此节点上的任意 WHERE 子句的选择性计算。
平均宽度是相当虚的东西，因为它实际上对变长度列没有任何认识。我正在考虑将来改进这些东西，但是也可能不值得这样做，因为宽度用的不是很多。
下面是几个例子（用的是经过清理（vacuum）分析后的蜕变测试数据库以及接近完成的 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"，将减少输出行数，但是不会修改任何一个输入扫描。
我们可以看一下我们强制规划器忽视它认为优秀的（扫描）策略（这还是相当原始的工具，但是它是我们目前能够用上的东西）的结果：
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 之前不可能获得任何输出记录。这个联接的总的预计时间同样还包括相当重的检测散列（哈希）表 10000 次的 CPU 时间。不过，请注意，我们不需要对 89.35 乘 10000；散列（哈希）表的在这个规划类型中只需要设置一次。
-----------------------------------------------------------------------------
