EXPLAIN 命令

常用語句

預生成執行計劃
```
EXPLAIN sq
```
真實執行計劃
```
EXPLAIN ANALYZE sq
```

輸出詳細內容

EXPLAIN (ANALYZE on, TIMING on, VERBOSE on, BUFFERS on, COSTS on) sql

語法

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

選項

analyze：執行語句並顯示真正的執行時間和其它統計資訊，會真正執行SQL語句；
verbose：顯示額外的資訊,尤其是計劃樹中每個節點的欄位列表,schema識別表和函式名稱。總是列印統計資料中顯示的每個觸發器的名字；
costs：包括每個計劃節點的啟動成本預估和總成本的消耗，也包括行數和行寬度的預估；
buffers：使用資訊，特別包括共享塊命中、讀、髒和寫的次數，本地塊命中、讀、髒和寫，臨時塊讀和寫的次數；
timing：在輸出中包含實際啟動時間和每個節點花費的時間，重複讀系統塊在某些系統上會顯著的減緩查詢的速度，只在ANALYZE也啟用的時候使用；
format：宣告輸出格式，可以為TEXT、XML、JSON 或 YAML，預設 text；

EXPLAIN 輸出

cost：第一個數字表示啟動的成本，也就是返回第一行需要多少 cost 值；第二個數字表示返回所有的資料的成本。預設 cost 值如下

順序掃描一個資料塊，cost 值定為 1
隨機掃描一個資料塊，cost 值定為 4
處理一個資料行的 CPU，cost 為 0.01
處理一個索引行的 CPU，cost 為 0.005
每個運算子的 CPU 代價為 0.0025

rows：表示會返回多少行
width：表示每行平均寬度為多少位元組
buffers

shared hit：表示在共享記憶體中直接讀到 xxx 個塊，
read：表示從磁碟讀了 xxx 塊
written：寫磁碟工 xxx 塊

執行計劃含義

Seq Scan：全表掃描
Index Scan：索引掃描
Bitmap Heap Scan：點陣圖掃描
Filter：條件過濾
Nestloop Join：巢狀迴圈連線，是在兩個表做連線時，內表唄外表驅動，外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大，要把返回子集較小的表作為外表，而且在內表的連線欄位上要有索引，否則會很慢。執行過程：

確定一個驅動表（outer table），另一個表為 inner table
驅動表中的每一行與 inner 表中的相應記錄 JOIN 類似一個巢狀的迴圈

Hash Join：使用兩個表中較小的表，並利用連線鍵在記憶體中建立雜湊表，然後掃描較大的表並探測雜湊表，找出與雜湊表匹配的行。適用於較小的表可以完全放入記憶體中的情況。如果表很大，不能完全放入記憶體，最佳化器會將它分割成若干不同的分割槽，把不能放入記憶體的部分寫入磁碟的臨時段。

Merge Join：如果源資料上有索引，或者結果已經被排過序，在執行排序合併連線時就不需要排序了，Merge Join 的效能會優於雜湊連線。

引數

ENABLE_* 引數

引數名稱	型別	說明
enable_seqscan	boolean	是否選擇全表順序掃描。實際上並不能完全禁止全表掃描，但是把這個變數關閉會讓最佳化器在存在其他方法時有限選擇其他方法
enable_indexscan	boolean	是否選擇索引掃描
enable_bitmapscan	boolean	是否選擇點陣圖掃描
enable_tidscan	boolean	是否選擇點陣圖掃描
enable_nestloop	boolean	多表連線時，是否選擇巢狀迴圈連線。如果設定為“off”，執行計劃只有走巢狀迴圈連線一條路時，最佳化器也只能選擇走這一條路，但是如果有其他連線方法可走，最佳化器會優先選擇其他方法。
enable_hashjoin	boolean	多表連線時，是否選擇 hash 連線
enable_mergejoin	boolean	多表連線時，是否選擇 merge 連線
enable_hashagg	boolean	是否使用 hash 聚合
enable_sort	boolean	是否使用明確的排序，如果設定為“off”，執行計劃只有排序一條路時，最佳化器也只能選擇這條路，但是如果有其他連線方法可走，最佳化器會優先選擇其他方法。

COST 基準值引數

引數名稱	型別	說明
seq_page_cost	float	執行計劃中依次順序訪問一個資料塊頁面的開銷。預設值是 1.0
random_page_cost	float	執行計劃中計算隨機訪問一個資料塊頁面的開銷。預設值是 4.0
cpu_tuple_cost	float	執行計劃中計算處理一條資料行的開銷。預設值為 0.01
cpu_index_tuple_cost	float	執行計劃中計算處理一條索引行的開銷。預設為 0.005
cpu_operator_cost	float	執行計劃中執行一個運算子或函式的開銷。預設為 0.0025
effective_cache_size	int	執行計劃中在一次索引掃描中可用的磁碟緩衝區的有效大小。在計算一個索引的預計開銷值時會對這個引數加以考慮。更高的數值會導致更可能使用索引掃描，更低的數值會導致更有可能選擇順序全表掃描。預設是 16384 個資料塊大小，即 128 MB。

基因查詢最佳化的引數

