快捷導(dǎo)航

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

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

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

簡(jiǎn)介

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

1. 語(yǔ)法

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

1.1. 參數(shù)選項(xiàng)

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

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

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

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

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

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

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

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

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

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

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

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

紅框：每個(gè)節(jié)點(diǎn)的開頭說(shuō)明

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

藍(lán)框：該節(jié)點(diǎn)的估算成本

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

綠框：該節(jié)點(diǎn)的真實(shí)成本

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

其他：該節(jié)點(diǎn)的詳細(xì)信息

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

簡(jiǎn)單說(shuō)明

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

2.2. 各個(gè)節(jié)點(diǎn)說(shuō)明

① 掃描節(jié)點(diǎn)

順序掃描（seq scan）

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

適用情況：

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

索引掃描（Index scan）

控制參數(shù)：enable_indexscan = on
解釋說(shuō)明：根據(jù)查詢條件掃描索引。因?yàn)樗饕怯行虻模圆捎脤?duì)半查找方式，快速找到符合條件的索引數(shù)據(jù)。再過(guò)濾條件和索引鍵值進(jìn)行比較。

適用情況：

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

注意情況：

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

位圖掃描（Bitmap scan）

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

適用情況：

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

TID掃描（TID Scan）

控制參數(shù)：enable_tidscan = on
解釋說(shuō)明：根據(jù)數(shù)據(jù)實(shí)際存儲(chǔ)位置的ctid進(jìn)行掃描，獲取元組。通過(guò)隱藏字段ctid掃描時(shí)標(biāo)記數(shù)據(jù)位置的字段，通過(guò)這個(gè)字段來(lái)查找數(shù)據(jù)（速度較快）

適用情況：

where條件中帶ctid的表。

覆蓋索引掃描（Index Only Scan）

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

適用情況：

更新少的表

其他掃描節(jié)點(diǎn)

Sample Scan

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

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

Subquery Scan

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

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

Function Scan

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

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

Valies Scan

values計(jì)算由值表達(dá)式指定一個(gè)行值或一組行值。

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

CTE Scan

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

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

WorkTable Scan

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

Foreign Scan

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

Custom Scan

自定義掃描接口

② 連接節(jié)點(diǎn)

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

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

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

哈希連接（Hash join）

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

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

歸并連接（Merge join）

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

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

③ 物化節(jié)點(diǎn)

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

物化節(jié)點(diǎn)（Material）

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

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

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

1、Hash Aggregate

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

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

2、Group Aggregate

解釋說(shuō)明：通過(guò)排序的方式進(jìn)行分組，再求聚集。

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

3、Aggregate

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

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

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

排序節(jié)點(diǎn)（Sort）

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

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

去重節(jié)點(diǎn)（Unique）

解釋說(shuō)明：對(duì)下層節(jié)點(diǎn)返回已排序的元組進(jìn)行去重。

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

其他物化節(jié)點(diǎn)

Window Agg

窗口函數(shù)

T_SetOp

setop語(yǔ)法節(jié)點(diǎn)

Lock Rows

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

Limit

使用limit時(shí)的節(jié)點(diǎn)

④ 控制節(jié)點(diǎn)

BitmapAnd / BitmapOr節(jié)點(diǎn)

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

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

Result節(jié)點(diǎn)

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

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

Append節(jié)點(diǎn)

解釋說(shuō)明：該節(jié)點(diǎn)會(huì)逐個(gè)處理這些子計(jì)劃，當(dāng)一個(gè)子計(jì)劃返回所有結(jié)果后，會(huì)接著執(zhí)行鏈表中的下一個(gè)子計(jì)劃，直到全部執(zhí)行完。

適用情況：用于處理包含一個(gè)或多個(gè)子計(jì)劃的鏈表。

Recursive Union節(jié)點(diǎn)

解釋說(shuō)明：對(duì)節(jié)點(diǎn)遞歸進(jìn)行處理。

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

⑤ 并行節(jié)點(diǎn)

并行全表掃描（Parallel SeqScan）

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

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

并行hash（Parallel Hash）

例如使用hash join

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

并行嵌套（Parallel NestedLoop）

支持并行嵌套查詢

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

也支持其他并行

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（默認(rèn)8）

OS支持的最大后臺(tái)進(jìn)程數(shù)

max_parallel_workers（默認(rèn)8）

最大并行worker數(shù)

max_parallel_workers_per_gather（默認(rèn)2）

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

max_parallel_maintenance_workers（默認(rèn)2）

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

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

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

并行的觸發(fā)條件

表

表的存儲(chǔ)空間至少大于 min_parallel_table_scan_size（默認(rèn) 8MB）

索引

索引的存儲(chǔ)空間至少大于 min_parallel_index_scan_size （默認(rèn)512KB）

查詢某張表的索引大小
SELECT pg_size_pretty( pg_relation_size('[表名]'));

并行注意項(xiàng)

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

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

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

costs

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

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

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

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

analyze

（這里手動(dòng)把costs關(guān)了，因?yàn)樗悄J(rèn)打開的，主要為了展示analyze的輸出結(jié)果）

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

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

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

verbose

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

buffers

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

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

3. 優(yōu)化建議

（來(lái)源于postgresql官方文檔）

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

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

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

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

耗時(shí)節(jié)點(diǎn)的合理性

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

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

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

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

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

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

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

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

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

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

總結(jié)

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