使用EXPLAIN语句显示语句的执行计划 - 云原生数据库 PolarDB

本文介绍了EXPLAIN语法的简介、使用方法以及示例等内容。

语法

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

    这里 option可以是：

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

简介

该命令显示PolarDB计划器提供的语句所生成的执行计划。该执行计划会显示如何扫描语句中引用的表，包括普通的顺序扫描、索引扫描等，以及在引用多个表时使用何种连接算法来把来自每个输入表的行连接在一起。

显示中最重要的部分是估计出的语句执行代价，它是计划器对于该语句要运行多久的猜测（以任意的代价单位度量，但是习惯上表示取磁盘页面的次数）。事实上会显示两个数字：在第一行能被返回前的启动代价，以及返回所有行的总代价。对于大部分查询来说，总代价是最重要的，但是在一些情景中（如EXISTS中的子查询），计划器将选择更小的启动代价来代替最小的总代价（因为执行器将在得到一行后停止）。此外，如果你用一个LIMIT子句限制返回行的数量，计划器会在终端代价之间做出适当的插值来估计到底哪个计划是真正代价最低的。

ANALYZE选项导致该语句被实际执行，而不仅仅是被计划。那么实际的运行时间统计会被显示出来，包括在每个计划节点上花费的总时间（以毫秒计）以及它实际返回的行数。这对观察计划器的估计是否与实际相近很有用。

说明

当使用了ANALYZE选项时语句会被实际执行。尽管EXPLAIN将丢弃SELECT所返回的任何输出，照例该语句的其他副作用还是会发生。如果你希望在INSERT、UPDATE、DELETE、CREATE TABLE AS或者EXECUTE上使用EXPLAIN ANALYZE而不希望它们影响你的数据，可以使用下面的方法：

 BEGIN;
    EXPLAIN ANALYZE ...;
    ROLLBACK;

只有ANALYZE和VERBOSE选项能被指定，并且必须按照上述的顺序，不要把选项列表放在括号内。

参数说明

ANALYZE：执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。
VERBOSE：显示关于计划的额外信息。特别是计划树中每个节点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。这个参数默认被设置为FALSE。
COSTS：包括每一个计划节点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。
SETTINGS：包括有关配置参数的信息。具体来说，包括影响查询计划的选项，其值与内置默认值不同。此参数默认为FALSE。
BUFFERS：包括缓冲区使用的信息。特别是共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、临时块读取和写入的次数以及 trackio_timing 启用时读取和写入数据文件块所用的时间（毫秒）。一次命中表示避免了一次读取，因为需要的块已经在缓存中找到了。共享块包含着来自于常规表和索引的数据；本地块包含着来自于临时表和索引的数据；而临时块包含着在排序、哈希、物化计划节点和类似情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入_块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。为一个较高层结点显示的块数包括它的所有子节点所用到的块数。在文本格式中，只会打印非零值。它默认为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、VALUES、EXECUTE、DECLARE、CREATE TABLE AS或者CREATE MATERIALIZED VIEW AS语句。

输出

这个命令的结果是为statement选中的计划的文本描述，可能还标注了执行统计信息。

说明

为了允许PolarDB查询计划器在优化查询时能做出合理的知情决策，查询中用到的所有表的pg_statistic数据应该能保持为最新。通常这个工作会由 autovacuum daemon负责自动完成。但是如果一个表最近在内容上有大量的改变，我们可能需要做一次手动的ANALYZE而不是等待autovacuum捕捉这些改变。

为了度量执行计划中每个节点的运行时成本，当前的EXPLAIN ANALYZE的当前实现为查询执行增加了性能分析开销。这样，在一个查询上运行EXPLAIN ANALYZE有时候比正常执行该查询要慢很多。开销的量取决于该查询的性质，以及使用的平台。最坏的情况会发生在那些自身执行时间很短的节点上，以及在那些具有相对较慢的有关时间的操作系统调用的机器上。

示例

具有单个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)

去掉了代价估计的同一个计划：

    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=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)
       Group Key: foo
       ->  Index Scan using test_pkey on test  (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)
             Index Cond: ((id > $1) AND (id < $2))
     Planning time: 0.197 ms
     Execution time: 0.225 ms
    (6 rows)

当然，这里显示的有关数字取决于表涉及到的实际内容。此外，ANALYZE命令使用随机采样来估计数据统计。因此，在新的ANALYZE运行之后，代价估计可能会改变，即便是表中数据的实际分布没有改变也是如此。