VACUUM、ANALYZE、EXPLAIN 和 COUNT 简介
这些文章版权所有 2005 年由 Jim Nasby，并在他在 Pervasive Software 工作期间撰写。经作者许可在此发布。
清理数据库中的垃圾
任何数据库的关键组件都是 ACID。有一个优秀的 关于 ACID 的维基百科文章，但简而言之，ACID 是保护数据库中数据的关键。如果数据库不是 ACID，那么没有任何东西可以确保数据在面对看似随机的变化时是安全的。这就是为什么几乎所有流行的数据库都符合 ACID（MySQL 在某些模式下是一个显著的例外）。
ACID 性有很多方面，但 MVCC (多版本并发控制) 主要关注 I，即隔离。隔离确保多个用户访问相同数据时，将获得与只有一个用户访问数据时相同的结果。
确保这一点的一种简单方法是不允许任何用户修改任何其他用户当前正在读取的数据。这保证了数据在每个人都完成读取之前不会改变。这是通过使用“读取锁定”实现的，也是许多数据库的操作方式。但是读取锁定有一些严重的缺点。
想象一个在网站上使用的数据库。网站上的大多数页面至少会对数据库执行一次查询，而许多页面会执行多次查询。当然，还有一些页面会修改数据。
现在，请记住，对于从数据库读取的每一行，都必须获取读取锁。因此，每个页面都将获取许多锁，有时数百个锁。每次获取或释放锁时，数据库都不会处理你的数据；它正在处理这些锁。
但是那些更新数据的页面呢？你无法更新任何正在读取的内容，同样，任何正在更新的内容也不能读取。当有人想要更新数据时，他们必须等到所有当前正在读取数据的人完成。同时，为了确保想要更新的人最终能够进行更新，想要读取该数据的新的查询将被阻塞，直到更新完成。因此，仅仅通过一个想要进行更新的查询，你就让大量的人在等待更新完成，而更新则在等待大量读取完成。
考虑使用 MVCC 的相同情况。所有正在读取数据的查询都不需要获取任何锁。这意味着数据库不需要处理很多锁，因此它可以花费更多时间处理你的数据（这正是你想要数据库做的）。更重要的是，更新查询不需要等待任何读取查询，它可以立即运行，读取查询也不需要等待更新查询。与多个查询等待其他查询完成不同，你的网站只是在不断运行。
当然，MVCC 并非没有缺点。MVCC 中的“MV”代表多版本。这意味着每当数据发生变化时，都会保留相同数据的多个版本。Oracle 通过将旧数据滚动到“撤消日志”中来实现这一点。PostgreSQL 不使用撤消日志；相反，它在基本表中保留数据的多个版本。这意味着在进行更新时，开销要少得多，并且你必须偶尔删除旧版本。这是 VACUUM 的作用之一。
PostgreSQL 管理这些多个版本的方式是，在每一行中存储一些额外的信息。此信息用于确定哪些事务应该能够看到该行。如果该行是旧版本，则存在一些信息告诉 PostgreSQL 在哪里可以找到该行的最新版本。此信息需要在更新期间锁定行。
考虑以下场景：将一行插入到具有多个索引的表中，并且该事务提交。对该行进行了一些更新。每个更新都将在所有索引中创建一个新行，即使索引键没有更改。并且每个更新还会在基本表中留下该行的旧版本，该版本已被更新以指向替换它的新版本的行的位置。所有旧数据都将保留在那里，直到对该表运行 vacuum。在一个繁忙的系统中，所有旧数据很快就会变成大量浪费的空间。如果它增长到不可接受的水平，则很难回收这些空间。
这对那些想要保持 PostgreSQL 数据库良好性能的人来说意味着，正确的 vacuuming 至关重要。对于任何看到大量更新（或插入/删除）负载的表来说，这一点尤其重要，例如用于实现某种队列的表。如果这些表很小，则通常应该经常对其进行 vacuuming（比 autovacuum 通常提供的频率更高）。对于中等负载，autovacuum 通常可以很好地将死空间降至最低。你可以并且应该调整 autovacuum 来正确地维护这些繁忙的表，而不是手动 vacuuming 它们。
请注意，VACUUM FULL 与常规 VACUUM 相比非常昂贵。它会从头开始重建整个表和所有索引，并且在运行时会持有该表的写锁。这将阻止所有 DML。
在 9.0 之前，VACUUM FULL 的工作方式有所不同。它实际上会将元组移动到表中，这很慢，会导致表膨胀。不建议使用它。有些人使用 CLUSTER 代替，但请注意，在 9.0 之前，CLUSTER 不是 MVCC 安全的，可能会导致数据丢失。
PostgreSQL 记得你清理了什么吗？
当数据库需要将新数据添加到表中作为 INSERT 或 UPDATE 的结果时，它需要找到某个地方存储这些数据。它可以有 3 种方法来做到这一点
- 扫描整个表以找到一些空闲空间
- 只需将信息添加到表的末尾
- 记住表中的哪些页面有空闲空间可用，并使用其中一个
选项 1 显然会非常慢。想象一下，每次你想要添加或更新数据时，都要读取整个表！选项 2 很快，但会导致表在每次添加行时都会变大。这留下了选项 3，这就是 FSM 的用武之地。FSM 是 PostgreSQL 用于跟踪具有空闲空间可用的页面的地方。每次它需要在表中空间时，它都会首先查看 FSM；如果它找不到该表的任何空闲空间，它将回退到将信息添加到表的末尾。
在现实生活中，这意味着什么？将页面放入 FSM 的唯一方法是通过 VACUUM。但是 FSM 的大小有限，因此每个表只允许一定量的空间来存储有关具有空闲空间的页面的信息。如果一个表具有比 FSM 中的空间更多的具有空闲空间的页面，则具有最少空闲空间的页面根本不会存储。这意味着这些页面上的空间将不会使用，直到至少下次对该表进行 vacuuming 为止。
最终结果是在一个具有大量具有空闲空间的页面的数据库中（例如，一个长时间未被 vacuuming 的数据库），将难以重新使用空闲空间。
幸运的是，有一种简单的方法可以估计需要多少空闲空间：VACUUM VERBOSE。每次在整个数据库上运行 VACUUM VERBOSE 时（即：vacuumdb -av），最后两行包含有关 FSM 利用率的信息
INFO: free space map: 81 relations, 235349 pages stored; 220672 total pages needed
DETAIL: Allocated FSM size: 1000 relations + 2000000 pages = 11817 kB shared memory.
第一行表明 FSM 中有 81 个关系，并且这些关系存储了 235349 个具有空闲空间的页面。数据库估计 FSM 中需要 220672 个插槽。
第二行显示实际的 FSM 设置。此 PostgreSQL 安装设置为跟踪 1000 个关系（max_fsm_relations），总共 2000000 个空闲页面（max_fsm_pages）。（请注意，这些参数已从 8.4 开始删除，因为空闲空间映射由 PostgreSQL 自动管理，不需要用户调整。）。
请注意，如果 PostgreSQL 安装中有许多数据库，而你只 vacuuming 其中一个，那么这些信息将不准确。最好 vacuuming 整个安装。
配置空闲空间映射（仅限 Pg 8.3 及更早版本）
确保你有足够的 FSM 页面，最好的方法是定期使用 vacuum -av vacuuming 整个安装，并查看最后两行的输出。你希望确保 max_fsm_pages 至少与“存储的页面”或“总共需要的页面”中较大的那个一样大。
比 max_fsm_pages 更重要的是 max_fsm_relations。如果安装中的关系数量超过 max_fsm_relations（这包括临时表），则某些关系将根本不会在 FSM 中存储任何信息。这甚至可能包括具有大量可用空闲空间的关系。因此，重要的是确保 max_fsm_relations 始终大于 VACUUM VERBOSE 报告的值，并且包括一些余量。同样，确保这一点的最佳方法是监控定期运行的 vacuum verbose 的结果。
使用 ANALYZE 优化 PostgreSQL 查询
vacuuming 不是你的数据库唯一需要的定期维护。你还需要分析数据库，以便查询规划器在决定如何执行查询时可以使用表统计信息。简而言之：确保你定期运行 ANALYZE，最好通过 autovacuum。并将 default_statistics_target（在 postgresql.conf 中）增加到 100。
PostgreSQL 的查询优化器非常复杂。根据你想要如何计算，几乎有十几个不同的构建块可以用来执行查询，如果查询连接了多个表，那么处理这些连接可能会有数百种甚至数千种不同的方法。把所有东西加在一起，很容易得到超过一百万种执行单个查询的不同方法。
那么，规划器如何确定运行查询的最佳方法？每个不同的“构建块”（在技术上称为查询节点）都有一个相关的函数来生成成本。这是你在运行 EXPLAIN 时看到的。
decibel=# explain select * from customer;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on customer (cost=0.00..12.50 rows=250 width=287)
(1 row)
不深入讨论如何阅读 EXPLAIN 输出（本身就是一篇单独的文章！），PostgreSQL 估计此查询将返回 250 行，平均每行占用 287 字节。获取第一行的成本为 0（实际上不是，只是数字太小，被四舍五入为 0），而获取整个结果集的成本为 12.50。从技术上讲，成本的单位是“从磁盘读取单个数据库页面的成本”，但实际上这个单位是相当任意的。它与你可以衡量的任何东西都没有实际关系。只要把成本想象成“工作量单位”；所以运行此查询将需要“12.5 个工作量单位”。
数据库是如何得出 12.5 的成本的？规划器调用了 Seq Scan 的成本估算函数。然后，该函数查找了有关“customer”表的许多统计信息，并使用它来估计执行查询所需的工作量。现在我们进入问题的核心：表统计信息！
PostgreSQL 会保留两组不同的关于表的统计信息。第一组与表的规模有关。这些信息存储在 pg_class 系统表中。“relpages”字段表示用于存储表的数据库页面的数量，“reltuples”字段表示表中的行数。reltuples/relpages 的值是每个页面上的平均行数，这是规划器需要知道的重要数字。通常情况下，查询只读取表的一小部分，返回有限数量的行。由于所有 I/O 操作都在页面级别进行，因此页面上的行数越多，数据库就需要读取越少的页面才能获取所需的所有行。
PostgreSQL 保留的另一组统计信息更直接地处理查询将返回多少行的这个问题。这些统计信息在字段的基础上进行维护。为了演示这个想法，让我们查询更有限的一组行。
decibel=# explain select * from customer where state='TX';
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using customer__state on customer (cost=0.00..5.96 rows=1 width=287)
Index Cond: (state = 'TX'::bpchar)
(2 rows)
现在，规划器认为我们只会得到一行。它通过查看 pg_stats.histogram_bounds 来估计这一点，这是一个值数组。每个值都定义了一个新的“桶”的起点，其中每个桶的大小都大致相同。例如，如果我们有一个包含数字 1 到 10 的表，并且我们有一个有两个桶的直方图，那么 pg_stats.histogram_bounds 将是 {1,5,10}。这告诉规划器，表中值为 1 到 5 的行与值为 5 到 10 的行数量相同。如果规划器将这些信息与 pg_class.reltuples 结合使用，它就可以估计将返回多少行。在这种情况下，如果我们执行 SELECT * FROM table WHERE value <= 5，规划器将看到值为 <= 5 的行与值为 >= 5 的行一样多，这意味着查询将返回表中的一半行。pg_class.reltuples 表明表中有 10 行，所以简单的数学告诉我们我们将会得到 5 行。然后，它可以查看每个页面上的行数，并决定需要读取多少页面。最后，有了所有这些信息，它就可以估计执行查询需要多少工作量单位。当然，实际上，在幕后它比这要复杂得多。例如，如果我们改为运行查询 SELECT * FROM table WHERE value <= 3，规划器现在将不得不通过对直方图数据进行插值来估计有多少行。
这种策略的一个问题是，如果有一些非常常见的数值，它们可能会导致一切偏离。例如，考虑以下直方图：{1,100,101}。100 到 101 之间的数值与 1 到 100 之间的数值一样多。但这是否意味着我们拥有 1 到 100 之间的每个数字？每一个隔一个数字？我们只有一个 1 和一堆 50 吗？
幸运的是，PostgreSQL 还有两个额外的统计信息字段来帮助解决这个问题：most_common_vals 和 most_common_freqs。正如你可能猜到的，这些字段存储了有关表中最常见的值的信息。most_common_vals 字段存储实际值，most_common_freqs 字段存储每个值出现的频率，作为总行数的一部分。所以，如果 most_common_vals 是 {1,2}，而 most_common_freqs 是 {0.2,0.11}，那么表中 20% 的值是 1，11% 的值是 2。
即使有了 most_common_vals，你仍然可能会遇到问题。默认情况下是存储 10 个最常见的值和直方图中的 10 个桶。但是，如果你有很多不同的值，并且这些值的分布差异很大，那么很容易“超载”统计信息。幸运的是，很容易增加直方图桶的数量和存储的常见值数量。有两种方法可以做到这一点。第一种方法是使用 postgresql.conf 参数 default_statistics_target。由于增加统计信息唯一的缺点是目录表中使用的空间更多，因此对于大多数安装，我建议将此参数提高到至少 100，如果你的表数量相对较少，我甚至建议提高到 300 或更高。
第二种方法是使用 ALTER TABLE，例如：ALTER TABLE table_name ALTER column_name SET STATISTICS 1000。这会覆盖表 table_name 中列 column_name 的 default_statistics_target。如果你有大量的表（例如，超过 100 个），使用非常大的 default_statistics_target 可能会导致统计信息表的大小增长到足以成为性能问题。在这种情况下，最好将 default_statistics_target 保持在适度较低的水平（可能在 50-100 范围内），并手动增加数据库中大型表的统计信息目标。请注意，只有在字段是 WHERE 子句的一部分时，才会使用字段的统计信息，因此没有理由增加从未搜索过的字段的目标。
关于直方图和最常见的值就说到这里。还有一个最终的统计信息处理在表中找到给定值的可能性，那就是 n_distinct。如果这个数字为正数，它就估计了表中不同值的个数。如果它为负数，它就表示不同值与总行数的比率。当 ANALYZE 认为不同值的个数会随着表的大小而变化时，会使用负形式。所以，如果字段中的每个值都是唯一的，n_distinct 将为 -1。
相关性是衡量表中行排序与字段排序相似性的指标。如果你按顺序扫描表，并且字段中的值在每行中都增加，那么相关性为 1。如果相反，每个字段都小于前面的字段，那么相关性为 -1。相关性是选择索引扫描的关键因素，因为接近 1 或 -1 的相关性意味着索引扫描不需要在表中跳动很多次。
最后，avg_width 是字段中数据的平均宽度，null_frac 是表中字段为空的行数所占的比例。
正如你所看到的，为了让规划器能够很好地选择如何执行查询，人们已经投入了大量的工作来维护足够的信息。但是，如果统计信息没有保持最新，或者更糟糕的是，根本没有收集统计信息，那么所有这些框架都毫无用处。还记得规划器决定选择德克萨斯州的所有客户将返回 1 行吗？那是因为在对表进行分析之前。让我们看看现实情况。
decibel=# analyze customer;
ANALYZE
decibel=# explain select * from customer where state='TX';
QUERY PLAN
--------------------------------------------------------------
Seq Scan on customer (cost=0.00..65.60 rows=2048 width=107)
Filter: (state = 'TX'::bpchar)
(2 rows)
行数的估计不仅远远偏离，而且偏离的程度足以改变查询的执行计划。这说明了为什么保持统计信息最新非常重要。正如我在本文开头提到的，最好的方法是使用 autovacuum，无论是 8.1.x 中内置的 autovacuum，还是 7.4.x 或 8.0.x 中的 contrib/pg_autovacuum。
这显然是一个非常复杂的话题。有关统计信息的更多信息，请访问 https://postgresql.ac.cn/docs/current/static/planner-stats-details.html。
PostgreSQL EXPLAIN
现在你了解了向查询规划器提供最新统计信息的重要性，这样它就可以规划执行查询的最佳方法。但是，你如何知道 PostgreSQL 实际上是如何执行你的查询的呢？这就是 EXPLAIN 的作用。
让我们看一个简单的例子，并解释各个部分的含义。
EXPLAIN SELECT * FROM CUSTOMER;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on customer (cost=0.00..60.48 rows=2048 width=107)
(1 row)
这告诉我们，优化器决定使用顺序扫描来执行查询。它估计返回第一行的成本为 0.00，而返回所有行的成本为 60.48。它认为将返回 2048 行，并且每行的平均宽度为 107 字节。
但是 EXPLAIN 实际上并没有运行查询。如果你想看看估计结果与实际情况有多接近，你需要使用 EXPLAIN ANALYZE。
EXPLAIN ANALYZE SELECT * FROM CUSTOMER;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on customer (cost=0.00..60.48 rows=2048 width=107) (actual time=0.033..6.577 rows=2048 loops=1)
Total runtime: 7.924 ms
(2 rows)
请注意，我们现在有一组新的信息；运行顺序扫描步骤所需的实际时间、该步骤返回的行数以及这些行被循环遍历的次数（稍后会详细介绍）。我们还获得了查询的总运行时间。
细心的读者会注意到，实际时间数字与成本估计并不完全匹配。实际上，这不是因为估计错误；而是因为估计的单位不是时间，而是任意单位。更具体地说，规划器估计的单位是“顺序从磁盘读取单个页面需要多长时间”。
当然，“SELECT * FROM table” 中并没有什么可以分析的，所以让我们尝试一些更有趣的东西……
EXPLAIN SELECT * FROM customer ORDER BY city;
QUERY PLAN
--------------------------------------------------------------------
Sort (cost=173.12..178.24 rows=2048 width=107)
Sort Key: city
-> Seq Scan on customer (cost=0.00..60.48 rows=2048 width=107)
现在我们看到查询计划包括两个步骤，排序和顺序扫描。虽然看起来可能违反直觉，但数据是从计划中较低的步骤流向较高的步骤，因此顺序扫描的输出被馈送到排序操作符（实际上，排序操作符是从顺序扫描中提取数据的）。
如果你查看排序步骤，你会注意到它告诉我们排序的内容（“排序键”）。许多查询步骤都会打印出像这样的附加信息。还要注意，从排序操作返回第一行的成本非常高，几乎与返回所有行的成本相同。这是因为排序操作必须在数据实际排序后才能返回任何行，而排序是耗时最长的步骤。
无论何时存在多个查询步骤，每个步骤中报告的成本不仅包括执行该步骤的成本，还包括执行所有后续步骤的成本。因此，在这个例子中，排序操作的实际成本是第一行的 173.12-60.48，或者所有行的 178.24-60.48。为什么我要从第一行和所有行的成本中减去 60.48？因为排序操作必须从顺序扫描中获取所有数据，然后才能返回任何数据。通常，当你看到一个步骤的第一个行成本和所有行成本非常相似时，该操作需要来自所有先前步骤的所有数据。
让我们看看更有趣的东西……
EXPLAIN ANALYZE SELECT * FROM customer JOIN contact USING (last_name);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.02..92.23 rows=2048 width=351) (actual time=1.366..58.684 rows=4096 loops=1)
Hash Cond: (("outer".last_name)::text = ("inner".last_name)::text)
-> Seq Scan on customer (cost=0.00..60.48 rows=2048 width=107) (actual time=0.079..21.658 rows=2048 loops=1)
-> Hash (cost=1.02..1.02 rows=2 width=287) (actual time=0.146..0.146 rows=2 loops=1)
-> Seq Scan on contact (cost=0.00..1.02 rows=2 width=287)
(actual time=0.074..0.088 rows=2 loops=1)
Total runtime: 62.233 ms
现在，我们来深入研究！请注意缩进的使用。缩进用于显示哪些查询步骤馈送到其他查询步骤。在这里我们可以看到，哈希连接由顺序扫描和哈希操作馈送。该哈希操作本身由另一个顺序扫描馈送。请注意，哈希操作的第一个行成本和所有行成本相同；它需要所有行才能返回任何行。当你查看哈希连接时的计划时，这一点变得有趣：第一行成本反映了哈希的总行成本，但它反映了客户顺序扫描的 0.00 的第一行成本。这是因为哈希连接一旦从两个输入中获得第一行，就可以开始返回行。
我承诺会回到循环的含义，所以这里有一个例子
-> Nested Loop (cost=5.64..14.71 rows=1 width=140) (actual time=18.983..19.481 rows=4 loops=1)
-> Hash Join (cost=5.64..8.82 rows=1 width=72) (actual time=18.876..19.212 rows=4 loops=1)
-> Index Scan using pg_class_oid_index on pg_class i (cost=0.00..5.88 rows=1 width=72)
(actual time=0.051..0.055 rows=1 loops=4)
嵌套循环是程序员应该熟悉的东西；它的工作原理如下
for each row in input_a
for each row in input_b
do something
next
next
因此，如果输入 a 中有 4 行，输入 b 将被完整读取 5 次。换句话说，它将被循环 4 次。这就是上面查询计划部分显示的内容。如果你进行计算，你会发现 0.055 * 4 占了哈希连接的总时间和嵌套循环的总时间之间大部分差异（剩余部分可能是测量所有这些的开销）。
那么，这在“现实生活中”意味着什么呢？通常，如果你在查询上运行 EXPLAIN，那是因为你试图提高它的性能。关键是确定花费时间最长的步骤，看看你能做些什么来解决它。让我们逐步浏览以下示例并确定“问题步骤”是什么。（这是一个任何拥有空数据库的人都可以运行并获得相同输出的查询）。
EXPLAIN ANALYZE SELECT * FROM pg_indexes WHERE tablename='pg_constraint';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=5.64..16.89 rows=1 width=260) (actual time=19.552..20.530 rows=4 loops=1)
Join Filter: ("inner".oid = "outer".reltablespace)
-> Nested Loop Left Join (cost=5.64..15.84 rows=1 width=200) (actual time=19.313..20.035 rows=4 loops=1)
Join Filter: ("inner".oid = "outer".relnamespace)
-> Nested Loop (cost=5.64..14.71 rows=1 width=140) (actual time=18.983..19.481 rows=4 loops=1)
-> Hash Join (cost=5.64..8.82 rows=1 width=72) (actual time=18.876..19.212 rows=4 loops=1)
Hash Cond: ("outer".indrelid = "inner".oid)
-> Seq Scan on pg_index x (cost=0.00..2.78 rows=78 width=8)
(actual time=0.037..0.296 rows=80 loops=1)
-> Hash (cost=5.63..5.63 rows=1 width=72)
(actual time=18.577..18.577 rows=1 loops=1)
-> Index Scan using pg_class_relname_nsp_index on pg_class c
(cost=0.00..5.63 rows=1 width=72)
(actual time=18.391..18.464 rows=1 loops=1)
Index Cond: (relname = 'pg_constraint'::name)
Filter: (relkind = 'r'::"char")
-> Index Scan using pg_class_oid_index on pg_class i (cost=0.00..5.88 rows=1 width=72)
(actual time=0.051..0.055 rows=1 loops=4)
Index Cond: (i.oid = "outer".indexrelid)
Filter: (relkind = 'i'::"char")
-> Seq Scan on pg_namespace n (cost=0.00..1.06 rows=6 width=68)
(actual time=0.014..0.045 rows=6 loops=4)
-> Seq Scan on pg_tablespace t (cost=0.00..1.02 rows=2 width=68)
(actual time=0.010..0.018 rows=2 loops=4)
Total runtime: 65.294 ms
嵌套循环占了大部分成本，运行时间为 20.035 毫秒。该嵌套循环也从嵌套循环和顺序扫描中提取数据，并且嵌套循环再次占了大部分成本（总时间为 19.481 毫秒）。
到目前为止，我们的“昂贵路径”如下所示
Nested Loop Left Join (cost=5.64..16.89 rows=1 width=260) (actual time=19.552..20.530 rows=4 loops=1)
Join Filter: ("inner".oid = "outer".reltablespace)
-> Nested Loop Left Join (cost=5.64..15.84 rows=1 width=200) (actual time=19.313..20.035 rows=4 loops=1)
Join Filter: ("inner".oid = "outer".relnamespace)
-> Nested Loop (cost=5.64..14.71 rows=1 width=140) (actual time=18.983..19.481 rows=4 loops=1)
在这个例子中，所有这些步骤都恰好在输出中一起出现，但并非总是如此。
最低嵌套循环节点从以下位置提取数据
-> Hash Join (cost=5.64..8.82 rows=1 width=72) (actual time=18.876..19.212 rows=4 loops=1)
Hash Cond: ("outer".indrelid = "inner".oid)
-> Seq Scan on pg_index x (cost=0.00..2.78 rows=78 width=8)
(actual time=0.037..0.296 rows=80 loops=1)
-> Hash (cost=5.63..5.63 rows=1 width=72) (actual time=18.577..18.577 rows=1 loops=1)
-> Index Scan using pg_class_relname_nsp_index on pg_class
(cost=0.00..5.63 rows=1 width=72) (actual time=18.391..18.464 rows=1 loops=1)
Index Cond: (relname = 'pg_constraint'::name)
Filter: (relkind = 'r'::"char")
-> Index Scan using pg_class_oid_index on pg_class i (cost=0.00..5.88 rows=1 width=72)
(actual time=0.051..0.055 rows=1 loops=4)
Index Cond: (i.oid = "outer".indexrelid)
Filter: (relkind = 'i'::"char")
在这里我们可以看到，哈希连接占用了大部分时间。它从顺序扫描和哈希中提取数据。该哈希占用了大部分时间
-> Hash (cost=5.63..5.63 rows=1 width=72) (actual time=18.577..18.577 rows=1 loops=1)
-> Index Scan using pg_class_relname_nsp_index on pg_class c
(cost=0.00..5.63 rows=1 width=72) (actual time=18.391..18.464 rows=1 loops=1)
Index Cond: (relname = 'pg_constraint'::name)
Filter: (relkind = 'r'::"char")
最后，我们来到了查询中最昂贵的部分：pg_class_relname_nsp_index 上的索引扫描。不幸的是，对只读取一行数据的索引扫描进行加速几乎是不可能的。但也要注意，它只花了 18.464 毫秒；你不太可能发现自己试图在这个级别上提高性能。
最后要注意一点：EXPLAIN ANALYZE 的测量开销并非微不足道。在极端情况下，它可能占查询执行时间的 30% 或更多。请记住，EXPLAIN 是用于测量相对性能，而不是绝对性能的工具。
不幸的是，EXPLAIN 是 PostgreSQL 手册中记录不佳的东西。我希望本文能阐明这个重要的调优工具。
聚合 - 为什么 min()、max() 和 count() 这么慢？
人们经常对 PostgreSQL 的聚合速度抱怨。人们经常问为什么 count(*) 或 min/max 比其他一些数据库慢。实际上这里有两个问题，一个很容易解决，另一个不容易解决。
ORDER BY / LIMIT 技巧
在 8.1 版之前，查询规划器不知道你可以使用索引来处理 min 或 max，因此它总是进行表扫描。幸运的是，你可以通过以下操作来解决这个问题
-- Find minimum value for field
SELECT field FROM table WHERE field IS NOT NULL ORDER BY field ASC LIMIT 1;
-- Find maximum value for field
SELECT field FROM table WHERE field IS NOT NULL ORDER BY field DESC LIMIT 1;
当然，这有点麻烦，所以在 8.1 中，规划器进行了更改，以便它可以动态地进行这种替换。不幸的是，它并不完美；在撰写本文时，我发现对包含大量 NULL 值的字段执行 SELECT max() 会花费很长时间，即使它正在使用该字段的索引。如果你尝试 ORDER BY / LIMIT 技巧，它同样慢。我怀疑这是因为数据库必须扫描所有 NULL 值。事实上，如果你在该字段上创建一个索引并从该索引中排除 NULL 值，则 ORDER BY / LIMIT 技巧将使用该索引并非常快地返回结果。但是对该字段执行简单的 max() 将继续使用包含 NULL 值的索引。
COUNT(*)
第二个问题不容易解决。如果你阅读过我之前的文章，你会记得 PostgreSQL 的 MVCC（多版本并发控制）通过保存已更新的表行的多个版本，并且不立即删除已删除的行，从而消除了对昂贵的读锁的需求。这是通过在每行中存储“可见性信息”来实现的。但出于性能原因，此信息不会存储在索引中。这意味着每次从索引中读取一行时，引擎还必须读取表中的实际行，以确保该行尚未被删除。
另一方面，许多其他数据库没有这个要求；如果一行在索引中，那么它就是表中的有效行。这使这些数据库能够进行所谓的“索引覆盖”。简而言之，如果查询需要的所有信息都在索引中，则数据库可以只读取索引而不读取基础表，从而提供更高的性能。
如果你使用 count(*)，数据库可以自由地使用任何列进行计数，这意味着它可以选择最小的覆盖索引进行扫描（注意，这就是为什么 count(*) 比 count(some_field) 好得多，只要你不关心是否计算 some_field 的 null 值）。由于索引通常完全适合内存，这意味着 count(*) 通常非常快。
但正如我提到的，PostgreSQL 必须在每次从索引中读取时读取基础表。这意味着，无论如何，SELECT count(*) FROM table; 必须读取整个表。幸运的是，在 8.2 中有一些计划可以允许部分索引覆盖。简而言之，数据库将跟踪已知不包含任何已删除行的表页。有了这些信息，引擎能够非常快地确定它是否需要查看基础表以获取从索引中读取的任何给定行。这意味着，那些没有进行大量更新或删除的表将看到索引扫描性能接近于在可以进行真正索引覆盖的数据库上获得的性能。
除了 8.2 的这个不错的性能改进之外，如果你目前使用 count(*)，你仍然可以通过一些方法来提高性能。关键是要考虑为什么首先使用 count(*)。你真的需要确切的计数吗？在许多情况下，你不需要。count(*) 可以说是最被滥用的数据库函数之一。我见过它在许多不需要的地方被使用。也许最糟糕的是作为查看特定行是否存在的一种方法，例如
SELECT count(*) INTO variable FROM table WHERE condition;
IF variable > 0 THEN
...
END IF;
你在这里不需要确切的计数。相反，尝试
IF EXISTS( SELECT * FROM table WHERE condition ) THEN
或者，如果你使用的是外部语言（虽然如果你在外部语言中这样做，你应该问问自己是否应该改为编写一个存储过程……）
SELECT 1 INTO dummy WHERE EXISTS( SELECT * FROM table WHERE condition );
IF FOUND THEN
...
END IF;
注意，在这个例子中，你将得到一行或没有行。
也许你正在处理一些实际上需要某种计数的东西。
在这种情况下，请考虑使用估计值。Google 就是一个完美的例子。
你有没有注意到，当你搜索某些内容时，结果页面会显示你正在查看“约 728,000 条结果中的结果 1-10”？这是因为没有理由提供确切的数字。
如何获取 count(*) 的估计值？如果你只想了解表中大约的行数，你可以简单地从 pg_class 中选择
SELECT reltuples FROM pg_class WHERE oid = 'schemaname.tablename'::regclass::oid;
返回的数字是对上次 ANALYZE 时表中表数的估计。
如果你想要从任意查询中返回的行数的估计值，不幸的是你需要解析 explain 的输出。
最后，如果你必须有确切的计数并且性能是一个问题，你可以构建一个包含表中行数的摘要表。有两种方法可以做到这一点。最简单的方法是创建一个触发器或规则，每当插入或删除行时更新摘要表：http://www.varlena.com/varlena/GeneralBits/49.php 是如何做到这一点的示例。这种方法的缺点是它迫使你正在对它进行计数的所有表上的所有插入和删除序列化。这是因为一次只能有一个事务更新行计数表中的相应行。
此方法的一个变体可以消除序列化，即保存从表中插入或删除的行的“运行总数”。由于该运行总数只需要插入到总数表中，因此多个事务可以同时更新你正在对它进行计数的表。缺点是，你必须定期清除总数表。此技术的摘要可以在 http://archives.postgresql.org/pgsql-performance/2004-01/msg00059.php 中找到。
当然，如果你需要对整个表以外的其他内容进行计数，这些技巧都不会对你有所帮助，但根据你的要求，你可以修改这两种技巧，以添加对你计数的条件的约束。
