一、explain命令詳解

1、語法

EXPLAIN [ ( option [, ...] ) ] statement

EXPLAIN [ ANALYZE ] [ VERBOSE ] statemen

2、選項說明

option可以有以下選項

analyze：執行語句並顯示真正的執行時間和其它統計資訊，會真正執行SQL語句；

verbose：顯示額外的資訊,尤其是計劃樹中每個節點的欄位列表,schema識別表和函式名稱。總是列印統計資料中顯示的每個觸發器的名字；

costs：包括每個計劃節點的啟動成本預估和總成本的消耗，也包括行數和行寬度的預估；

buffers：使用資訊，特別包括共享塊命中、讀、髒和寫的次數，本地塊命中、讀、髒和寫，臨時塊讀和寫的次數；

timing：在輸出中包含實際啟動時間和每個節點花費的時間，重複讀系統塊在某些系統上會顯著的減緩查詢的速度，只在ANALYZE也啟用的時候使用；

format：宣告輸出格式，可以為TEXT、XML、JSON 或 YAML，預設 text；

二、explain輸出詳解

1、計劃解讀

a=> explain analyze select * from pgbench_accounts ;

QUERY PLAN

Seq Scan on pgbench_accounts (cost=0.00..2688.00 rows=100000 width=97) (actual time=0.267..34.302 rows=100000 loops=1)

Planning Time: 0.165 ms

Execution Time: 46.280 ms

cost=0.00..2688.00，0.00代表啟動成本；2688.00代表返回所有資料的成本；rows=100000表示返回多少行；width=97，表示每行平均寬度(以位元組計算)；actual time=0.267..34.302，實際花費的時間；loops=1，迴圈的次數；

Planning Time，生成執行計劃的時間；

Execution Time，執行執行計劃的時間。

2、輸出引數詳細說明

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

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

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

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

執行計劃含義

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

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

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

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

3、解讀原則

①從下往上讀
②從右至左讀
③開啟時間消耗是輸出開始前的時間例如排序的時間
④消耗包括磁碟檢索頁，cpu時間
⑤注意，每一步的cost包括上一步的

⑥重要的是，explain 不是真正的執行一次查詢只是得到查詢執行的計劃和估計的花費

4、其他舉例說明

下面是一個hash，hash join例子：
=# EXPLAIN SELECT relname, nspname FROM pg_class JOIN pg_namespace ON (pg_class.relnamespace=pg_namespace.oid);
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=1.06..10.71 rows=186 width=128)
Hash Cond: ("outer".relnamespace = "inner".oid)
     -> Seq Scan on pg_class (cost=0.00..6.86 rows=186 width=68)
     -> Hash (cost=1.05..1.05 rows=5 width=68)
        -> Seq Scan on pg_namespace (cost=0.00..1.05 rows=5 width=68)
兩個表間INNER JOIN和LEFT OUTER JOIN 連線的時候，這個運算是很常用的。這個運算是先把外表中關聯條件部分做一個雜湊表，然後去和內部表關聯。

首先在表 pg_namespace上做了一次順序掃描，之後進行hash運算形成一個hash表，之後再pg_class表上進行順序掃描，再進行hash join操作，hash join操作的條件為 "outer".relnamespace = "inner".oid。

如何檢視SQL的執行計劃

執行計劃含義

相關文章