postgres using explain
发表更新postgres1 小时读完 (大约8347个字)0次访问

postgres using explain

14.1. 使用 EXPLAIN

PostgreSQL 会为其接收的每个查询设计一个查询计划。选择正确的计划以匹配查询结构和数据属性对于获得良好性能至关重要，因此系统包含一个复杂的规划器，该规划器尝试选择好的计划。您可以使用 EXPLAIN 命令查看规划器为任何查询创建的查询计划。计划解读是一门艺术，需要一定的经验才能掌握，但本节旨在涵盖其基础知识。

本节中的示例取自执行 VACUUM ANALYZE 后的回归测试数据库，并使用了 v17 开发版本。如果您自己尝试这些示例，应该能获得类似的结果，但您的估计成本和行数可能会略有不同，因为 ANALYZE 的统计数据是随机抽样而非精确的，并且成本本身在某种程度上依赖于平台。

这些示例使用 EXPLAIN 默认的“文本”输出格式，该格式紧凑且方便人类阅读。如果您想将 EXPLAIN 的输出提供给程序进行进一步分析，则应改用其机器可读的输出格式之一（XML、JSON 或 YAML）。

14.1.1. EXPLAIN 基础知识

查询计划的结构是计划节点的树形结构。树的最底层节点是扫描节点：它们从表中返回原始行。针对不同的表访问方法，有不同类型的扫描节点：顺序扫描、索引扫描和位图索引扫描。此外，还有非表行源，例如 VALUES 子句和 FROM 子句中的集合返回函数，它们拥有自己的扫描节点类型。如果查询需要对原始行进行连接、聚合、排序或其他操作，那么扫描节点上方将有额外的节点来执行这些操作。同样，执行这些操作通常有不止一种可能的方法，因此这里也可以出现不同类型的节点。EXPLAIN 的输出为计划树中的每个节点提供一行，显示基本的节点类型以及规划器对该计划节点执行的成本估计。可能会出现额外的行，从节点的摘要行缩进，以显示节点的附加属性。第一行（最顶层节点的摘要行）是计划的估计总执行成本；规划器致力于将这个数字最小化。

这是一个简单的例子，只为展示输出的样式：

1
2
3
4
5
EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

由于此查询没有 WHERE 子句，它必须扫描表的所有行，因此规划器选择了使用简单的顺序扫描计划。括号中引用的数字（从左到右）是：

  • 估计启动成本（Estimated start-up cost）。这是输出阶段开始前所花费的时间，例如，在排序节点中执行排序所需的时间。
  • 估计总成本（Estimated total cost）。这是基于计划节点运行完成的假设得出的，即所有可用行都已检索。实际上，节点的父节点可能在读取所有可用行之前停止（请参阅下面的 LIMIT 示例）。
  • 此计划节点输出的估计行数（Estimated number of rows output by this plan node）。同样，假定该节点已运行完成。
  • 此计划节点输出的行的估计平均宽度（以字节为单位）（Estimated average width of rows output by this plan node (in bytes)）。

成本以规划器的成本参数（参见 Section 19.7.2）确定的任意单位衡量。传统做法是以磁盘页面获取为单位衡量成本；也就是说，seq_page_cost 通常设置为 1.0，其他成本参数相对于它进行设置。本节中的示例使用默认的成本参数运行。

理解上层节点的成本包含其所有子节点的成本是很重要的。同样重要的是要认识到，成本只反映规划器关心的事情。特别是，成本不考虑将输出值转换为文本形式或将其传输到客户端所花费的时间，这在实际经过的时间中可能是重要因素；但规划器忽略这些成本，因为它无法通过更改计划来改变它们。（我们相信，每个正确的计划都将输出相同的行集。）

rows 值有点棘手，因为它不是计划节点处理或扫描的行数，而是节点发出的行数。由于在节点处应用了任何 WHERE 子句条件进行过滤，这通常少于扫描的行数。理想情况下，顶层 rows 估计将近似于查询实际返回、更新或删除的行数。

回到我们的例子：

1
2
3
4
5
EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

这些数字的推导非常直接。如果您执行：

1
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

您会发现 tenk1 有 345 个磁盘页面和 10000 行。估计成本计算为 (读取的磁盘页面 * seq_page_cost) + (扫描的行数 * cpu_tuple_cost)。默认情况下，seq_page_cost 是 1.0，cpu_tuple_cost 是 0.01，所以估计成本是 (345 * 1.0) + (10000 * 0.01) = 445。

