Chapter 12. 并发控制

Table of Contents
12.1. 介绍
12.2. 事务隔离

    12.2.1. 读已提交隔离级别
    12.2.2. 可串行化隔离级别

12.3. 明确锁定

    12.3.1. 表级锁
    12.3.2. 行级锁
    12.3.3. 死锁

12.4. 应用层的数据完整性检查
12.5. 锁和索引

本章描述 PostgreSQL 数据库系统在两个或者更多会话试图同时访问同样的数据的时候表现出来的样子。 在那种并发访问的情况下的目标是为所有会话提供高效的访问，同时还要维护严格的数据完整性。 每个数据库应用的开发人员都应该熟悉本章讨论的话题。
12.1. 介绍

与其他使用锁来进行并行控制的数据库系统不同， PostgreSQL 利用多版本模型来维护数据的一致性。 （多版本并发控制，Multiversion Concurrency Control, MVCC）。 这就意味着当检索数据库时，每个事务都看到一个数据的一段时间前的快照 （ 一个数据库版本）， 而不管正在处理的数据当前的状态。这样，如果对每个数据库会话进行事务隔离， 就可以避免一个事务看到因为其它并行的事务更新同一行数据而导致的不连贯的数据。

使用MVCC多版本并发控制比锁定模型的主要优点是在MVCC里， 对检索（读）数据的锁要求与写数据的锁要求不冲突， 所以读不会阻塞写，而写也从不阻塞读。

在 PostgreSQL 里也有表和行级别的锁定设施， 用于给那些无法轻松接受 MVCC 行为的应用。 不过，恰当地使用 MVCC 总会提供比锁更好地性能。

12.2. 事务隔离

SQL 标准用三个必须在并行的事务之间避免的现象定义了四个级别的事务隔离。 这些不希望发生的现象是：

脏读（dirty reads）

    一个事务读取了另一个未提交的并行事务写的数据。
不可重复读（non-repeatable reads）

    一个事务重新读取前面读取过的数据， 发现该数据已经被另一个已提交的事务修改过。
幻读（phantom read）

    一个事务重新执行一个查询，返回一套符合查询条件的行， 发现这些行因为其他最近提交的事务而发生了改变。

这四种隔离级别和对应的行为在Table 12-1 里描述。

Table 12-1. SQL 事务隔离级别
隔离级别         脏读（Dirty Read）         不可重复读（NonRepeatable Read）         幻读（Phantom Read）
读未提交（Read uncommitted）         可能         可能         可能
读已提交（Read committed）         不可能         可能         可能
可重复读（Repeatable read）         不可能         不可能         可能
可串行化（Serializable ）         不可能         不可能         不可能

在 PostgreSQL 里，你可以请求四种可能的事务隔离级别中的任意一种。 但是在内部，实际上只有两种独立的隔离级别，分别对应读已提交和可串行化。 如果你选择了读未提交的级别，实际上你用的是读已提交， 在你选择可重复的读级别的时候，实际上你用的是可串行化，所以实际的隔离级别可能比你选择的更严格。 这是 SQL 标准允许的：四种隔离级别只定义了哪种现象不能发生，但是没有定义那种现象一定发生。 PostgreSQL 只提供两种隔离级别的原因是， 这是把标准的隔离级别与多版本并发控制架构映射相关的唯一的合理方法。 可用的隔离级别的行为在下面小节里描述。
12.2.1. 读已提交隔离级别

读已提交（Read Committed） 是 PostgreSQL 里的缺省隔离级别。 当一个事务运行在这个隔离级别时， 一个 SELECT 查询只能看到查询开始之前提交的数据而永远无法看到未提交的数据或者是在查询执行时其他并行的事务提交做的改变。 （不过 SELECT 的确看得见同一次事务中前面更新的结果。即使它们还没提交也看得到。） 实际上，一个 SELECT 查询看到一个在该查询开始运行的瞬间该数据库的一个快照。 请注意两个相邻的 SELECT 命令可能看到不同的数据，哪怕它们是在同一个事务里， 因为其它事务会在第一个SELECT执行的时候提交．

UPDATE， DELETE， 或者 SELECT FOR UPDATE 在搜索目标行的时候的行为和SELECT 一样： 它们只能找到在命令开始的时候已经提交的行。 不过，这样的目标行在被找到的时候可能已经被其它并发的事务更新（或者删除，或者标记为更新的）。 在这种情况下，即将进行的更新将等待第一个更新事务提交或者回滚（如果它还在处理）。 如果第一个更新回滚，那么它的作用将被忽略，而第二个更新者将继续更新最初发现的行。 如果第一个更新者提交，那么如果第一个更新者删除了该行，则第二个更新者将忽略该行， 否则它将试图在该行的已更新的版本上施加它的操作。系统将重新计算命令搜索条件（WHERE 子句）， 看看该行已更新的办不那是否仍然符合搜索条件。如果是，则第二个更新继续其操作，从该行的已更新版本开始。

