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.