簡介

執(zhí)行計劃功能展示了SQL在執(zhí)行的過程中走向、成本以及命中情況。主要作用于SQL調(diào)優(yōu)，輸出SQL執(zhí)行的詳細信息，有利于調(diào)優(yōu)人員及時分析性能下降原因。

1. 語法

EXPLAIN [statement]
EXPLAIN [option] [statement]
EXPLAIN [all_option] [statement]

1.1. 參數(shù)選項

analyze
    -執(zhí)行真實的SQL，除估算成本外，額外輸出一項實際結(jié)果
verbose
    -輸出每個結(jié)點的詳細信息
costs
    -輸出估算成本(默認開啟)
buffers  --(前提：必須打開analyze)
    -輸出緩存使用信息命中率、臟數(shù)據(jù)、寫，包含：共享塊、本地塊、臨時塊
format { text | xml | json | yaml }
    -指定輸出格式

若想查詢DDL真實語句成本，可以利用事務(wù)回滾方法來使用執(zhí)行計劃。例如：

--開始事務(wù)
BEGIN;
--DDL語句執(zhí)行查詢計劃
    EXPLAIN ANALYZE UPDATE [table] SET id = id + 1;
--回滾事務(wù)
ROLLBACK;

事務(wù)回滾后并不會更新真正的數(shù)據(jù)，這樣就能達到既想查看DDL語句的真實成本，又不會改變數(shù)據(jù)的好處。

2. 查看執(zhí)行計劃

執(zhí)行計劃輸出結(jié)構(gòu)示意圖：

2.1. 整體結(jié)構(gòu)解析

執(zhí)行計劃的結(jié)構(gòu)是怎樣的？

• 按樹形結(jié)構(gòu)劃分，每層分支用節(jié)點來表示，每個結(jié)點表示SQL在這一階段做了什么。
• 紅框表示第1層，藍框表示第2層，綠框表示第3層，黃框表示第4層。
  • 每一層都標識了掃描方式、估算成本、輸出字段、內(nèi)存命中。

怎么查看執(zhí)行計劃的結(jié)構(gòu)？

• 從下到上：每個箭頭表示1個節(jié)點。上一個節(jié)點的輸入信息來自于它的下一個節(jié)點，所以需要從最下面開始分析，依次讀到最頂層。
• 從里往外：由于上層結(jié)果是下層結(jié)果的輸出，所以在理清層次結(jié)構(gòu)后，需要先分析最里層，再依次往外分析。

例如：最里層的啟動成本是0.00，結(jié)束成本是10.00；那么上一層可能就是啟動成本從10.00開始，結(jié)束成本大于10.00。以下示意圖中藍框就是最好的例子：

紅框：每個節(jié)點的開頭說明

• HashAggregate：表示走的hash聚合函數(shù)
• Seq Scan：表示全部掃描

藍框：該節(jié)點的估算成本

• cost=1.92..1.95：表示啟動成本1.92，結(jié)束成本1.95
• rows：該語句返回的行數(shù)
• width：該行的平均字節(jié)數(shù)

綠框：該節(jié)點的真實成本

• time=0.087..0.093：表示啟動成本0.087，結(jié)束成本0.093
• rows：該節(jié)點返回的行數(shù)
• loops：該節(jié)點循環(huán)次數(shù)

其他：該節(jié)點的詳細信息

• Output：該節(jié)點輸出的sql語句
• Batches：該節(jié)點內(nèi)存使用大小
• Buffers：內(nèi)存命中率

簡單說明

                                          QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=1.92..1.95 rows=3 width=21) (actual time=0.100..0.102 rows=2 loops=1)
 'hash聚合掃描'  '(估算成本=啟動成本..結(jié)束成本 , 執(zhí)行SQL返回的行數(shù) , 每行平均字節(jié)數(shù))'  '(實際成本)'
   Output: count(*), "position"
     '表示該節(jié)點執(zhí)行的字段'
   Group Key: pay_scale."position"
     '表示該節(jié)點執(zhí)行的字段'
   Batches: 1  Memory Usage: 24kB
     '表示該節(jié)點內(nèi)存使用大小'
   Buffers: shared hit=7 read=10
     '表示共享內(nèi)存中有7個命中塊，10個未命中(可能在系統(tǒng)緩存中命中的)'