因为上面的规则，正在更新的命令可能会看到不一致的快照 — 它们可以看到影响它们试图更新的并发更新命令的效果， 但是它们看不到那些命令对数据库里其它行的作用。 这样的行为令读已提交模式不适合用于哪种涉及复杂搜索条件的命令。 不过，它对于简单的情况而言是正确的。比如，假设我们用类似下面这样的命令更新银行余额：

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

如果两个并发事务试图修改帐号 12345 的余额，那我们很明显希望第二个事务是从帐户行的已经更新过的版本上进行更新。 因为每个命令只是影响一个已经决定了的行，因此让它看到更新后的版本不会导致任何不一致的问题。

因为在读已提交模式里，每个新的命令都是从一个新的快照开始的，而这个快照包含所有到该时刻为止已经提交的事务， 因此同一个事务里的后面的命令将看到任何已提交的并发事务的效果。 这里要考虑的问题是我们在一个命令里是否看到数据库里绝对一致的视图。

读已提交模式提供的部分事务隔离对于许多应用而言是足够的，并且这个模式速度快，使用简单。 不过，对于做复杂查询和更新的应用，可能需要保证数据库有比读已提交模式提供的更加严格的一致性视图。
12.2.2. 可串行化隔离级别

可串行化（Serializable） 级别提供最严格的事务隔离。 这个级别模拟串行的事务执行， 就好象事务将被一个接着一个那样串行的，而不是并行的执行。 不过，使用这个级别的应用必须准备在串行化失败的时候重新发动事务．

当一个事务处于可串行化级别， 一个 SELECT 查询只能看到在事务开始之前提交的数据而永远看不到未提交的数据或事务执行中其他并行事务提交的修改。 （不过，SELECT 的确看得到同一次事务中前面的更新的效果。即使事务还没有提交也一样。） 这个行为和读已提交级别是不太一样，它的 SELECT 看到的是该事务开始时的快照，而不是该事务内部当前查询开始时的快照。 这样，一个事务内部后面的 SELECT 命令总是看到同样的数据。

UPDATE， DELETE，和 SELECT FOR UPDATE 在搜索目标行上的行为和 SELECT 一样： 它们将只寻找在事务开始的时候已经提交的目标行。但是， 这样的目标行在被发现的时候可能已经被另外一个并发的事务更新了（或者是删除或者是标记为更新）。 在这种情况下，可串行化的事务将等待第一个正在更新的事务提交或者回滚（如果它仍然在处理中）。 如果第一个更新者回滚，那么它的影响将被忽略， 而这个可串行化的就可以继续更新它最初发现的行。 但是如果第一个更新者提交了（并且实际上更新或者删除了该行，而不只是为更新选中它）那么可串行化事务将回滚，并返回下面信息

ERROR:  Can't serialize access due to concurrent update

因为一个可串行化的事务在可串行化事务开始之后不能更改被其他事务更改过的行。

当应用收到这样的错误信息时，它应该退出当前的事务然后从头开始重新进行整个事务。 第二次运行时，该事务看到的前一次提交的修改是该数据库初始的样子中的一部分， 所以把新版本的行作为新事务更新的起点不会有逻辑冲突。

请注意只有更新事务才需要重试，只读事务从来没有串行化冲突．

可串行化事务级别提供了严格的保证：每个事务都看到一个完全一致的数据库的视图。 不过，如果并行更新令数据库不能维持串行执行的样子，那么应用必须准备重试事务。 因为重做复杂的事务的开销可能是非常可观的，所以我们只建议在更新命令中包含足够复杂的逻辑， 在读已提交级别中可能导致错误的结果的情况下才使用。 最常见的是，可串行化模式只是在这样的情况下是必要的：一个事务连续做若干个命令， 而这几个命令必须看到数据库完全一样的视图。
12.2.2.1. 可串行化隔离与真正的可串行化之比较

执行的"可串行化"的直观含义（以及数学定义）是两个成功提交的并发事务将显得好像严格地串行执行一样， 一个跟着一个 — 尽管我们可能无法预期哪个首先执行。我们必须明白，禁止那些在 Table 12-1 里面列出的行为并不能保证真正的可串行化， 并且，实际上 PostgreSQL 的可串行化模式并不保证在这种含义下的可串行化。 举例来说，假设一个表 mytab，最初包含

class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

假设可串行化事务 A 计算