现在让我们修改查询以添加一个 WHERE 条件：

1
2
3
4
5
6
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
   Filter: (unique1 < 7000)

请注意，EXPLAIN 输出显示 WHERE 子句作为附加到 Seq Scan 计划节点的“过滤器”条件。这意味着计划节点检查它扫描的每一行的条件，并只输出通过条件的行。由于 WHERE 子句，输出行数的估计已减少。但是，扫描仍然需要访问所有 10000 行，因此成本没有降低；事实上，它略有上升（准确地说是增加了 10000 * cpu_operator_cost）以反映检查 WHERE 条件所花费的额外 CPU 时间。

这个查询实际选择的行数是 7000，但行数估计只是近似值。如果您尝试重复此实验，您很可能会得到一个略有不同的估计；此外，它可以在每个 ANALYZE 命令之后改变，因为 ANALYZE 生成的统计数据是从表的随机样本中获取的。

现在，让我们使条件更具限制性：

1
2
3
4
5
6
7
8
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

这里规划器决定使用两步计划：子计划节点访问索引以查找匹配索引条件的行位置，然后上层计划节点实际上从表本身获取这些行。单独获取行的成本比顺序读取它们昂贵得多，但由于不必访问表的所有页面，这仍然比顺序扫描便宜。（使用两个计划级别的原因是上层计划节点在读取它们之前，将索引识别的行位置按物理顺序排序，以最小化单独获取的成本。“位图”在节点名称中提到的机制是执行排序的机制。）

现在，让我们在 WHERE 子句中添加另一个条件：

1
2
3
4
5
6
7
8
9
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)
   Recheck Cond: (unique1 < 100)
   Filter: (stringu1 = 'xxx'::name)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

添加的条件 stringu1 = 'xxx' 减少了输出行数估计，但没有减少成本，因为我们仍然需要访问相同的行集。这是因为 stringu1 子句不能作为索引条件应用，因为此索引只在 unique1 列上。相反，它被作为过滤器应用于使用索引检索的行。因此，成本实际上略有上升，以反映这种额外的检查。

在某些情况下，规划器会选择“简单”索引扫描计划：

1
2
3
4
5
6
EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: (unique1 = 42)

在这种类型的计划中，表行按索引顺序获取，这使得读取它们更加昂贵，但由于行数很少，排序行位置的额外成本不值得。对于只获取单行的查询，您最常会看到这种计划类型。它也常用于具有匹配索引顺序的 ORDER BY 条件的查询，因为这样就不需要额外的排序步骤来满足 ORDER BY。在此示例中，添加 ORDER BY unique1 将使用相同的计划，因为索引已经隐式提供了请求的排序。

规划器可以通过多种方式实现 ORDER BY 子句。上面的示例显示这种排序子句可以隐式实现。规划器也可能添加一个显式的 Sort 步骤：

1
2
3
4
5
6
7
EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

                            QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=1109.39..1134.39 rows=10000 width=244)
   Sort Key: unique1
   ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

如果计划的一部分保证了所需排序键前缀上的排序，那么规划器可能会决定改用增量排序步骤：

1
2
3
4
5
6
7
8
9
EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;

                                              QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=19.35..39.49 rows=100 width=244)
   ->  Incremental Sort  (cost=19.35..2033.39 rows=10000 width=244)
         Sort Key: hundred, ten
         Presorted Key: hundred
         ->  Index Scan using tenk1_hundred on tenk1  (cost=0.29..1574.20 rows=10000 width=244)

与常规排序相比，增量排序允许在整个结果集排序完成之前返回元组，这尤其有利于 LIMIT 查询的优化。它还可以减少内存使用和将排序溢出到磁盘的可能性，但这需要以增加将结果集分成多个排序批次的开销为代价。

如果在 WHERE 子句中引用的多个列上有单独的索引，规划器可能会选择使用索引的 AND 或 OR 组合：

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
               Index Cond: (unique2 > 9000)

但这需要访问两个索引，因此与仅使用一个索引并将另一个条件作为过滤器处理相比，不一定有优势。如果您更改涉及的范围，您会看到计划相应地更改。

这是一个显示 LIMIT 效果的示例：

1
2
3
4
5
6
7
8
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Limit  (cost=0.29..14.28 rows=2 width=244)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.27 rows=10 width=244)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)

