Posgrseql - Execution Plan

show the execution plan of a statement。

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 togethe r the required rows 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 cost units that are arbitrary, but conventionally mean disk page fetches). Actually two numbers are shown : the start-up cost before the first row can be returned, and the total cost to return all the rows. For most queries the total cost is what matters, but in contexts such as a subquery in  EXISTS , the planner will choose the smallest start-up cost instead of the smallest total cost (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. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node (in milliseconds) and the total number of rows it actually returned. This is useful for seeing whether the planner's estimates are close to reality.

# ANALYZE 选项语句实际执行。将实际运行时统计信息添加到显示器中.

Parameters

ANALYZE

Carry out the command and show actual run times and other statistics. This parameter defaults to  FALSE .

# 实际执行

VERBOSE

Display additional information regarding the plan. Specifically, include the output column list for each node in the plan tree, schema-qualify table and function names, always label variables in expressions with their range table alias, and always print the name of each trigger for which statistics are displayed. This parameter defaults to  FALSE .

# 附加信息。包括计划树中每个节点的输出列列表、架构限定表和函数名，总是用表达式中的变量表别名来标记表达式中的变量，并且总是打印每个统计数据显示的触发器的名称。

COSTS

Include information on the estimated startup and total cost of each plan node, as well as the estimated number of rows and the estimated width of each row. This parameter defaults to  TRUE .

# 每个计划节点的估计启动和总成本的信息，以及估计的行数和每行的估计宽度

BUFFERS

Include information on buffer usage . Specifically, include the number of shared blocks hit, read, dirtied, and written, the number of local blocks hit, read, dirtied, and written, and the number of temp blocks read and written. A  hit  means that a read was avoided because the block was found already in cache when needed. Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases. The number of blocks  dirtied  indicates the number of previously unmodified blocks that were changed by this query; while the number of blocks  written  indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed. This parameter may only be used when  ANALYZE  is also enabled. It defaults to  FALSE .

# 有关缓冲区使用的信息。包括共享块的命中、读取、污损和写入的数量、本地块的命中数、读、写和写入的数量以及读取和写入的临时块的数量。

#共享块包含来自常规表和索引的数据；本地块包含来自临时表和索引的数据；而临时块包含分类使用的短期工作数据、散列、实体化计划节点和类似的情况。

#已删除的块的数目指示由该查询改变的先前未修改的块的数目；而写入的块的数目指示在查询处理期间由后端从缓存释放的先前已被清除的块的数目。

# 上层节点所显示的块的数目包括所有子节点所使用的块。

# 默认为false

TIMING

Include actual startup time and time spent in each node in the output. The overhead of repeatedly reading the system clock can slow down the query significantly on some systems, so it may be useful to set this parameter to  FALSE  when only actual row counts, and not exact times, are needed. Run time of the entire statement is always measured, even when node-level timing is turned off with this option. This parameter may only be used when  ANALYZE  is also enabled. It defaults to  TRUE .

# 包括实际启动时间和在输出中的每个节点中花费的时间。重复读取系统时钟的开销导致在某些系统上显著地减慢查询，因此当只需要实际行计数而不需要精确的时间时，将该参数设置为false是有用的。整个语句的运行时间总是被测量，即使当节点级别的定时被关闭时。只有在启用了分析时，才能使用此参数。它默认为真。

SUMMARY

Include summary information (e.g., totaled timing information) after the query plan. Summary information is included by default when  ANALYZE  is used but otherwise is not included by default , but can be enabled using this option. Planning time in  EXPLAIN EXECUTE  includes the time required to fetch the plan from the cache and the time required for re-planning, if necessary.

# 包括查询计划之后的摘要信息。默认情况下包含摘要信息。 EXPLAIN EXECUTE的计划时间包括从缓存中获取计划所需的时间和重新规划所需的时间。

FORMAT

Specify the output format, which can be TEXT, XML, JSON, or YAML . Non-text output contains the same information as the text output format, but is easier for programs to parse. This parameter defaults to  TEXT .

# 指定输出格式

boolean

Specifies whether the selected option should be turned on or off. You can write  TRUE ,  ON , or  1  to enable the option, and  FALSE ,  OFF , or  0  to disable it. The  boolean  value can also be omitted, in which case  TRUE  is assumed.

statement

Any  SELECT ,  INSERT ,  UPDATE ,  DELETE ,  VALUES ,  EXECUTE ,  DECLARE ,  CREATE TABLE AS , or  CREATE MATERIALIZED VIEW AS  statement, whose execution plan you wish to see.

如何使用：

EXPLAIN SELECT * FROM foo;

#########

EXPLAIN (FORMAT JSON) SELECT * FROM foo;

#########

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

#########

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);

#########