NAME
EXPLAIN - 显示语句执行规划
SYNOPSIS
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
DESCRIPTION 描述
这条命令显示PostgreSQL规划器为所提供的语句生成的执行规划。 执行规划显示语句引用的表是如何被扫描的--- 是简单的顺序扫描，还是索引扫描等 --- 并且如果引用了多个表， 采用了什么样的连接算法从每个输入的表中取出所需要的记录。
显示出来的最关键的部分是预计的语句执行开销， 这就是规划器对运行该语句所需时间的估计（以磁盘页面存取为单位计量）。 实际上显示了两个数字：返回***行记录前的启动时间， 和返回所有记录的总时间。对于大多数查询而言，关心的是总时间，但是， 在某些环境下，比如一个 EXISTS 子查询里， 规划器将选择最小启动时间而不是最小总时间（因为执行器在获取一条记录后总是要停下来）。 同样，如果你用一条 LIMIT 子句限制返回的记录数， 规划器会在最终的开销上做一个合理的插值以计算哪个规划开销最省。
ANALYZE 选项导致查询被实际执行，而不仅仅是规划。 它在显示中增加了在每个规划节点内部花掉的总时间（以毫秒计）和它实际返回的行数。 这些数据对搜索该规划器的预期是否和现实相近很有帮助。
- Important:
要记住的是查询实际上在使用 ANALYZE 的时候是执行的。 尽管 EXPLAIN 会抛弃任何 SELECT 会返回的输出， 但是其它查询的副作用还是一样会发生的。 如果你在 INSERT，UPDATE，DELETE，或者 EXECUTE 语句里使用 EXPLAIN ANALYZE，而且还不想让查询影响你的数据， 用下面的方法：BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
PARAMETERS 参数
- ANALYZE
执行命令并显示实际运行时间。- VERBOSE
显示规划树完整的内部表现形式，而不仅仅是一个摘要。通常，这个选项只是在调试 PostgreSQL 的时候有用。 VERBOSE 输出可能是打印得工整的，也可能不是， 具体取决于配置参数 explain_pretty_print。- statement
任何 SELECT, INSERT, UPDATE, DELETE, EXECUTE, 或 DECLARE 语句。
NOTES 注意
在 PostgreSQL 里只有很少的一些文档介绍有关优化器计算开销的问题。参考 Section 13.1 ``Using EXPLAIN'' 获取更多信息。
为了让 PostgreSQL 查询规划器在优化查询的时候做出合理的判断， 我们需要运行 ANALYZE 语句以记录有关数据在表中的分布的统计信息。 如果你没做过这件事情（或者如果自上次 ANALYZE 以来， 表中的数据统计分布发生了显著变化），那么计算出来的开销预计很可能与查询的实际属性并不对应， 因此很可能会选取一个比较差的查询规划。
在 PostgreSQL 7.3 以前，查询规划是以 NOTICE 消息的形式发出来的。 现在它的显示格式是一个查询结果（格式化成了类似一个有单个文本字段的表。）
EXAMPLES 例子
显示一个对只有一个 int4 列和 10000 行的表的简单查询的查询规划：
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 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)
下面是一个使用了聚集函数的查询的查询规划：
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=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
-> Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
Index Cond: ((id > $1) AND (id < $2))
Total runtime: 0.851 ms
(4 rows)
注意这里显示的数字， 甚至还有选择的查询策略都有可能在各个 PostgreSQL版本之间不同--因为规划器在不断改进。 另外，ANALYZE 命令使用随机的采样来估计数据统计； 因此，一次新的 ANALYZE 运行之后开销估计可能会变化， 即使数据的实际分布没有改变也这样。
COMPATIBILITY 兼容性
在 SQL 标准中没有EXPLAIN 语句。
#p#
NAME
EXPLAIN - show the execution plan of a statement
SYNOPSIS
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
DESCRIPTION
This command displays the execution plan that the PostgreSQL planner generates for the supplied statement. The execution plan shows how the table(s) referenced by the statement will be scanned---by plain sequential scan, index scan, etc.---and if multiple tables are referenced, what join algorithms will be used to bring together the required row from each input table.
The most critical part of the display is the estimated statement execution cost, which is the planner's guess at how long it will take to run the statement (measured in units of disk page fetches). Actually two numbers are shown: the start-up time before the first row can be returned, and the total time to return all the rows. For most queries the total time is what matters, but in contexts such as a subquery in EXISTS, the planner will choose the smallest start-up time instead of the smallest total time (since the executor will stop after getting one row, anyway). Also, if you limit the number of rows to return with a LIMIT clause, the planner makes an appropriate interpolation between the endpoint costs to estimate which plan is really the cheapest.
The ANALYZE option causes the statement to be actually executed, not only planned. The total elapsed time expended within each plan node (in milliseconds) and total number of rows it actually returned are added to the display. This is useful for seeing whether the planner's estimates are close to reality.
- Important: Keep in mind that the statement is actually executed when ANALYZE is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, or EXECUTE statement without letting the command affect your data, use this approach:
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
PARAMETERS
- ANALYZE
- Carry out the command and show the actual run times.
- VERBOSE
- Show the full internal representation of the plan tree, rather than just a summary. Usually this option is only useful for debugging PostgreSQL. The VERBOSE output is either pretty-printed or not, depending on the setting of the explain_pretty_print configuration parameter.
- statement
- Any SELECT, INSERT, UPDATE, DELETE, EXECUTE, or DECLARE statement, whose execution plan you wish to see.
NOTES
There is only sparse documentation on the optimizer's use of cost information in PostgreSQL. Refer to the section called ``Using EXPLAIN'' in the documentation for more information.
In order to allow the PostgreSQL query planner to make reasonably informed decisions when optimizing queries, the ANALYZE statement should be run to record statistics about the distribution of data within the table. If you have not done this (or if the statistical distribution of the data in the table has changed significantly since the last time ANALYZE was run), the estimated costs are unlikely to conform to the real properties of the query, and consequently an inferior query plan may be chosen.
Prior to PostgreSQL 7.3, the plan was emitted in the form of a NOTICE message. Now it appears as a query result (formatted like a table with a single text column).
EXAMPLES
To show the plan for a simple query on a table with a single integer column and 10000 rows:
EXPLAIN SELECT * FROM foo;
QUERY PLAN
---------------------------------------------------------
Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
(1 row)
If there is an index and we use a query with an indexable WHERE condition, EXPLAIN might show a different plan:
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)
And here is an example of a query plan for a query using an aggregate function:
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)
Here is an example of using EXPLAIN EXECUTE to display the execution plan for a prepared query:
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=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
-> Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
Index Cond: ((id > $1) AND (id < $2))
Total runtime: 0.851 ms
(4 rows)
Of course, the specific numbers shown here depend on the actual contents of the tables involved. Also note that the numbers, and even the selected query strategy, may vary between PostgreSQL releases due to planner improvements. In addition, the ANALYZE command uses random sampling to estimate data statistics; therefore, it is possible for cost estimates to change after a fresh run of ANALYZE, even if the actual distribution of data in the table has not changed.
COMPATIBILITY
There is no EXPLAIN statement defined in the SQL standard.