2.2. 各個節(jié)點說明

① 掃描節(jié)點

順序掃描（seq scan）

• 控制參數(shù)：enable_seqscan = on
• 解釋說明：根據(jù)實際的數(shù)據(jù)存取順序，連續(xù)掃描所有數(shù)據(jù)。（多用于無索引的情況下）

適用情況：

• 一般為數(shù)據(jù)量小、且選擇率高的表。
• 1000條數(shù)據(jù)以下，select 查出結(jié)果大于500條

索引掃描（Index scan）

• 控制參數(shù)：enable_indexscan = on
• 解釋說明：根據(jù)查詢條件掃描索引。因為索引是有序的，所以采用對半查找方式，快速找到符合條件的索引數(shù)據(jù)。再過濾條件和索引鍵值進行比較。

適用情況：

• 一般數(shù)據(jù)量大，但選擇率較低的表。
• 1w條數(shù)據(jù)以上，select 查出結(jié)果低于實際條數(shù)的20%。

注意情況：

• 如果索引條件不存在（選擇率非常高），性能會嚴重下降，甚至不如全表掃描。

位圖掃描（Bitmap scan）

• 控制參數(shù)：enable_bitmapscan = on
• 解釋說明：
  • 1、先通過Bitmap Index Scan索引掃描，在內(nèi)存中創(chuàng)建一個位圖表，每個bit表示一個與過濾條件有關(guān)的頁面（此頁面有可能數(shù)據(jù)為1，不可能為0）。
  • 2、再通過Bitmap Heap Scan表掃描，在內(nèi)存中創(chuàng)建好的位圖表指針對應(yīng)的頁面進行順序掃描，排除不符合的記錄，返回需要的結(jié)果。

適用情況：

• 列中含有重復(fù)值。
• 查詢中包含and、or等范圍性查找。

TID掃描（TID Scan）

• 控制參數(shù)：enable_tidscan = on
• 解釋說明：根據(jù)數(shù)據(jù)實際存儲位置的ctid進行掃描，獲取元組。通過隱藏字段ctid掃描時標記數(shù)據(jù)位置的字段，通過這個字段來查找數(shù)據(jù)（速度較快）

適用情況：

• where條件中帶ctid的表。

覆蓋索引掃描（Index Only Scan）

• 控制參數(shù)：enable_indexonlyscan = on
• 解釋說明：允許直接從索引得到元組。覆蓋索引掃描要求查詢中的某個表，所需要數(shù)據(jù)均可從這張表的同一索引的索引頁面中獲得。

適用情況：

• 更新少的表

其他掃描節(jié)點

Sample Scan

數(shù)據(jù)取樣功能，支持查詢返回取樣數(shù)據(jù)。

• 當前只在常規(guī)表和物化視圖上接受tables ample子句。

Subquery Scan

以另一個查詢計劃樹（子計劃）為掃描對象進行元組掃描，其掃描過程最終被轉(zhuǎn)換為子計劃的執(zhí)行。

• 主要包含：exists、in、not in、any/some、all。

Function Scan

掃描對象為：婦女會元組集的函數(shù)。

• 該節(jié)點在Scan的基礎(chǔ)上擴展定義了function列表字段，存放Function Scan涉及的函數(shù)，以及funcordinality字段，是否返回結(jié)果加上序號列。

Valies Scan

values計算 由值表達式指定一個行值或一組行值。

• 常見的：把它用來生成一個大型命令內(nèi)的常量表，但是它也可以被獨自使用。

CTE Scan

with提供了一種方式來書寫大型查詢中使用的輔助語句，這些語句通常被稱為公共表達式或CTE，它可以被看成是被定義在一個查詢中存在的臨時表。