SELECT SUM(value) FROM mytab WHERE class = 1;

然后把结果（30）作为 value 到表中，class = 2。 同时，一个并发的可串行化的事务 B 进行下面计算

SELECT SUM(value) FROM mytab WHERE class = 2;

并且获取结果 300，然后它插入一行新行，class = 1。 然后两个事务都提交。所有列出的禁止行为都不会发生，但是我们拿到的结果是不可能在任何一种串行执行下看到的。 如果 A 在 B 之前执行，B 应该计算出总和 330，而不是 300，如果是另外一种顺序，那么 A 计算出的综合也会不同。

为了保证真正数学上的可串行化，一个数据库系统必须强制谓词锁定， 这就意味着一个事务不能插入或者更改这样的数据行：这个数据行的数据匹配另外一个并发事务的 WHERE 条件。 比如，一旦事务 A 执行了查询 SELECT ... WHERE class = 1，那么一个谓词锁定系统将禁止事务 B 插入任何 class 为 1 的新行，直到 A 提交。 [1] 这样的锁系统实现起来非常复杂，并且执行起来代价高昂， 因为每个会话都必须要知道每个并发事务的每个查询的执行细节。 并且这样大量的开销在大部分情况下都是浪费掉的， 因为在实际情况下大部分应用都不做会导致问题的这种事情。 （当然，上面的例子是静心设计的，不能代表真实的软件。） 因此，PostgreSQL 并未实现谓词锁定， 而就我们所知，没有其它的生产中的 DBMS 实现了这个。

在那些非串行化执行真的可能有危险的场合，可以通过使用明确的锁定来避免问题的法上。 更多的讨论在下面的小节进行。
Notes
[1]       

实际上，一个谓词锁定系统避免了幻读，方法是约束写入的东西，而 MVCC 避免幻读的方法是约束它读取的东西。

12.3. 明确锁定

PostgreSQL 提供了各种各样的锁模式用于控制对表中的数据的并发访问。 这些模式可以用于在 MVCC 无法给出期望的行为的时候用于应用控制的锁定。 同样，大多数 PostgreSQL 命令自动施加恰当的锁以保证被引用的表在命令执行的时候不会以一种不兼容的方式被删除或者修改。 （比如，在存在其它并发操作的时候，ALTER TABLE 是不能在同一个表上面执行的。）

要检查一列当前数据库服务器里正在持有的锁，我们可以使用系统视图 pg_locks （Section 41.33）。有关监控锁管理器子系统的状态的更多信息，请参考 Chapter 23。
12.3.1. 表级锁

下面的列表显示了可用的锁模式和它们被 PostgreSQL 自动使用的环境。 你也可以用命令 LOCK 明确获取这些锁。 请注意所有这些锁模式都是表级锁，即使它们的名字包含单词 "row"；这些锁模式的名称是历史造成的。 从某种角度而言，这些名字反应了每种锁模式的典型用法 — 但是语意都是一样的。 两种锁模式之间真正的区别是它们有着不同的冲突锁集合。 两个事务在同一时刻不能在同一个表上持有相互冲突的锁。 （不过，一个事务决不会和自身冲突。比如，它可以在一个表上请求 ACCESS EXCLUSIVE 然后稍后的时候请求 ACCESS SHARE。） 非冲突锁模式可以由许多事务并发地持有。 请特别注意有些锁模式是自冲突的（比如，在任意时刻 ACCESS EXCLUSIVE 模式就不能够被多个事务拥有） 而其它地都不是自冲突的（比如，ACCESS SHARE 可以被多个事务持有）。 一旦请求到了某种锁，那么该锁模式将持续到事务结束。

表级锁模式

ACCESS SHARE

    只与 ACCESS EXCLUSIVE 冲突。

    SELECT 和 ANALYZE 命令在被引用的表上请求一个这种锁。 通常，任何只读取表而不修改它的命令都请求这种锁模式。
ROW SHARE

    与EXCLUSIVE和ACCESS EXCLUSIVE模式冲突。

    SELECT FOR UPDATE 命令在目标表上需要一个这样模式的锁（加上在所有被引用但没有 FOR UPDATE 的表上的 ACCESS SHARE 锁）。
ROW EXCLUSIVE

    与 SHARE，SHARE ROW EXCLUSIVE， EXCLUSIVE 和 ACCESS EXCLUSIVE 模式冲突。

    命令 UPDATE，DELETE， 和 INSERT 自动请求这个锁模式。 （加上所有其它被引用的表上的 ACCESS SHARE 锁）。 通常，这种锁将被任何修改表中数据的查询请求。
