Postgresql 中文操作指南

EXPLAIN

EXPLAIN——显示语句的执行计划

Synopsis

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    GENERIC_PLAN [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

Description

此命令显示 PostgreSQL 规划器针对所提供的语句生成的执行计划。执行计划显示了语句引用的表将如何扫描——通过纯顺序扫描、索引扫描等——如果引用了多个表，将使用哪些连接算法将各个输入表中的必需行组合在一起。

显示中最关键的部分是估计的语句执行成本，这是规划器衡量运行语句需要多长时间的猜测（以任意的成本单位衡量，但按照惯例表示磁盘页面提取）。实际上显示了两个数字：第一个行可以返回之前的启动成本，以及返回所有行的总成本。对于大多数查询，总成本才是重要的事项，但在 EXISTS 中的子查询等上下文中，规划器将选择最小的启动成本而不是最小的总成本（因为执行器在获取一行后无论如何都会停止）。此外，如果您使用 LIMIT 子句限制要返回的行数，则规划器会在端点成本之间进行适当的插值，以估计哪个计划实际上是最便宜的。

ANALYZE 选项导致实际执行该语句，而不仅仅是计划。然后，将实际运行时间统计信息添加到显示中，包括在每个计划节点中实际消耗的总时间（以毫秒为单位）以及它实际返回的行总数。这对于观察规划器的估算是否接近实际情况非常有用。

Important

请记住，当使用 ANALYZE 选项时，会实际执行该语句。虽然 EXPLAIN 会丢弃 SELECT 会返回的任何输出，但语句的其他副作用将照常发生。如果您希望在 INSERT 、 UPDATE 、 DELETE 、 MERGE 、 CREATE TABLE AS 或 EXECUTE 语句中使用 EXPLAIN ANALYZE 而又不让命令影响您的数据，请使用此方法：

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

只能指定 ANALYZE 和 VERBOSE 选项，并且只能按照该顺序指定，而不能用括号括住选项列表。在 PostgreSQL 9.0 之前，未加括号的语法是唯一受支持的语法。预期所有新选项都只能在带括号的语法中受支持。

Parameters

  • ANALYZE

    • 执行命令并显示实际运行时间和其他统计信息。该参数默认为 FALSE 。

  • VERBOSE

    • 显示有关计划的其他信息。具体来说，包括计划树中每个节点的输出列列表、模式限定表和函数名称，始终使用其范围表别名标记表达式中的变量，并始终打印显示统计信息的每个触发器的名称。如果已计算查询标识符，它也将显示，有关更多详细信息，请参见 compute_query_id 。此参数默认为 FALSE 。

  • COSTS

    • 包括每个计划节点的估计启动和总成本的信息，以及估计的行数和每行的估计宽度。此参数默认为 TRUE 。

  • SETTINGS

    • 包括有关配置参数的信息。具体来说，包括影响查询规划并具有与内置默认值不同的值的选项。此参数默认为 FALSE 。

  • GENERIC_PLAN

    • 允许语句包含像 $1 这样的参数占位符，并生成一个不依赖于这些参数值的泛型计划。有关泛型计划和支持参数的语句类型的详细信息，请参见 PREPARE 。该参数不能与 ANALYZE 一起使用。其默认为 FALSE 。

  • BUFFERS

    • 包括有关缓冲区使用情况的信息。具体来说，包括命中、读取、弄脏和写入的共享块的数量、命中、读取、弄脏和写入的本地块的数量、读取和写入的 temp 块的数量以及在 track_io_timing 启用时读取和写入数据文件块和临时文件块所花费的时间（以毫秒为单位）。 hit 表示避免了读取，因为在需要时已在缓存中找到了该块。共享块包含常规表和索引的数据；本地块包含临时表和索引的数据；而临时块包含在排序、哈希、物化计划节点和类似的情况下使用的短期工作数据。块 dirtied 的数量表示此查询更改的以前未修改的块的数量；而块 written 的数量表示在查询处理期间此后端从缓存中驱逐的以前弄脏块的数量。为上层节点显示的块数包括其所有子节点使用的块数。在文本格式中，仅打印非零值。此参数默认为 FALSE 。

  • WAL

    • 包括有关 WAL 记录生成的信息。具体来说，包括记录数、完整页面图像 (fpi) 数以及以字节为单位生成的 WAL 量。在文本格式中，仅打印非零值。该参数仅可在 ANALYZE 也启用时使用。其默认为 FALSE 。

  • TIMING

    • 在输出中包括实际启动时间和在每个节点中花费的时间。在某些系统上，反复读取系统时钟的开销会显著降低查询速度，因此在仅需要实际行计数而不是精确时间时，将此参数设置为 FALSE 可能会很有用。即使使用此选项关闭了节点级别的时间，仍会始终测量整个语句的运行时间。该参数仅可在 ANALYZE 也启用时使用。其默认为 TRUE 。

  • SUMMARY

    • 在查询计划后包括摘要信息（例如，总计时间信息）。当使用 ANALYZE 时默认包含摘要信息，但在其他情况下不默认包含，但可以使用此选项启用。 EXPLAIN EXECUTE 中的计划时间包括从缓存中获取计划所需的时间以及重新计划所需的（如果需要）时间。

  • FORMAT

    • 指定输出格式，可以是 TEXT、XML、JSON 或 YAML。非文本输出包含与文本输出格式相同的信息，但程序更容易解析。此参数默认为 TEXT 。

  • boolean

    • 指定所选选项应该开启还是关闭。您可以编写 TRUE 、 ON 或 1 来启用该选项，并编写 FALSE 、 OFF 或 0 来禁用它。也可以省略 boolean 值，在这种情况下假定 TRUE 。

  • statement

    • 任何 SELECT 、 INSERT 、 UPDATE 、 DELETE 、 MERGE 、 VALUES 、 EXECUTE 、 DECLARE 、 CREATE TABLE AS 或 CREATE MATERIALIZED VIEW AS 语句（您希望查看其执行计划）。

Outputs

该命令的结果是对 statement 选择的计划的文本描述，可选地带有执行统计信息注释。 Section 14.1 描述了所提供的信息。

Notes

为了允许 PostgreSQL 查询规划器在优化查询时做出合理的明智决策， pg_statistic 数据应针对查询中使用的所有表都是最新的。通常 autovacuum daemon 会自动处理。但是，如果某个表的原始内容发生了重大更改，则可能需要进行手动 ANALYZE ，而不是等待自动清理赶上更改。

为了测量执行计划中每个节点的运行时成本， EXPLAIN ANALYZE 的当前实现增加了查询执行的分析开销。因此，对查询运行 EXPLAIN ANALYZE 有时可能比正常执行查询所需的时间长得多。开销量取决于查询的性质以及所使用的平台。最坏的情况发生在本身每次执行所需时间很短的计划节点，以及获取当天时间的操作系统调用相对较慢的机器上。

Examples

要在具有单个 integer 列和 10000 行的表上显示简单查询的计划：

EXPLAIN SELECT * FROM foo;

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)

