腳本之家服務器常用軟件

快捷導航

postgresql數(shù)據(jù)庫執(zhí)行計劃圖文詳解

更新時間：2024年01月12日 11:01:15 作者：一只勤勞的耗子

了解PostgreSQL執(zhí)行計劃對于程序員來說是一項關鍵技能,執(zhí)行計劃是我們優(yōu)化查詢,驗證我們的優(yōu)化查詢是否確實按照我們期望的方式運行的重要方式,這篇文章主要給大家介紹了關于postgresql數(shù)據(jù)庫執(zhí)行計劃的相關資料,需要的朋友可以參考下

簡介

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

1. 語法

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

1.1. 參數(shù)選項

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

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

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

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

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

執(zhí)行計劃輸出結構示意圖：

2.1. 整體結構解析

執(zhí)行計劃的結構是怎樣的？

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

怎么查看執(zhí)行計劃的結構？

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

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

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

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

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

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

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

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

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

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

簡單說明

                                          QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=1.92..1.95 rows=3 width=21) (actual time=0.100..0.102 rows=2 loops=1)
 'hash聚合掃描'  '(估算成本=啟動成本..結束成本 , 執(zhí)行SQL返回的行數(shù) , 每行平均字節(jié)數(shù))'  '(實際成本)'
   Output: count(*), "position"
     '表示該節(jié)點執(zhí)行的字段'
   Group Key: pay_scale."position"
     '表示該節(jié)點執(zhí)行的字段'
   Batches: 1  Memory Usage: 24kB
     '表示該節(jié)點內存使用大小'
   Buffers: shared hit=7 read=10
     '表示共享內存中有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 查出結果大于500條

索引掃描（Index scan）

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

適用情況：

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

注意情況：

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

位圖掃描（Bitmap scan）

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

適用情況：

列中含有重復值。
查詢中包含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

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

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

Function Scan

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

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

Valies Scan

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

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

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條）去連接。時間復雜度為：m * n。

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

哈希連接（Hash join）

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

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

歸并連接（Merge join）

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

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

③ 物化節(jié)點

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

物化節(jié)點（Material）

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

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

分組節(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é)點（Unique）

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

適用情況：查詢中帶distinct關鍵字（當要求去重的屬性被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運算（將產生每一個位圖的子計劃放在一個鏈表中，在執(zhí)行過程中先執(zhí)行子計劃節(jié)點獲取位圖，再進行and / or操作）。

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

Result節(jié)點

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

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

Append節(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é)點合并最終結果集。

并行嵌套（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ù)

它們之間的配置關系：

以 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、共享內存、worker進程等資源是整個數(shù)據(jù)庫共享。一個select如果消耗了大量的資源（比如開啟了64個worker），其他會話能夠申請的資源會變得有限，這一行為在OLTP事務應用中尤為重要。所以需要將worker設置為合理的范圍。

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

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

costs

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

cost=0.00..1.60：表示啟動成本0.00，結束成本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關了，因為它是默認打開的，主要為了展示analyze的輸出結果）

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

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

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

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總和考慮重復利用索引。
不干擾過濾的前提下，order by 排序字段加入索引。
索引應盡量使用字節(jié)數(shù)小的列，對于重復值多的列不建議使用索引。

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

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

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

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

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

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

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

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

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

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

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

5、是否有內存不足的情況？

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