• with子句中的每個輔助語句可以是：select、insert、update、delete。
• with子句本身也可以被附加到一個主語句，主語句也可以是select、insert、update、delete。

WorkTable Scan

它與Recursive Union共同完成遞歸合并子查詢。

Foreign Scan

掃描外部表，用于fdw或dblink和外部數(shù)據(jù)的交互情況。

Custom Scan

自定義掃描接口

② 連接節(jié)點

嵌套循環(huán)連接（Nest Loop join）

• 控制參數(shù)：enable_nestloop = on
• 解釋說明：掃描每條外表數(shù)據(jù)（m條），再與內(nèi)表中所有的記錄（n條）去連接。時間復(fù)雜度為：m * n。

適用情況：數(shù)據(jù)量小的表。

哈希連接（Hash join）

• 控制參數(shù)：enable_hashjoin = on
• 解釋說明：對內(nèi)表建立hash表，掃描所有內(nèi)表數(shù)據(jù)到各個hash桶；再建立hash桶逐個掃描外表每一行，對外表數(shù)據(jù)進行hash到某個桶，再與這個桶里的數(shù)據(jù)進行連接。

適用情況：數(shù)據(jù)分布隨機，重復(fù)值不多的表。

歸并連接（Merge join）

• 控制參數(shù)：enable_mergejoin = on
• 解釋說明：先對兩張表排序，再做連接。

適用情況：兩張表的數(shù)據(jù)都是有序的。

③ 物化節(jié)點

• 說明：物化節(jié)點是一類可緩存元組的節(jié)點。在執(zhí)行過程中，很多擴展的物理操作符需要先獲取所有元組后才能操作，這時就需要用物化節(jié)點將元組緩存（例如：聚合函數(shù)、無索引的排序）。

物化節(jié)點（Material）

• 控制參數(shù)：enable_material = on
• 解釋說明：用戶緩存子節(jié)點結(jié)果。對于需要重復(fù)多次掃描的子節(jié)點，可以減少執(zhí)行的代價。

適用情況：結(jié)果在子查詢中會被多次使用。

分組節(jié)點（3種情況）

1、Hash Aggregate

• 控制參數(shù)：enable_hashagg = on
• 解釋說明：通過hash算法，把相同的值hash到同一桶中，再求聚集。

適用情況：數(shù)據(jù)無序的表。

2、Group Aggregate

• 解釋說明：通過排序的方式進行分組，再求聚集。

適用情況：數(shù)據(jù)有序的表。

3、Aggregate

• 解釋說明：執(zhí)行含有聚集函數(shù)的group by操作，其中有3種策略：

• Plain：不分組的聚集計算。
• Sorted：下層節(jié)點提供排好序的元組（類似group方法）。
• Hash：先對下層節(jié)點提供的末排序元組進行分組，再計算。

適用情況：含有聚集函數(shù)的group by操作。

排序節(jié)點（Sort）

• 控制參數(shù)：enable_sort = on
• 解釋說明：對數(shù)據(jù)進行排序。

適用情況：輸出結(jié)果是有序的情況。

去重節(jié)點（Unique）

• 解釋說明：對下層節(jié)點返回已排序的元組進行去重。

適用情況：查詢中帶distinct關(guān)鍵字（當要求去重的屬性被order by子句引用時，一般會使用該節(jié)點）。

其他物化節(jié)點

Window Agg

• 窗口函數(shù)

T_SetOp

• setop語法節(jié)點

Lock Rows

• 使用鎖定子句（for update、for share）

Limit

• 使用limit時的節(jié)點

④ 控制節(jié)點

BitmapAnd / BitmapOr節(jié)點

• 解釋說明：這兩個節(jié)點實現(xiàn)了2個或多個位圖的and和or運算（將產(chǎn)生每一個位圖的子計劃放在一個鏈表中，在執(zhí)行過程中先執(zhí)行子計劃節(jié)點獲取位圖，再進行and / or操作）。