这与上面的查询相同，但我们添加了 LIMIT，这样就不需要检索所有行，规划器也改变了它的做法。请注意，Index Scan 节点的总成本和行数显示为如果它运行完成。但是，Limit 节点预计在检索到五分之一的行后停止，因此其总成本仅为五分之一，这就是查询的实际估计成本。此计划优于向先前计划添加 Limit 节点，因为 Limit 无法避免支付位图扫描的启动成本，因此该方法总成本将超过 25 个单位。

让我们尝试连接两个表，使用我们一直在讨论的列：

1
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

在此计划中，我们有一个嵌套循环连接节点，其输入或子节点是两个表扫描。节点摘要行的缩进反映了计划树结构。连接的第一个或“外部”子节点是位图扫描，类似于我们之前看到的那些。它的成本和行数与我们从 SELECT ... WHERE unique1 < 10 获得的结果相同，因为我们在该节点应用 WHERE 子句 unique1 < 10。t1.unique2 = t2.unique2 子句尚未相关，因此它不影响外部扫描的行数。嵌套循环连接节点将为其从外部子节点获得的每一行运行其第二个或“内部”子节点一次。当前外部行中的列值可以插入到内部扫描中；在这里，外部行中的 t1.unique2 值可用，因此我们得到了一个计划和成本，类似于我们上面为简单 SELECT ... WHERE t2.unique2 = constant 情况所看到的。（估计成本实际上比上面看到的略低，这是由于在 t2 上重复索引扫描期间预期的缓存所致。）然后根据外部扫描的成本，加上每个外部行内部扫描的一次重复（这里是 10 * 7.90），再加上一点用于连接处理的 CPU 时间来设置循环节点的成本。

在本例中，连接的输出行数与两次扫描的行数乘积相同，但这并非在所有情况下都如此，因为可能存在提及两个表的额外 WHERE 子句，因此只能在连接点而不是任何输入扫描处应用。下面是一个例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
               Index Cond: (unique2 < 10)

条件 t1.hundred < t2.hundred 无法在 tenk2_unique2 索引中测试，因此它在连接节点应用。这减少了连接节点的估计输出行数，但没有改变任何输入扫描。

请注意，这里规划器选择通过在连接上放置一个 Materialize 计划节点来“具体化”连接的内部关系。这意味着 t2 索引扫描只会执行一次，即使嵌套循环连接节点需要读取该数据十次，即对于外部关系的每一行一次。Materialize 节点在读取数据时将其保存在内存中，然后在每次后续通过时从内存中返回数据。

在处理外连接时，您可能会看到附加了“Join Filter”和纯“Filter”条件的连接计划节点。Join Filter 条件来自外连接的 ON 子句，因此未能通过 Join Filter 条件的行仍然可以作为空扩展行发出。但纯 Filter 条件在外连接规则之后应用，因此可以无条件地删除行。在内连接中，这些过滤器类型之间没有语义差异。

如果我们稍微改变查询的选择性，我们可能会得到一个非常不同的连接计划：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..709.73 rows=100 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

这里，规划器选择使用哈希连接，其中一个表的行被输入到内存中的哈希表，然后扫描另一个表，并探测哈希表以查找与每行的匹配项。再次注意缩进如何反映计划结构：tenk1 上的位图扫描是 Hash 节点的输入，该节点构造哈希表。然后将其返回到 Hash Join 节点，该节点从其外部子计划读取行并为每行搜索哈希表。

另一种可能的连接类型是合并连接，示例如下：

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Merge Join  (cost=0.56..233.49 rows=10 width=488)
   Merge Cond: (t1.unique2 = t2.unique2)
   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)
         Filter: (unique1 < 100)
   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)

合并连接要求其输入数据按连接键排序。在本例中，每个输入都通过使用索引扫描按正确顺序访问行进行排序；但也可以使用顺序扫描和排序。（由于索引扫描所需的非顺序磁盘访问，顺序扫描和排序通常在排序多行时优于索引扫描。）

一种查看变体计划的方法是强制规划器不考虑它认为最便宜的策略，使用 Section 19.7.1 中描述的启用/禁用标志。（这是一个粗糙但有用的工具。另请参阅 Section 14.3）。例如，如果我们不相信合并连接是上一个示例的最佳连接类型，我们可以尝试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SET enable_mergejoin = off;

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..344.08 rows=10 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

