Chapter 10. 性能提升技巧
Table of Contents
10.1. 使用 EXPLAIN
10.2. 规划器使用的统计信息
10.3. 用明确的 JOIN （连接）控制规划器
10.4. 向数据库中添加记录
10.4.1. 关闭自动提交
10.4.2. 使用 COPY FROM
10.4.3. 删除索引
10.4.4. 事后运行 ANALYZE
查询的性能可能受多种因素影响． 其中一些因素可以由用户操纵，而其他的则属于下层系统设计的基本问题了． 本章我们提供一些有关理解和调节 PostgreSQL 性能的线索．
10.1. 使用 EXPLAIN
PostgreSQL 为给它的每个 查询产生一个查询规划． 为匹配查询结构和数据属性选择正确的规划对性能绝对有关键性的影响． 你可以使用 EXPLAIN 命令察看系统为每个查询生成的 查询规划是什么．阅读查询规划是一门值得写一个相当长的教程的学问， 而我这份文档可不是这样的教程，但是这里有一些基本的信息．
目前被 EXPLAN 引用的数字是：
*
预计的启动开销（在输出扫描开始之前消耗的时间， 也就是，在一个排序节点里做排续的时间）．
*
预计的总开销（如果所有的行都被检索的话， 不过很可能不是这样 --- 比如 LIMIT 将在总开销的一小部分就停止）．
*
预计的这个规划节点输出的行数． （同样，只执行到完成为止）．
*
预计的这个规划节点的行的平均宽度（以字节计算）．
开销是以磁盘页面的存取为单位计算的． （预计的 CPU 处理用一些非常随意的捏造的权值被转换成磁盘页面单位。 如果你想试验这些东西，请参阅在 PostgreSQL 7.3 管理员手册 里的运行时参数列表．）
有一点很重要：那就是一个上层节点的开销包括它的所有子节点的开销。 还有一点也很重要：就是这个开销只反映规划器/优化器关心的东西。 尤其是开销没有把结果记录传递给前端的时间考虑进去 --- 这个时间可能在真正的总时间里面占据相当重要的分量， 但是被规划器忽略了，因为它无法通过修改规划来改变之。 （我们相信，每个正确的规划都将输出同样的记录集。）
输出的行数有一些小处理，因为它不是 查询处理/扫描过的行数 --- 通常会少一些， 反映对应用于此节点上的任意WHERE子句约束的选择性估计． 通常而言，顶层的行预计会接近于查询实际返回，更新，或删除的行数
下面是几个例子（用的是经过VACUUM ANALYZE后的回归测试数据库以及 7.3 的开发代码）：
regression=# EXPLAIN SELECT * FROM tenk1;
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 查看）。
现在让我们修改查询并增加一个WHERE条件：
regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..358.00 rows=1033 width=148)
Filter: (unique1 < 1000)
预计的输出行数降低了，因为有WHERE子句。 不过，扫描仍将必须访问所有 10000 行，因此开销没有降低； 实际上它还增加了一些以反映检查WHERE条件的额外 CPU 时间．
这条查询实际选择的行数是 1000，但是预计的数目只是个大概． 如果你试图重复这个试验，那么你很可能得到有些不同的预计； 还有，这个预计会在每次 ANALYZE 命令之后 改变，因为 ANALYZE 生成的统计是从该表中 随机抽取的样本计算的．
把查询修改为限制条件更严格：
regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.33 rows=49 width=148)
Index Cond: (unique1 < 50)
这时你会看到，如果我们把WHERE条件变得足够有选择性， 规划器将最终决定一次索引扫描将比一次顺序扫描快。 因为有索引，这个规划将只需要访问 50 条记录， 因此尽管每条记录单独的抓取比顺序读取整个磁盘页面的开销大， 它（这个查询规划）还是胜出。
向条件里面增加另外一个WHERE条件：
regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND
regression-# stringu1 = 'xxx';
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.45 rows=1 width=148)
Index Cond: (unique1 < 50)
Filter: (stringu1 = 'xxx'::name)
新增的子句 stringu1 = 'xxx' 减少了预计的输出行， 但是没有减少开销，因为我们仍然需要访问相同的行。 请注意 stringu1 子句不能当做一个索引条件施用 (因为这个索引只是在 unique1 列上有)．它是当做一个从 索引中检索出的行的过滤器来用的．因此开销实际上略微增加了一些以 反映这个额外的检查．
让我们试着使用我们上面讨论的数据域连接两个表：
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------
Nested Loop (cost=0.00..327.02 rows=49 width=296)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=148)
Index Cond: ("outer".unique2 = t2.unique2)
在这个嵌套循环联接里，外层扫描和我们前一个例子是一样的， 因此它的开销和行数是一样的，因为我们对那个节点应用了 unique1 < 50 的WHERE子句。 t1.unique2 = t2.unique2 这时还不相关， 因此它没有影响外层扫描的行计数。 对于内层扫描， 目前的外层扫描行的unique2值被插入到内层索引扫描以生成一个象 t2.unique2 = constant 这样的索引条件。这样我们就得到与我们想要的和查询 EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 同样的内层扫描规划和开销。 然后再以外层扫描的开销为基础设置循环节点的开销， 加上一个为每个外层行扫描重复的内层扫描（这里是 49 * 3.01）， 再加上一点点处理联接的 CPU 时间。
在这个例子里，循环的输出行数与两个扫描的行数的乘积相同， 但是通常并不是这样的，因为通常你会有提及两个关系的WHERE子句， 因此它只能应用于连接（join）点，而不能影响两个关系的输入扫描。 比如，如果我们加一条 WHERE ... AND t1.hundred < t2.hundred， 将减少输出行数，但是不改变任何一个输入扫描。
寻找另外一个规划的方法是通过设置每种规划类型的允许/禁止开关， 强制规划器抛弃它认为优秀的（扫描）策略． （这个工具目前比较原始，但很有用． 又见Section 10.3．）
regression=# SET enable_nestloop = off;
SET
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=179.45..563.06 rows=49 width=296)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..333.00 rows=10000 width=148)
-> Hash (cost=179.33..179.33 rows=49 width=148)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
Index Cond: (unique1 < 50)
这个规划仍然试图用同样的索引扫描从tenk1 里面取出感兴趣的 50 行， 把它们藏在一个在内存里的散列（哈希）表里，然后对tenk2 做一次顺序 扫描，对每一条tenk2记录检测上面的散列（哈希）表， 寻找可能匹配t1.unique2 = t2.unique2 的行。 读取tenk1和建立散列表是此散列联接的全部启动开销， 因为我们在开始读取tenk2 之前不可能获得任何输出行。 这个联接的总的预计时间同样还包括相当重的检测散列（哈希）表 10000 次的 CPU 时间。不过，请注意，我们不需要对 179.33 乘 10000； 散列（哈希）表的在这个规划类型中只需要设置一次。
我们可以用EXPLAIN ANALYZE检查规划器的估计值的准确性． 这个命令实际上执行该查询然后显示每个规划节点内实际运行时间的和以及 单纯EXPLAIN显示的估计开销．比如，我们可以象下面这样获取一个结果∶
regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.00..327.02 rows=49 width=296)
(actual time=1.18..29.82 rows=50 loops=1)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
(actual time=0.63..8.91 rows=50 loops=1)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=148)
(actual time=0.29..0.32 rows=1 loops=50)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 31.60 msec
请注意 "actual time" 数值是以真实时间的毫秒计的， 而 "cost" 估计值是以任意磁盘抓取的单元计的； 因此它们很可能不一致．我们要关心的事是两组比值是否一致．
在一些查询规划里，一个子规划节点很可能运行多次．比如，在上面的嵌套 循环的规划里，内层的索引扫描是对每个外层行执行一次的． 在这种情况下，"loops" 报告该节点执行的总数目， 而显示的实际时间和行数目是每次执行的平均值．这么做的原因是令这些数字 与开销预计显示的数字具有可比性．要乘以 "loops" 值才能 获得在该节点时间花费的总时间．
EXPLAIN ANALYZE 显示的 "Total runtime" 包括执行器启动 和关闭的时间，以及花在处理结果行上的时间．它不包括分析，重写， 或者规划的时间．对于SELECT查询，总运行时间通常只是比从顶层规划节点 汇报出来的总时间略微大些．对于INSERT，UPDATE，和 DELETE 查询， 总运行时间可能会显著增大，因为它包括花费在处理结果行上的时间． 在这些查询里，顶层规划节点的时间实际上是花在计算新行和/或定位旧 行上的时间，但是不包括花在执行改动上的时间．
如果EXPLAIN的结果除了在你实际测试的情况之外不能推导出其它的情况， 那它就什么用都没有；比如，在一个小得象玩具的表上的结果不能 适用于大表．规划器的开销计算不是线性的，因此它很可能对大些或者 小些的表选择不同的规划．一个极端的例子是一个只占据一个磁盘页面的表， 在这样的表上，不管它有没有索引可以使用，你几乎都总是得到顺序 扫描规划．规划器知道不管在任何情况下它都要进行一个磁盘页面的读取， 所以再扩大几个磁盘页面读取以查找索引是没有意义的．