基因查詢最佳化（GEQO）是一個使用探索式搜尋來執行查詢規劃的演算法，它可以降低負載查詢的規劃時間。GEQO 的檢索是隨機的，因此它生成的執行計劃可能會有不確定性。

引數名稱	型別	說明
geqo	integer	允許或禁止基因查詢變化，在生產系統中最好把此引數開啟，預設是開啟的。geqo_threshold 引數提供了一種是否使用基因查詢最佳化方法的更驚喜的控制方法
geqo_threshold	integer	只有當涉及的 FROM 關係數量至少有這麼多個時，才是用基因查詢最佳化。對於數量小於此值的查詢，規劃器做判斷要花很多時間。預設是 12。一個 FULL OUTER JOIN 只算一個 FROM 項。
geqo_effort	integer	控制 GEQO 裡規劃時間和查詢規劃有效性之間的平衡。這個變數必須是一個從 1 到 10 的整數。預設值是 5。大的數值增加花在進行查詢規劃上面的時間，但是也很可能提高選中更有效的查詢規劃的機率。
geqo_pool_size	integer	控制 GEQO 使用的池大小。池大小是基因全體中的個體數量，它必須至少是 2，有用的數值通常在 100 到 1000 之間。如果把它設定為 0（預設值），那麼就會基於 geqo_effort 和查詢中表的數量選取一個合適的值
geqo_generations	integer	控制 GEQO 使用的子代數目。子代的意思是演算法的迭代次數。它必須至少是 1，有用值的範圍和池大小相同。如果設定為 0（預設值），那麼將基於 geqo_effort 選取合適的值。
geqo_selection_bias	float	控制 GEQO 使用的選擇性偏好。選擇性偏好是指在一個種群中的選擇性壓力。數值可以是 1.5 到 2.0 之間，預設值是 2.0
geqo_seed	float	控制 GEQO 使用的隨機數產生器的初始值，用以選擇隨機路徑。這個值可以從 0（預設值）到 1.修改這個值會改變連線路徑搜尋的設定，同時會找到最優或最差路徑

其他執行計劃配置項

引數名稱	型別	說明
default_statistics_target	enum	此引數設定表欄位的預設直方圖統計目標值，如果表欄位的直方圖統計目標值沒有用 ALTER TABLE SET STATISTICS 明確設定過，則使用此引數指定的值。此值越大，ANALYZE 需要花費越多的時間，同時統計出的直方圖資訊越詳細，這樣生成的執行計劃也越準確。預設值是 100，最大值是 10000
constraint_exclusion	float	指定執行計劃中是否使用約束排除。可以取三個值：partition、on、off。預設值為 partition。約束排除就是指最佳化器分析 where 中的過濾條件與表上的 check 約束。當最佳化器使用約束排除時，需要花更多的時間去對比約束條件和 where 中的過濾條件，在多數情況下，對無繼承的表開啟約束排除意義不大，所以 PostgreSQL 把此值預設設定為 partition。當對一張表做查詢時，如果這張表有很多繼承的子表，通常也需要掃描這些子表，設定為“partition”，最佳化器就會對這些子表做約束排除分析
cursor_tuple_fraction	float	遊標在選擇執行計算時有兩種策略：第一種是選擇總體執行代價最低的，第二種是返回第一條記錄時代價最低的。有時總體執行代價最低，但返回第一條記錄到代價不是最低，這是返回給使用者的第一條記錄的時間比較長，這會讓使用者覺得等待較長的時間，系統才有相應，導致使用者體驗不太好。為了讓使用者體驗比較好，可以選擇返回第一條記錄最快的執行計劃，這時使用者可以比較快地看到第一條記錄。設定遊標，在選擇總體代價最低的執行計劃和返回第一條記錄代價最低的執行計劃兩者之間，比較傾向性的大小。預設值是 0.1。最大值是 1.0，此時遊標會選擇總體代價最低的執行計劃，而不考慮多久才會輸出第一個行
from_collapse_limit	integer	預設值是 8。如果查詢重寫生成的 FROM 後的專案數不超過這個限制數目，最佳化器將把子查詢融合到上層查詢。小的數值可縮短規劃的時間，但是可能會生成差一些的執行計劃。將這個值設定得與配置項 geqo_threshold 的數值相同或更大，可能觸發使用 GEQO 規劃器，從而產生不確定的執行計劃
join_collapse_limit	integer	如果查詢重寫生成的 FROM 後的專案數不超過這個限制數目，最佳化器把顯式使用 JOIN 子句（不包括 FULL JOIN）的連線也重寫到 FROM 後的列表中。小的數值可縮短規劃的時間，但是可能會生成差一些的查詢計劃值。預設值與 from_collapse_limit 一樣。將這個值設定得與配置項 geqo_threshold 的數值相同或更大，可能觸發使用 GEQO 規劃器，從而產生不確定的執行計劃

注意

在加上 ANALYZE 選項後，會真正執行實際的 SQL，如果 SQL 語句是一個插入、刪除、更新或 CREATE TABLE AS 語句，這些語句會修改資料庫。為了不影響實際的資料，可以吧 EXPLAIN ANALYZE 放到一個事務中，執行完後回滾事務，如下：

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

PostgreSQL：EXPLAIN