这表明规划器认为在这种情况下哈希连接会比合并连接贵近 50%。当然，下一个问题是它是否正确。我们可以使用 EXPLAIN ANALYZE 来调查这一点，如下所述。

有些查询计划涉及子计划，它们源自原始查询中的子 SELECT。此类查询有时可以转换为普通的连接计划，但当它们无法转换时，我们会得到如下计划：

1
2
3
4
5
6
7
8
9
10
11
12
13
EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t
WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);

                               QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on public.tenk1 t  (cost=0.00..586095.00 rows=5000 width=4)
   Output: t.unique1
   Filter: (ALL (t.ten < (SubPlan 1).col1))
   SubPlan 1
     ->  Seq Scan on public.onek o  (cost=0.00..116.50 rows=250 width=4)
           Output: o.ten
           Filter: (o.four = t.four)

这个相当人为的例子说明了几点：外部计划级别的值可以向下传递到子计划（这里，t.four 被向下传递），并且子选择的结果可用于外部计划。EXPLAIN 用 (subplan_name).colN 这样的表示法显示这些结果值，它指的是子 SELECT 的第 N 个输出列。

在上面的示例中，ALL 运算符为外部查询的每一行再次运行子计划（这解释了其高估计成本）。一些查询可以使用哈希子计划来避免这种情况：

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN SELECT *
FROM tenk1 t
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on tenk1 t  (cost=61.77..531.77 rows=5000 width=244)
   Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Index Only Scan using onek_unique1 on onek o  (cost=0.28..59.27 rows=1000 width=4)
(4 rows)

在这里，子计划只运行一次，其输出被加载到内存中的哈希表，然后由外部 ANY 运算符进行探测。这要求子 SELECT 不引用外部查询的任何变量，并且 ANY 的比较运算符适合哈希。

如果除了不引用外部查询的任何变量之外，子 SELECT 不能返回多于一行，那么它可能被实现为 initplan：

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);

                             QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on public.tenk1 t1  (cost=0.02..470.02 rows=1000 width=4)
   Output: t1.unique1
   Filter: (t1.ten = (InitPlan 1).col1)
   InitPlan 1
     ->  Result  (cost=0.00..0.02 rows=1 width=4)
           Output: ((random() * '10'::double precision))::integer

initplan 在外部计划的每次执行中只运行一次，其结果被保存以供外部计划的后续行重复使用。因此，在这个例子中，random() 只评估一次，并且 t1.ten 的所有值都与相同的随机选择的整数进行比较。这与没有子 SELECT 构造时发生的情况大不相同。

14.1.2. EXPLAIN ANALYZE

可以使用 EXPLAIN 的 ANALYZE 选项检查规划器估计的准确性。使用此选项，EXPLAIN 实际上会执行查询，然后显示在每个计划节点中累积的真实行数和真实运行时间，以及普通 EXPLAIN 显示的相同估计值。例如，我们可能会得到这样的结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1)
         Recheck Cond: (unique1 < 10)
         Heap Blocks: exact=10
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10)
         Index Cond: (unique2 = t1.unique2)
 Planning Time: 0.485 ms
 Execution Time: 0.073 ms

请注意，“actual time”值以毫秒为单位（实时时间），而成本估计以任意单位表示；因此它们不太可能匹配。通常最重要的是查看估计的行数是否与实际情况合理接近。在这个例子中，估计值都非常准确，但实际上这很不常见。

在某些查询计划中，子计划节点可能会多次执行。例如，在上述嵌套循环计划中，内部索引扫描将每外部行执行一次。在这种情况下，loops 值报告节点的总执行次数，显示的实际时间和行值是每次执行的平均值。这样做是为了使数字与成本估计的显示方式保持可比性。乘以 loops 值以获得节点中实际花费的总时间。在上面的例子中，我们在 tenk2 上执行索引扫描总共花费了 0.030 毫秒。

在某些情况下，EXPLAIN ANALYZE 除了计划节点执行时间和行数外，还会显示额外的执行统计信息。例如，Sort 和 Hash 节点提供了额外的信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 74kB
   ->  Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1)
         ->  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 35kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     Heap Blocks: exact=90
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning Time: 0.187 ms
 Execution Time: 3.036 ms

Sort 节点显示使用的排序方法（特别是排序是在内存中还是在磁盘上）以及所需的内存或磁盘空间量。Hash 节点显示哈希桶和批次的数量以及哈希表使用的峰值内存量。（如果批次数量超过一个，还将涉及磁盘空间使用，但未显示。）