適用情況：2種節(jié)點都是位圖類型，用于位圖計算。

Result節(jié)點

• 解釋說明：執(zhí)行計劃不需要掃描表，執(zhí)行器會直接計算select的投影屬性，或使用values子句構(gòu)造元組。

適用情況：針對那些不掃描的查詢，用來優(yōu)化包含僅需計算一次的過濾條件。

Append節(jié)點

• 解釋說明：該節(jié)點會逐個處理這些子計劃，當一個子計劃返回所有結(jié)果后，會接著執(zhí)行鏈表中的下一個子計劃，直到全部執(zhí)行完。

適用情況：用于處理包含一個或多個子計劃的鏈表。

Recursive Union節(jié)點

• 解釋說明：對節(jié)點遞歸進行處理。

適用情況：用于處理遞歸定義的union語句。

⑤ 并行節(jié)點

并行全表掃描（Parallel SeqScan）

當表數(shù)據(jù)量大、選擇率低時，自動使用并行。

當表數(shù)據(jù)量大、選擇率高時，不自動使用并行（大于50%）。

并行hash（Parallel Hash）

例如使用hash join

• 每個worker都是先掃描小表score計算hash，再并行掃描大表，最后做hash。將數(shù)據(jù)匯總到gather節(jié)點合并最終結(jié)果集。

并行嵌套（Parallel NestedLoop）

支持并行嵌套查詢

• 如果不開啟并行，普通的hash join性能會比開啟低很多。

也支持其他并行

• Gather / Gather Merge
• 并行聚集（Partial / Finalize Aggregate / HashAggregate / GroupAggregate）
• 并行排序（Gather Merge）
• 并行B-Tree索引掃描（B-tree Index Scan）
• 并行Bitmap掃描（Bitmap Heap Scan）
• 并行Append（Parallel Append）
• 并行Union（Parallel Union）

開啟并行的參數(shù)

max_worker_processes（默認8）

• OS支持的最大后臺進程數(shù)

max_parallel_workers（默認8）

• 最大并行worker數(shù)

max_parallel_workers_per_gather（默認2）

• 最大并行執(zhí)行worker數(shù)

max_parallel_maintenance_workers（默認2）

• 最大并行維護worker數(shù)

它們之間的配置關(guān)系：

• 以 max_worker_processes 為主配置
• max_parallel_workers 不能超過主配置數(shù)量
• max_parallel_workers_per_gather 和 max_parallel_maintenance_workers 相加的值也不能超過主配置數(shù)量

并行的觸發(fā)條件

表

• 表的存儲空間至少大于 min_parallel_table_scan_size（默認 8MB）

索引

• 索引的存儲空間至少大于 min_parallel_index_scan_size （默認512KB）

查詢某張表的索引大小

SELECT pg_size_pretty( pg_relation_size('[表名]'));

并行注意項

• 在開啟并行時，并不是所有的SQL都適合開并行，也并不是并行越多性能就越好，每條SQL都有適合自己的worker數(shù)，需要考慮開啟并行后對系統(tǒng)開銷成本計算。
• 如果cpu、共享內(nèi)存、worker進程等資源是整個數(shù)據(jù)庫共享。一個select如果消耗了大量的資源（比如開啟了64個worker），其他會話能夠申請的資源會變得有限，這一行為在OLTP事務(wù)應(yīng)用中尤為重要。所以需要將worker設(shè)置為合理的范圍。

2.3. 參數(shù)輸出說明

• postgresql的執(zhí)行計劃是以樹形的方式輸出該SQL的執(zhí)行順序，樹形的呈現(xiàn)方式就是以節(jié)點呈現(xiàn)，而每個結(jié)點輸出的詳細信息由參數(shù)控制。

costs

costs主要輸出執(zhí)行計劃的估算成本，而不是實際成本

• cost=0.00..1.60：表示啟動成本0.00，結(jié)束成本1.60
• rows：該語句返回的行數(shù)
• width：該行的平均字節(jié)數(shù)