SHARE UPDATE EXCLUSIVE

    和 SHARE UPDATE EXCLUSIVE，SHARE， SHARE ROW EXCLUSIVE，EXCLUSIVE， 和 ACCESS EXCLUSIVE 模式冲突。 这个模式保护一个表不被并发模式改变和 VACUUM。

    VACUUM（不带 FULL 选项）请求这样的锁。
SHARE

    与 ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE， SHARE ROW EXCLUSIVE，EXCLUSIVE 和 ACCESS EXCLUSIVE 模式冲突。 这个模式避免表的并发数据修改。

    CREATE INDEX 语句要求这样的锁模式。
SHARE ROW EXCLUSIVE

    与 ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE，SHARE， SHARE ROW EXCLUSIVE，EXCLUSIVE， 和 ACCESS EXCLUSIVE 模式冲突。

    任何 PostgreSQL 命令都不会自动请求这样的锁模式。
EXCLUSIVE LOCK

    与 ROW SHARE，ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE， SHARE，SHARE ROW EXCLUSIVE， EXCLUSIVE 和 ACCESS EXCLUSIVE 模式冲突。 这个模式只允许并发 ACCESS SHARE 锁，也就是说， 只有对表的读动作可以和持有这个锁模式的事务并行执行。

    任何 PostgreSQL 命令都不会自动请求这样的锁模式．
ACCESS EXCLUSIVE

    与所有模式冲突。 （ ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 和 ACCESS EXCLUSIVE). 这个模式保证其所有者（事务）是可以用任意方式访问该表的唯一事务。

    ALTER TABLE， DROP TABLE，REINDEX，CLUSTER和 VACUUM FULL 命令要求这样的锁。 在 LOCK TABLE 命令没有明确声明需要的锁模式时，它也是缺省锁模式。

    提示: 只有 ACCESS EXCLUSIVE 阻塞 SELECT （没有 FOR UPDATE语句）。

12.3.2. 行级锁

除了表级锁以外，还有行级锁。 特定行上的行级锁是在行被更新的时候自动请求的（或者被删除时或标记为更新）。 锁一直保持到事务提交或者回滚。 行级锁不影响对数据的查询； 它们只阻塞对同一行的写入。 要在不修改某行的前提下请求在该行的行级锁，用 SELECT FOR UPDATE 选取该行。请注意一旦我们请求了特定的行级锁， 那么该事务就可以多次对该行进行更新而不用担心冲突。

PostgreSQL 不会在内存里保存任何关于已修改行的信息， 因此对一次锁定的行数没有限制。 不过，锁住一行会导致一次磁盘写；因此，象 SELECT FOR UPDATE 将修改选中的行以标记它们， 因此会导致磁盘写。

除了表级别的和行级别的锁以外， 页面级别的共享/排他销也用于控制对共享缓冲池中表页面的读/写访问。 这些锁在抓取或者更新一行后马上被释放。 应用程序员通常不需要关心页级锁，我们在这里提到它们只是为了完整。
12.3.3. 死锁

明确锁定的使用可能会增加死锁的可能性， 死锁是是指两个（或多个）事务相互持有对方期待的锁。比如， 如果事务 1 在表 A上持有一个排他锁， 同时试图请求一个在表 B 上的排他锁， 而事务 2 已经持有表B的排他锁，而却正在请求在表 A上的一个排他锁，那么两个事务就都不能执行。 PostgreSQL 自动侦测到死锁条件并且会通过退出一个当事的事务来解决这个问题， 以此来允许其它事务完成。（具体哪个事务会被退出是很难预计的，而且也不应该依靠这样的预计。）

要注意的是死锁也可能会因为行级锁而发生（因此，即使是没有使用明确的锁定，也可能发生）。 考虑这样一种情况，两个并发事务在修改一个表。第一个事务执行了：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

这样就在指定帐号的行上请求了一个行级锁。然后，第二个事务执行：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

第一个 UPDATE 语句成功地在指定行上请求到了一个行级锁，因此它成功更新了该行。 但是第二个 UPDATE 语句发现它试图更新地行已经被锁住了， 因此它等待持有该锁的事务结束。事务二现在就在等待事务一结束，然后再继续执行。 现在，事务一执行：

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

事务一企图在指定行上请求一个行级锁，但是它得不到：事务二已经持有这样的锁了。 所以它等待事务二完成。因此，事务一在事务二上阻塞住了，而事务二在事务一上阻塞住了：这就是一个死锁条件。 PostgreSQL 将侦测这样的条件并退出其中一个事务。