另一种额外信息类型是被过滤条件删除的行数：

1
2
3
4
5
6
7
8
9
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1)
   Filter: (ten < 7)
   Rows Removed by Filter: 3000
 Planning Time: 0.102 ms
 Execution Time: 2.145 ms

这些计数对于在连接节点应用的过滤器条件特别有价值。“Rows Removed”行仅在至少有一行被扫描（或者在连接节点的情况下，潜在的连接对）被过滤器条件拒绝时才出现。

与过滤条件类似的情况发生在“有损”索引扫描中。例如，考虑以下搜索包含特定点的多边形：

1
2
3
4
5
6
7
8
9
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on polygon_tbl  (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1)
   Filter: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Filter: 7
 Planning Time: 0.039 ms
 Execution Time: 0.033 ms

规划器认为（非常正确地）这个样本表太小，不值得使用索引扫描，所以我们有一个普通的顺序扫描，其中所有行都被过滤条件拒绝。但如果我们强制使用索引扫描，我们会看到：

1
2
3
4
5
6
7
8
9
10
11
SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1)
   Index Cond: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Index Recheck: 1
 Planning Time: 0.039 ms
 Execution Time: 0.098 ms

这里我们可以看到索引返回了一个候选行，然后该行被索引条件的重新检查拒绝了。发生这种情况是因为 GiST 索引对于多边形包含测试是“有损的”：它实际上返回与目标重叠的多边形的行，然后我们必须对这些行进行精确的包含测试。

EXPLAIN 有一个 BUFFERS 选项，可以与 ANALYZE 结合使用以获取更多运行时统计信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   Heap Blocks: exact=10
   Buffers: shared hit=14 read=3
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1)
         Buffers: shared hit=4 read=3
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1)
               Index Cond: (unique1 < 100)
               Buffers: shared hit=2
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1)
               Index Cond: (unique2 > 9000)
               Buffers: shared hit=2 read=3
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.162 ms
 Execution Time: 0.143 ms

BUFFERS 提供的数据有助于识别查询中哪些部分是 I/O 密集型。

请记住，由于 EXPLAIN ANALYZE 实际运行查询，任何副作用都会照常发生，即使查询可能输出的任何结果都被丢弃以利于打印 EXPLAIN 数据。如果您想分析数据修改查询而不更改表，您可以在之后回滚命令，例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BEGIN;

EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0 loops=1)
   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         Heap Blocks: exact=90
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1)
               Index Cond: (unique1 < 100)
 Planning Time: 0.151 ms
 Execution Time: 1.856 ms

ROLLBACK;

如本例所示，当查询是 INSERT、UPDATE、DELETE 或 MERGE 命令时，应用表更改的实际工作由顶层 Insert、Update、Delete 或 Merge 计划节点完成。该节点下的计划节点执行定位旧行和/或计算新数据的工作。因此，上面我们看到了与我们已经看到的位图表扫描相同的类型，其输出被馈送到一个 Update 节点，该节点存储更新的行。值得注意的是，尽管数据修改节点可能占用相当多的运行时间（这里它消耗了大部分时间），但规划器目前没有为该工作添加任何成本估计。那是因为要完成的工作对于每个正确的查询计划都是相同的，因此它不影响规划决策。

当 UPDATE、DELETE 或 MERGE 命令影响分区表或继承层次结构时，输出可能如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Update on gtest_parent  (cost=0.00..3.06 rows=0 width=0)
   Update on gtest_child gtest_parent_1
   Update on gtest_child2 gtest_parent_2
   Update on gtest_child3 gtest_parent_3
   ->  Append  (cost=0.00..3.06 rows=3 width=14)
         ->  Seq Scan on gtest_child gtest_parent_1  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child2 gtest_parent_2  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child3 gtest_parent_3  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)

在此示例中，Update 节点需要考虑三个子表，而不是最初提到的分区表（因为它从不存储任何数据）。因此，有三个输入扫描子计划，每个表一个。为了清晰起见，Update 节点被注释以显示将要更新的特定目标表，其顺序与相应的子计划相同。

EXPLAIN ANALYZE 显示的 Planning time 是从解析的查询生成查询计划并对其进行优化所需的时间。它不包括解析或重写。