这是具有 JSON 输出格式的相同查询：

EXPLAIN (FORMAT JSON) SELECT * FROM foo;
           QUERY PLAN
--------------------------------
 [                             +
   {                           +
     "Plan": {                 +
       "Node Type": "Seq Scan",+
       "Relation Name": "foo", +
       "Alias": "foo",         +
       "Startup Cost": 0.00,   +
       "Total Cost": 155.00,   +
       "Plan Rows": 10000,     +
       "Plan Width": 4         +
     }                         +
   }                           +
 ]
(1 row)

如果有索引，并且我们对可索引的 WHERE 条件使用查询，则 EXPLAIN 可能显示不同的计划：

EXPLAIN SELECT * FROM foo WHERE i = 4;

                         QUERY PLAN
--------------------------------------------------------------
 Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
   Index Cond: (i = 4)
(2 rows)

这是相同的查询，但采用 YAML 格式：

EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
          QUERY PLAN
-------------------------------
 - Plan:                      +
     Node Type: "Index Scan"  +
     Scan Direction: "Forward"+
     Index Name: "fi"         +
     Relation Name: "foo"     +
     Alias: "foo"             +
     Startup Cost: 0.00       +
     Total Cost: 5.98         +
     Plan Rows: 1             +
     Plan Width: 4            +
     Index Cond: "(i = 4)"
(1 row)

XML 格式留给读者练习。

以下是隐藏成本估算的同计划：

EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;

        QUERY PLAN
----------------------------
 Index Scan using fi on foo
   Index Cond: (i = 4)
(2 rows)

以下是一个使用聚合函数查询的查询计划示例：

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

                             QUERY PLAN
-------------------------------------------------------------------​--
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)
(3 rows)

以下是使用 EXPLAIN EXECUTE 显示准备查询的执行计划的示例：

PREPARE query(int, int) AS SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);

                                                       QUERY PLAN
-------------------------------------------------------------------​------------------------------------------------------
 HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10 loops=1)
   Group Key: foo
   Batches: 1  Memory Usage: 24kB
   ->  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99 loops=1)
         Index Cond: ((id > 100) AND (id < 200))
 Planning Time: 0.244 ms
 Execution Time: 0.073 ms
(7 rows)

当然，此处显示的具体数字取决于所涉及的表中的实际内容。还要注意，由于计划人员的改进，这些数字，甚至选定的查询策略，可能会在不同的 PostgreSQL 版本之间有所不同。此外， ANALYZE 命令使用随机抽样来估算数据统计信息；因此，即使表中数据的实际分布未发生变化，仍然有可能在重新运行 ANALYZE 之后改变成本估算。

请注意，前面的示例显示了针对 EXECUTE 中给定的特定参数值的一个“自定义”计划。我们可能还希望看到带参数的查询的通用计划，这可以通过 GENERIC_PLAN 实现：

EXPLAIN (GENERIC_PLAN)
  SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;

                                  QUERY PLAN
-------------------------------------------------------------------​------------
 HashAggregate  (cost=26.79..26.89 rows=10 width=12)
   Group Key: foo
   ->  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)
         Index Cond: ((id > $1) AND (id < $2))
(4 rows)

在这种情况下，解析器正确地推断出 $1 和 $2 应与 id 具有相同的数据类型，因此，缺少 PREPARE 的参数类型信息并没有问题。在其他情况下，可能需要为参数符号明确指定类型，可以执行转换，例如：

EXPLAIN (GENERIC_PLAN)
  SELECT sum(bar) FROM test
    WHERE id > $1::integer AND id < $2::integer
    GROUP BY foo;

Compatibility

SQL 标准中未定义 EXPLAIN 语句。

See Also

ANALYZE