防止死锁的最好方法通常是保证所有使用一个数据库的应用都以一致的顺序在多个对象上请求锁定。 在上面的例子里，如果两个事务以同样的顺序更新那些行，那么就不会发生死锁。 我们也要保证在一个对象上请求的第一个锁是该对象需要的最高的锁模式。 如果我们无法提前核实这些问题，那么我们可以通过在现场重新尝试因死锁而退出的事务的方法来处理。

只要没有检测到死锁条件，一个等待表级锁或者行级锁的事务将等待冲突锁的释放不确定的时间。 这就意味着一个应用持有打开的事务时间太长可不是什么好事情（比如锁，等待用户输入）。

12.4. 应用层的数据完整性检查

因为不管哪种隔离级别，对 PostgreSQL 的读动作不会锁定数据， 一个事务读取的数据可能被另一个事务覆盖。换句话说，如果一条 SELECT 返回了一行， 这并不意味着在返回该行时该行还存在（也就是说在语句完成或事务开始后的某时） 该行可能已经被一个在此事务开始之后提交的事务更新或者删除。 即使该行"现在"仍然有效，那它也可能在当前事务提交或者回滚之前被改变或者删除。

另外一个认识它的方法是每个事务都看到一个数据库内容的快照， 而并行执行的事物很可能看到不同的快照。 因此不管怎样，整个"现在"的概念都是定义不清的。 不过如果客户端应用相互隔离，那么这就不是个大问题， 但是如果客户端之间在数据库外部相互之间通过通道通讯，那就可能有严重的歧义。

要保证一行的实际存在和避免其被并行更新，我们必须使用 SELECT FOR UPDATE 或者或者合适的 LOCK TABLE 语句。 （SELECT FOR UPDATE 只是对其它的并行更新锁住返回的行，而 LOCK TABLE 保护整个表。） 当从其他环境向 PostgreSQL 里用可串行化模式移植应用时一定要把这些问题考虑进去。 （在版本 6.5 前，PostgreSQL 使用读动作锁，因而当从以前的 PostgreSQL 版本向6.5（或更高版本）升级时也要考虑这些问题。）

在 MVCC 环境下，全局有效性检查需要一些额外的考虑。 比如，一个银行应用可能会希望检查一个表重的所有扣款总和等于另外一个表中的加款总和， 同时两个表还会被活跃地更新。在读已提交模式下比较两个连续的 SELECT sum(...) 命令的结果是不可靠的， 因为第二个查询很可能会包含第一个没计算的事务提交的结果。 在一个可串行化的事务里进行两个求和则给出在可串行化事务开始之前提交的所有事务产生的精确的结果 — 但我们还是会合理地置疑在结果提交的时候，它们是否还相关。 如果可串行化事务本身在试图做一致性检查之前进行了某些变更， 那么检查的有用性就更加值得讨论了，因为现在它包含了一些，但不是全部，事务开始后的变化。 在这种情况下，一个仔细的人会希望锁住所有需要检查的表， 这样才能获得一个无可置疑的当前现状的图象。 一个 SHARE 模式（或者更高级）的锁保证在被锁定表中除了当前事务之外，没有未提交的更新。

还要注意如果我们依赖明确锁定来避免并发更新，那么我们应该使用读已提交模式， 或者是在可串行化模式里在执行命令之前小心地获取锁。 在可串行化事务里的获取的锁保证了不会有其它正在运行的修改该表的事务存在， 但是如果事务看到的快照提前获取了锁，那么它可能提前把一些现在已经提交的改变放到表中。 一个可串行化事务的快照实际上是在它的第一个查询或者数据修改命令开始的时候冻结的（SELECT， INSERT，UPDATE，或 DELETE）， 因此我们可能在快照冻结之前明确获取锁。

12.5. 锁和索引

尽管 PostgreSQL 提供对表数据访问的非阻塞的读/写，但并非所有 PostgreSQL 里实现的索引访问模式都能够进行非阻塞读/写。 不同的索引类型按照下面方法操作∶

B-tree 索引

    短期的共享/排他的页面级的销用于读/写访问。 销在索引行被插入/抓取后立即释放。 B-tree 索引提供了无死锁条件的最高级的并行性。
GiST 和 R-Tree 索引

    共享/排他的页面级锁用于读/写访问。锁在命令处理完成后释放。
Hash （散列）索引

    共享/排他的散列桶级锁用于读/写访问。锁在整个散列桶处理完成后释放。 散列桶级锁比索引级的锁提供了更好的并行性但是可能产生死锁，因为锁持有的时间比一次索引操作时间长。

简单说，B-tree 索引是我们推荐的用于并行应用的索引类型； 因为它们还有比散列索引更多的特性，在那些需要对标量数据进行索引的应用中，我们建议使用这种索引类型。 在处理非标量类型数据的时候，显然不能使用 B-tree；在这种环境下， 应用开发人员应该清醒意识到 GiST 和 R-tree 索引的相对弱的并发性能。