EXPLAIN ANALYZE 显示的 Execution time 包括执行器启动和关闭时间，以及运行任何触发器的触发时间，但不包括解析、重写或规划时间。执行 BEFORE 触发器所花费的时间（如果有）包含在相关 Insert、Update 或 Delete 节点的时间中；但执行 AFTER 触发器所花费的时间不计入其中，因为 AFTER 触发器在整个计划完成后才触发。每个触发器（无论是 BEFORE 还是 AFTER）中花费的总时间也单独显示。请注意，延迟约束触发器直到事务结束才会执行，因此 EXPLAIN ANALYZE 完全不考虑它们。

顶级节点显示的时间不包括将查询的输出数据转换为可显示形式或将其发送到客户端所需的任何时间。虽然 EXPLAIN ANALYZE 永远不会将数据发送到客户端，但可以通过指定 SERIALIZE 选项来指示它将查询的输出数据转换为可显示形式并测量所需时间。该时间将单独显示，并且也包含在总 Execution time 中。

14.1.3. 注意事项

EXPLAIN ANALYZE 测量的运行时间与相同查询的正常执行方式之间存在两种显著差异。首先，由于没有输出行传递给客户端，因此不包括网络传输成本。除非指定 SERIALIZE，否则也不包括 I/O 转换成本。其次，EXPLAIN ANALYZE 添加的测量开销可能很大，尤其是在 gettimeofday() 操作系统调用缓慢的机器上。您可以使用 pg_test_timing 工具来测量您系统上计时开销。

EXPLAIN 的结果不应外推到与您实际测试的情况大相径庭的场景；例如，不能假定在玩具大小的表上的结果适用于大型表。规划器的成本估算不是线性的，因此它可能会为更大或更小的表选择不同的计划。一个极端的例子是，对于只占用一个磁盘页面的表，您几乎总是会得到一个顺序扫描计划，无论是否有索引可用。规划器知道无论如何处理表都需要读取一个磁盘页面，因此花费额外的页面读取来查看索引没有价值。（我们上面在 polygon_tbl 示例中看到了这一点。）

在某些情况下，实际值和估计值可能不匹配，但实际上并没有问题。一个这样的情况发生在计划节点执行因 LIMIT 或类似效果而提前停止时。例如，在我们之前使用的 LIMIT 查询中，

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)
         Rows Removed by Filter: 287
 Planning Time: 0.077 ms
 Execution Time: 0.086 ms

Index Scan 节点的估计成本和行数显示为它已运行完成。但实际上 Limit 节点在获取到两行后停止请求行，因此实际行数仅为 2，并且运行时间低于成本估计所暗示的。这不是估计错误，只是估计值和真实值显示方式上的差异。

合并连接也有可能让不熟悉的人感到困惑的测量伪影。如果合并连接用尽了另一个输入，并且一个输入中的下一个键值大于另一个输入中的最后一个键值，它将停止读取一个输入；在这种情况下，将不再有匹配项，因此不需要扫描第一个输入的其余部分。这导致不读取一个子节点的全部内容，其结果类似于 LIMIT 中提到的情况。此外，如果外部（第一个）子节点包含具有重复键值的行，则内部（第二个）子节点会回溯并重新扫描与其键值匹配的那部分行。EXPLAIN ANALYZE 将这些相同的内部行的重复发出计为实际的额外行。当有许多外部重复项时，内部子计划节点的报告实际行数可能显著大于内部关系中实际的行数。

由于实现限制，BitmapAnd 和 BitmapOr 节点始终将其实际行数报告为零。

通常，EXPLAIN 将显示规划器创建的每个计划节点。但是，在某些情况下，执行器可以根据规划时不可用的参数值确定某些节点不需要执行，因为它们无法生成任何行。（目前这只可能发生在正在扫描分区表的 Append 或 MergeAppend 节点的子节点。）发生这种情况时，这些计划节点将从 EXPLAIN 输出中省略，并显示 Subplans Removed: N 注释。

参考

using-explain

postgres using explain

https://wuhunyu.top/postgres/2025/07/17/explain/index.html

作者

wuhunyu

发布于

2025-07-17

更新于

2025-07-17

许可协议

#
关注我

目录

最新文章

分类

标签

DataGrip1
Dijkstra 算法1
Docker Hub1
GitHub Actions1
GoLand1
IntelliJ IDEA1
Java1
JetBrains 全家桶1
LeetCode 里程碑1
MyBatis1
×