計算估算成本的參數(shù)分別有：

• seq_page_cost：全表掃描的單個數(shù)據(jù)塊代價因子。
• random_page_cost：索引掃描的單個數(shù)據(jù)塊代價因子。
• cpu_tuple_cost：處理每條記錄的CPU開銷代價因子。
• cpu_index_tuple_cost：索引掃描時，每個索引條目的CPU開銷代價因子。
• cpu_operator_cost：操作符或函數(shù)的開銷代價因子。

analyze

（這里手動把costs關(guān)了，因為它是默認打開的，主要為了展示analyze的輸出結(jié)果）

analyze主要輸出真實的成本（真正的去執(zhí)行了這條SQL語句）

• time=0.010..0.013：表示啟動成本0.010，結(jié)束成本0.013
• rows：該節(jié)點返回的行數(shù)
• loops：該節(jié)點循環(huán)次數(shù)
• Planning Time：計劃執(zhí)行的時間
• Execution Time：實際執(zhí)行的時間

如果不想讓該SQL執(zhí)行成功，可以利用事務(wù)回滾

verbose

verbose用于輸出該節(jié)點執(zhí)行的事情 

buffers

buffers用于輸出該節(jié)點的緩存命中率，前提是必須開啟analyze

• hit：該節(jié)點shared_buffer命中的page數(shù)量。
• read：該節(jié)點shared_buffer沒有命中的page，但可能在系統(tǒng)緩存中命中。
• dirtied：該節(jié)點shared_buffer中出現(xiàn)的臟塊。
• written：該節(jié)點寫入磁盤的page。
• shared hit blocks（共享塊）：例如 表、索引、序列的數(shù)據(jù)塊。
• local hit blocks（本地塊）：例如 臨時表、索引的數(shù)據(jù)塊。
• temp read blocks（臨時塊）：例如 排序、hash、物化節(jié)點。

3. 優(yōu)化建議

（來源于postgresql官方文檔）

掃描節(jié)點優(yōu)化建議

• 過濾條件盡量提早使用。
• 過濾性越高的字段靠前，過濾性低的字段靠后（id1 < 10 and id2 < 100)。
• 核心SQL可以考慮采用"覆蓋索引"方式，確保盡可能高效。
• 多SQL總和考慮重復(fù)利用索引。
• 不干擾過濾的前提下，order by 排序字段加入索引。
• 索引應(yīng)盡量使用字節(jié)數(shù)小的列，對于重復(fù)值多的列不建議使用索引。

連接節(jié)點優(yōu)化建議

• 每次使用執(zhí)行計劃前，先對表進行分析（analyze [表名]）。
• 調(diào)整合適的連接順序（選擇率低的join先執(zhí)行）

耗時節(jié)點的合理性

1、數(shù)據(jù)掃描是否可以走索引、分區(qū)、物化視圖？

• 返回大量行數(shù)的表，采用順序掃描。
• 返回行數(shù)較少的表，采用索引掃描。

2、多表的連接順序是否合理？

• 當使用3張表查詢時（1張表數(shù)據(jù)小，2張表數(shù)據(jù)大），在做連接操作性，可以先讓大表和小表連接，再去連接另一張大表。

3、兩張表的連接算法是否合理？

• 查看基數(shù)估算結(jié)果是否準確，出現(xiàn)2表連接方式不合理。

4、返回行數(shù)估算是否準確？

• 比較估算成本與實際成本。若統(tǒng)計信息差距過大，進而導(dǎo)致選擇了次優(yōu)的執(zhí)行計劃。

5、是否有內(nèi)存不足的情況？

• 當存在排序等情況時，操作的表超過了work_mem時，可以考慮適當增加該參數(shù)大小。

總結(jié) 

到此這篇關(guān)于postgresql數(shù)據(jù)庫執(zhí)行計劃的文章就介紹到這了,更多相關(guān)postgresql執(zhí)行計劃內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！