Chapter 13. 性能提升技巧

Table of Contents
13.1. 使用 EXPLAIN
13.2. 规划器使用的统计信息
13.3. 用明确的 JOIN （连接）控制规划器
13.4. 向数据库中添加记录

    13.4.1. 关闭自动提交
    13.4.2. 使用 COPY
    13.4.3. 删除索引
    13.4.4. 增大 maintenance_work_mem
    13.4.5. 增大 checkpoint_segments
    13.4.6. 事后运行ANALYZE

查询的性能可能受多种因素影响。 其中一些因素可以由用户操纵，而其他的则属于下层系统设计的基本问题了。 本章我们提供一些有关理解和调节 PostgreSQL 性能的线索。
13.1. 使用 EXPLAIN

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

目前被 EXPLAN 引用的数字是：

    *

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

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

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

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

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

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

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

下面是几个例子（用的是经过 VACUUM ANALYZE 后的回归测试数据库以及 7.3 的开发代码）：

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条件：

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 生成的统计是从该表中随机抽取的样本计算的。

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

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子句里面增加另外一个条件：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND 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 列上有）。 它是当做一个从索引中检索出的行的过滤器来用的。 因此开销实际上略微增加了一些以反映这个额外的检查。

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

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 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)

在这个嵌套循环联接里，外层扫描和我们前一个例子是一样的， 因此它的开销和行数是一样的，因为我们对那个节点应用了WHERE子句 unique1 < 50。 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 13.3。）

SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 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显示的估计开销。 比如，我们可以象下面这样获取一个结果：

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

13.2. 规划器使用的统计信息

就象我们在上一节里展示的那样，查询规划器需要估计一个查询检索的行的数目，这样才能选择正确的查询规划。 本节就系统用于这些估计的统计进行一些描述。

统计的一个部分就是每个表和索引中的记录总数，以及每个表和索引占据的磁盘块数。 这个信息保存在 pg_class 的 reltuples 和 relpages 字段中。我们可以用类似下面的查询检索这些信息：

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';

    relname    | relkind | reltuples | relpages
---------------+---------+-----------+----------
tenk1         | r       |     10000 |      233
tenk1_hundred | i       |     10000 |       30
tenk1_unique1 | i       |     10000 |       30
tenk1_unique2 | i       |     10000 |       30
(4 rows)

我们在这里可以看到 tenk1 有 10000 行， 它的索引也有这么多行，但是索引远比表小得多（很正常）。

出于效率考虑，reltuples 和 relpages 不是实时更新的， 因此它们通常包含可能有些过时的数值。 它们被 VACUUM，ANALYZE，和几个 DDL 命令，比如 CREATE INDEX 更新。一个独立的 ANALYZE， 也就是没有和 VACUUM 在一起的， 生成一个reltuples 的近似数值， 因为它并没有读取表里的每一行。规划器将把 pg_class 表里面的数值调整为和当前的物理表尺寸匹配，以此获取一个更接近的近似值。

大多数查询只是检索表中行的一部分，因为它们有限制待查行的 WHERE 子句。 因此规划器需要对WHERE子句的选择性(selectivity)进行评估， 选择性也就是符合WHERE子句中每个条件的部分。 用于这个目的的信息存储在 pg_statistic 系统表中。 在 pg_statistic 中的记录是由 ANALYZE 和 VACUUM ANALYZE 命令更新的， 并且总是近似值，即使刚刚更新完也不例外。

除了直接查看 pg_statistic 之外， 我们手工检查统计的时候最好查看它的视图 pg_stats。 pg_stats 设计成更具可读性。 而且，pg_stats 是所有人都可以读取的， 而 pg_statistic 只能由超级用户读取。 （这样就可以避免非特权用户从统计信息中获取一些和其他人的表内容相关的信息。 pg_stats 视图是受约束的，只显示当前用户可读的表。） 比如，我们可以∶

SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'road';

attname | n_distinct |                                                                                                                                                                                 most_common_vals                                                                                                                              

---------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name    |  -0.467008 | {"I- 580                        Ramp","I- 880                        Ramp","Sp Railroad                       ","I- 580                            ","I- 680                        Ramp","I- 80                         Ramp","14th                          St  ","5th                           St  ","Mission                       Blvd","I- 880                            "}
thepath |         20 | {"[(-122.089,37.71),(-122.0886,37.711)]"}
(2 rows)

pg_stats 在 Section 41.36 里详细描述。

