======= EXPLAIN ======= .. container:: refentry :name: SQL-EXPLAIN .. container:: titlepage .. container:: refnamediv .. rubric:: EXPLAIN :name: explain EXPLAIN — 显示一个语句的执行计划 .. container:: refsynopsisdiv .. rubric:: 大纲 :name: 大纲 .. code:: synopsis EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statement 这里 option可以是： ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] SETTINGS [ boolean ] BUFFERS [ boolean ] TIMING [ boolean ] SUMMARY [ boolean ] FORMAT { TEXT | XML | JSON | YAML } .. container:: refsect1 :name: id-1.9.3.148.7 .. rubric:: 描述 :name: 描述 这个命令显示PostgreSQL计划器为提供的语句所生成的执行计划。该执行计划会显示将怎样扫描语句中引用的表 — 普通的顺序扫描、索引扫描等等 — 以及在引用多个表时使用何种连接算法来把来自每个输入表的行连接在一起。 显示中最重要的部分是估计出的语句执行代价，它是计划器对于该语句要运行多久的猜测（以任意的代价单位度量，但是习惯上表示取磁盘页面的次数）。事实上会显示两个数字：在第一行能被返回前的启动代价，以及返回所有行的总代价。对于大部分查询来说总代价是最重要的，但是在一些情景中（如\ ``EXISTS``\ 中的子查询），计划器将选择更小的启动代价来代替最小的总代价（因为执行器将在得到一行后停止）。此外，如果你用一个\ ``LIMIT``\ 子句限制返回行的数量，计划器会在终端代价之间做出适当的插值来估计到底哪个计划是真正代价最低的。 ``ANALYZE``\ 选项导致该语句被实际执行，而不仅仅是被计划。那么实际的运行时间统计会被显示出来，包括在每个计划结点上花费的总时间（以毫秒计）以及它实际返回的行数。这对观察计划器的估计是否与实际相近很有用。 .. important:: .. rubric:: 重要 :name: 重要 :class: title 记住当使用了\ ``ANALYZE``\ 选项时语句会被实际执行。尽管\ ``EXPLAIN``\ 将丢弃\ ``SELECT``\ 所返回的任何输出，照例该语句的其他副作用还是会发生。如果你希望在\ ``INSERT``\ 、\ ``UPDATE``\ 、\ ``DELETE``\ 、\ ``CREATE TABLE AS``\ 或者\ ``EXECUTE``\ 上使用\ ``EXPLAIN ANALYZE``\ 而不希望它们影响你的数据，可以使用下面的方法： .. code:: programlisting BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; 只有\ ``ANALYZE``\ 和\ ``VERBOSE``\ 选项能被指定，并且必须按照上述的顺序，不要把选项列表放在圆括号内。在PostgreSQL 9.0 之前，只支持没有圆括号的语法。我们期望所有新的选项将只在圆括号语法中支持。 .. container:: refsect1 :name: id-1.9.3.148.8 .. rubric:: 参数 :name: 参数 .. container:: variablelist ``ANALYZE`` 执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为\ ``FALSE``\ 。 ``VERBOSE`` 显示关于计划的额外信息。特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。这个参数默认被设置为\ ``FALSE``\ 。 ``COSTS`` 包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为\ ``TRUE``\ 。 ``SETTINGS`` 包括有关配置参数的信息。具体来说，包括影响查询计划的选项，其值与内置默认值不同。此参数默认为\ ``FALSE``\ 。 ``BUFFERS`` 包括缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。一次\ *命中*\ 表示避免了一次读取，因为需要的块已经在缓存中找到了。共享块包含着来自于常规表和索引的数据，本地块包含着来自于临时表和索引的数据，而临时块包含着在排序、哈希、物化计划结点和类似情况中使用的短期工作数据。\ *脏*\ 块的数量表示被这个查询改变的之前未被修改块的数量，而\ *写入*\ 块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。为一个较高层结点显示的块数包括它的所有子结点所用到的块数。在文本格式中，只会打印非零值。只有当\ ``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``\ 语句。 .. container:: refsect1 :name: id-1.9.3.148.9 .. rubric:: 输出 :name: 输出 这个命令的结果是为\ ``statement``\ 选中的计划的文本描述，可能还标注了执行统计信息。\ `节 `__\ 描述了所提供的信息。 .. container:: refsect1 :name: id-1.9.3.148.10 .. rubric:: 注解 :name: 注解 为了允许PostgreSQL查询计划器在优化查询时能做出合理的知情决策，查询中用到的所有表的\ `pg_statistic `__\ 数据应该能保持为最新。通常这个工作会由\ `autovacuum daemon `__\ 负责自动完成。但是如果一个表最近在内容上有大量的改变，我们可能需要做一次手动的\ `ANALYZE `__\ 而不是等待 autovacuum 捕捉这些改变。 为了度量执行计划中每个节点的运行时成本，当前的\ ``EXPLAIN ANALYZE``\ 的当前实现为查询执行增加了性能分析开销。 这样，在一个查询上运行\ ``EXPLAIN ANALYZE``\ 有时候比正常执行该查询要慢很多。 开销的量取决于该查询的性质，以及使用的平台。最坏的情况会发生在那些自身执行时间很短的结点上，以及在那些具有相对较慢的有关时间的操作系统调用的机器上。 .. container:: refsect1 :name: id-1.9.3.148.11 .. rubric:: 例子 :name: 例子 有一个具有单个\ ``integer``\ 列和 10000 行的表，要显示在其上的一个简单查询的计划： .. code:: programlisting EXPLAIN SELECT * FROM foo; QUERY PLAN --------------------------------------------------------- Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4) (1 row) 这里有同样一个查询的 JSON 输出格式： .. code:: programlisting 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``\ 可能会显示一个不同的计划： .. code:: programlisting 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 格式： .. code:: programlisting 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 格式我们留给读者做练习。 这里是去掉了代价估计的同样一个计划： .. code:: programlisting EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4; QUERY PLAN ---------------------------- Index Scan using fi on foo Index Cond: (i = 4) (2 rows) 这里是一个使用聚集函数的查询的查询计划例子： .. code:: programlisting 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``\ 显示预备查询的执行计划的例子： .. code:: programlisting 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) 当然，这里显示的有关数字取决于表涉及到的实际内容。还要注意这些数字甚至选中的查询策略，可能在PostgreSQL的不同版本之间变化，因为计划器可能被改进。此外，\ ``ANALYZE``\ 命令使用随机采样来估计数据统计。因此，在一次新的\ ``ANALYZE``\ 运行之后，代价估计可能会改变，即便是表中数据的实际分布没有改变也是如此。 .. container:: refsect1 :name: id-1.9.3.148.12 .. rubric:: 兼容性 :name: 兼容性 在 SQL 标准中没有定义\ ``EXPLAIN``\ 语句。 .. container:: refsect1 :name: id-1.9.3.148.13 .. rubric:: 参见 :name: 参见 `ANALYZE `__