在 pg_statistic 李存储的信息的数量，特别是 给每个字段用的 most_common_vals 和 histogram_bounds 数组上的最大记录数目可以用 ALTER TABLE SET STATISTICS 命令设置， 或者是用运行时参数 default_statistics_target 进行全局设置。 目前缺省的限制是 10 个记录。 提升该限制应该可以做出更准确的规划器估计，特别是对那些有不规则数据分布的字段而言， 付出的代价是在 pg_statistic 里使用了更多空间，并且需要略微多一些的时间计算估计数值。 相比之下，比较低的限制可能更适合那些数据分布比较简单的字段。

13.3. 用明确的 JOIN （连接）控制规划器

我们可以在一定程度上用明确的JOIN语法控制查询规划器。 要明白为什么有这茬事，我们首先需要一些背景知识。

在简单的连接查询里，比如

SELECT * FROM a,b,c WHERE a.id = b.id AND b.ref = c.id;

规划器可以按照任何顺序自由地连接给出的表。 比如，它可以生成一个查询规划先用WHERE子句 a.id = b.id 把 A 连接到 B，然后用另外一个WHERE子句把 C 连接到这个表上来， 或者它也可以先连接 B 和 C 然后再连接 A， 也得到这个结果。或者它也可以连接 A 到 C 然后把结果与 B 连接， 不过这么做效率比较差，因为必须生成完整的 A 和 C 的迪卡尔积， 而在查询里没有可用的WHERE子句可以优化该连接。 （PostgreSQL 执行器里的所有连接都发生在两个输入表之间，所以在这种情况下它必须先得出一个结果。） 重要的一点是这些连接方式给出语义上相同的结果，但在执行开销上却可能有巨大的差别。 因此，规划器会对它们进行检查并找出最高效的查询规划。

如果查询只涉及两或三个表，那么在查询里不会有太多需要考虑的连接。 但是潜在的连接顺序的数目随着表数目的增加程指数增加的趋势。 当超过十个左右的表以后，实际上根本不可能对所有可能做一次穷举搜索， 甚至对六七个表都需要相当长的时间进行规划。 如果有太多输入的表，PostgreSQL 规划器将从穷举搜索切换为基因概率搜索， 以减少可能性数目（样本空间）。 （切换的阈值是用运行时参数 geqo_threshold 设置的。） 基因搜索花的时间少，但是并不一定能找到最好的规划。

当查询涉及外部连接时，规划器就不象对付普通（内部）连接那么自由了。 比如，看看下面这个查询

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

尽管这个查询的约束和前面一个非常相似，但它们的语义却不同， 因为如果 A 里有任何一行不能匹配 B 和 C 的连接里的行， 那么该行都必须输出。因此这里规划器对连接顺序没有什么选择： 它必须先连接 B 到 C，然后把 A 连接到该结果上。因此， 这个查询比前面一个花在规划上的时间少。

明确的连接语法（INNER JOIN，CROSS JOIN， 或者无修饰的 JOIN）语义上和和 FROM 中 列出输入关系是一样的，因此我们没有必要约束连接顺序。 但是我们可以告诉 PostgreSQL 的查询规划器把明确的内 JOIN 当作约束顺序。 比如，下面三个查询逻辑上是等效的：

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

但如果我们告诉规划期尊重 JOIN 的顺序，那么第二个和第三个还是要比第一个花在规划上的时间少。 这个作用对于只有三个表的连接而言是微不足道的， 但对于数目众多的表，可能就是救命稻草了。

要强制规划器为内层的连接遵循 JOIN 顺序，我们可以把运行时参数 join_collapse_limit 设置为 1。 （其他可能的数值在下面讨论。）

你完全不必为了缩短搜索时间来约束连接顺序， 因为在一个简单的FROM列表里使用JOIN操作符就很好了。 比如，

SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;

如果设置 join_collapse_limit = 1，那么这句话就相当于强迫规划器先把 A 连接到 B，然后再连接到其它的表上， 但并不约束其它的选择。在本例中，可能的连接顺序的数目减少了 5 倍。

按照上面的想法考虑规划器的搜索问题是一个很有用的技巧， 不管是对减少规划时间还是对引导规划器生成好的规划都很有帮助。 如果缺省时规划器选择了一个糟糕的连接顺序， 你可以用JOIN语法强迫它选择一个更好的 — 假设知道一个更好的顺序。所以我们建议多试验。

一个非常相近的影响规划时间的问题是把子查询压缩到它们的父查询里面。比如，考虑下面的查询

SELECT *
FROM x, y,
    (SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;

这个情况可能在那种包含连接的视图中出现；该视图的SELECT规则将被插入到引用视图的场合，生成非常类似上面的查询。 通常，规划器会试图把子查询压缩到父查询里，生成

SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;

这样通常会生成一个比独立的子查询更好些的规划。 （比如，外层的 WHERE 条件可能是先把 X 连接到 A 上，这样就消除了 A 中的许多行， 因此避免了形成全部子查询逻辑输出的需要。）但是同时，我们增加了规划的时间； 在这里，我们有一个用五路连接代替两个独立的三路连接的问题， 这样的差距是巨大的，因为可能的规划数的是按照指数增长的。 规划器将在父查询可能超过 from_collapse_limit 个FROM项的时候，不再压缩子查询， 以此来避免巨大的连接搜索数的问题。 你可以通过调整这个运行时参数来在规划时间和规划质量之间作出平衡。

from_collapse_limit 和 join_collapse_limit 名字类似时因为他们做的事情几乎相同：一个控制规划期何时把子查询"平面化"， 另外一个控制何时把明确的内连接平面化。通常，你要么把 join_collapse_limit 设置成和 from_collapse_limit 一样（这样，明确连接和子查询的行为类似）要么把 join_collapse_limit 设置为 1（如果你想用明确连接控制连接顺序）。 但是你可以把它们设置成不同的值，这样你就可以在规划时间和运行时间之间进行仔细的调节。

13.4. 向数据库中添加记录

我们第一次填充数据库时可能需要做大量的表插入。 下面是一些建议，可以尽可能高效地处理这些事情。
13.4.1. 关闭自动提交

关闭自动提交，并且只在每次（数据拷贝）结束的时候做一次提交。 （在纯 SQL 里，这就意味着在开始的时候发出 BEGIN， 并且在结束的时候执行 COMMIT。有些客户端的库可能背着你干这些事情， 这种情况下你必须确信只有在你要那些库干这些事情的时候它才做。） 如果你允许每个插入都独立地提交，那么 PostgreSQL 会为所增加的每行记录做大量的处理。 在一个事务里完成所有插入的动作的最大的好处就是，如果有一条记录插入失败， 那么，到该点为止的所有已插入记录都将被回滚，这样你就不会很难受地面对一个只装载了一部分数据的表。
13.4.2. 使用 COPY

使用 COPY 在一条命令里装载所有记录， 而不是一连串的INSERT命令。COPY 命令是为装载数量巨大的数据行优化过的； 它没 INSERT 那么灵活，但是在大量装载数据的情况下，导致的过荷也少很多。 因为 COPY 是单条命令，因此填充表的时候就没有必要关闭自动提交了。

如果你不能使用 COPY，那么 使用 PREPARE 来创建一个准备好的 INSERT， 然后使用 EXECUTE 多次效率更高。 这样就避免了重复分析和规划 INSERT 的开销。

请注意，在装载大量数据行的时候，COPY 几乎总是比 INSERT 快， 即使使用了 PREPARE 并且把多个 INSERT 命令绑在一个事务中也是这样的。
13.4.3. 删除索引

如果你正在装载一个新创建的表，最快的方法是创建表， 用COPY批量装载，然后创建表需要的任何索引。 在已存在数据的表上创建索引要比递增地更新所装载的每一行记录要快。

如果你对现有表进行增大操作，你可以删除索引， 装载表，然后重新创建索引。 当然，在缺少索引的期间，其他数据库用户的数据库性能将有负面的影响。 并且我们在删除唯一索引之前还需要仔细考虑清楚，因为唯一约束 提供的错误检查在缺少索引的时候会消失．
13.4.4. 增大 maintenance_work_mem

在装载大量的数据的时候，可以临时增大 maintenance_work_mem 配置变量以便改进性能。 这是因为在从零开始创建一个 B-tree 索引地时候，现有的表需要进行排序。 允许融合排序使用更多的缓冲页面意味着需要少一些的融合回合数。
13.4.5. 增大 checkpoint_segments

临时增大 checkpoint_segments 配置变量也可以让大量数据装载得更快。 这是因为向 PostgreSQL 里面装载大量的数据可以导致检查点操作 （由配置变量 checkpoint_timeout 声明） 比平常更加频繁发生。在发生一个检查点的时候，所有脏数据都必须刷新到磁盘上。 通过在大量数据装载的时候临时增加 checkpoint_segments， 所要求的检查点的数目可以减少。
13.4.6. 事后运行ANALYZE

不管甚么时候，如果你在增加或者更新了大量数据之后， 运行 ANALYZE 都是个好习惯。 运行 ANALYZE（或者 VACUUM ANALYZE） 可以保证规划器有最新的表的数据的统计。 如果没有统计数据或者统计数据太陈旧，那么规划器可能选择很差劲的查询规划，导致检索你的表的查询性能的恶化。