postgresq-自定義執行計劃(custom plan)與generic plan(通用執行計劃）

文章目錄


之前寫過一篇關于 PostgreSQL prepare sql的文章，但當時沒有提到generic plan(通用計劃)和custom plan(自定義計劃)這兩個概念。現在將通過舉例介紹這兩個概念。

創建測試表：

postgres=# create database demo;
CREATE DATABASE
postgres=# \c demo
You are now connected to database "demo" as user "postgres".
demo=# create table t1(a int,b text);
CREATE TABLE
demo=# insert into t1 select i,'aaa' from generate_series(1,100) i;
INSERT 0 100
demo=# insert into t1 select i,'bbb' from generate_series(101,200) i;
INSERT 0 100
demo=# insert into t1 select i,'ccc' from generate_series(201,300) i;
INSERT 0 100

在有了數據之后，可以創建一個預處理語句（prepared statement），這個語句的結構是固定的，但允許在執行時插入不同的參數值。這樣可以實現代碼重用，并提高查詢的執行效率。

demo=# prepare pre_stmt as select * from t1 where b=$1;
PREPARE

在 PostgreSQL 中，預處理語句（prepared statements）會在當前會話中注冊。如果想查看當前會話中有哪些預處理語句已經被創建并可用，可以通過查詢系統視圖 pg_prepared_statements 來獲取這些信息。

   name   |                    statement                     |         prepare_time          | parameter_types |  result_types  | from_sql | generic_plans | custom_plans
----------+--------------------------------------------------+-------------------------------+-----------------+----------------+----------+---------------+--------------pre_stmt | prepare pre_stmt as select * from t1 where b=$1; | 2025-01-01 18:32:43.697846+07 | {text}          | {integer,text} | t        |             0 |            0
(1 row)

當我們對一個語句運行 EXPLAIN (ANALYZE) 時，可以看到 PostgreSQL 為該語句生成的實際執行計劃以及執行的統計信息。這包括查詢是如何被優化和執行的，例如是否使用了索引、查詢的執行時間、返回的行數等。

demo=# explain (analyze) execute pre_stmt ('aaa');QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.032..0.252 rows=100 loops=1)Filter: (b = 'aaa'::text)Rows Removed by Filter: 200Planning Time: 0.568 msExecution Time: 0.287 ms
(5 rows)

在執行計劃的“Filter”行中,第一次執行時，執行計劃會顯示你傳入的實際參數值（比如 ‘aaa’），這時是 自定義計劃（custom plan)。但是，當你多次執行這個預處理語句時，執行計劃中filter行的參數值會變成顯示占位符（如 $1），這時是 通用計劃(generic plan)。

demo=# explain (analyze) execute pre_stmt ('aaa');QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.107..0.255 rows=100 loops=1)Filter: (b = 'aaa'::text)Rows Removed by Filter: 200Planning Time: 0.106 msExecution Time: 0.308 ms
(5 rows)demo=# explain (analyze) execute pre_stmt ('aaa');QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.045..0.255 rows=100 loops=1)Filter: (b = 'aaa'::text)Rows Removed by Filter: 200Planning Time: 0.144 msExecution Time: 0.297 ms
(5 rows)demo=# explain (analyze) execute pre_stmt ('aaa');QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.049..0.259 rows=100 loops=1)Filter: (b = 'aaa'::text)Rows Removed by Filter: 200Planning Time: 0.293 msExecution Time: 0.309 ms
(5 rows)demo=# explain (analyze) execute pre_stmt ('aaa');QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.041..0.248 rows=100 loops=1)Filter: (b = 'aaa'::text)Rows Removed by Filter: 200Planning Time: 0.279 msExecution Time: 0.289 ms
(5 rows)demo=# explain (analyze) execute pre_stmt ('aaa');QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.034..0.215 rows=100 loops=1)Filter: (b = $1)Rows Removed by Filter: 200Planning Time: 0.101 msExecution Time: 0.245 ms
(5 rows)

在執行查詢時，最開始會看到執行計劃中顯示實際傳入的參數值，但隨著執行次數的增加，執行計劃會變成使用占位符（例如 $1）來表示參數。此時，查詢使用的是通用計劃。通用計劃一旦生成后，無論傳入什么不同的參數值，它的執行計劃都不會再發生變化，直到該預處理語句結束。

demo=# explain (analyze) execute pre_stmt ( 'bbb' );QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.111..0.367 rows=100 loops=1)Filter: (b = $1)Rows Removed by Filter: 200Planning Time: 0.181 msExecution Time: 0.412 ms
(5 rows)demo=# explain (analyze) execute pre_stmt ( 'ccc' );QUERY PLAN
------------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.165..0.249 rows=100 loops=1)Filter: (b = $1)Rows Removed by Filter: 200Planning Time: 0.025 msExecution Time: 0.289 ms
(5 rows)demo=# explain (analyze) execute pre_stmt ( null );QUERY PLAN
----------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.75 rows=100 width=8) (actual time=0.232..0.233 rows=0 loops=1)Filter: (b = $1)Rows Removed by Filter: 300Planning Time: 0.022 msExecution Time: 0.255 ms
(5 rows)

如果你查看 PostgreSQL 的源代碼（特別是 src/backend/utils/cache/plancache.c 文件），你將能理解為什么在執行 5 次之后，執行計劃會發生變化。
這個文件包含了與查詢計劃緩存相關的邏輯，解釋了為什么預處理語句在執行多次后會從“自定義計劃”變為“通用計劃”。

/** choose_custom_plan: choose whether to use custom or generic plan** This defines the policy followed by GetCachedPlan.*/
static bool
choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
{double          avg_custom_cost;/* One-shot plans will always be considered custom */if (plansource->is_oneshot)return true;/* Otherwise, never any point in a custom plan if there's no parameters */if (boundParams == NULL)return false;/* ... nor for transaction control statements */if (IsTransactionStmtPlan(plansource))return false;/* See if caller wants to force the decision */if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)return false;if (plansource->cursor_options & CURSOR_OPT_CUSTOM_PLAN)return true;/* Generate custom plans until we have done at least 5 (arbitrary) */if (plansource->num_custom_plans < 5)return true;

一旦 PostgreSQL 使用了通用計劃，即使數據改變并重新分析表，執行計劃也不會再改變。

demo=# insert into t1 select i,'ddd' from generate_series(201,210) i;
INSERT 0 10
demo=# insert into t1 select i,'eee' from generate_series(211,211) i;
INSERT 0 1
demo=# analyze t1;
ANALYZE
demo=# select b,count(*) from t1 group by b order by b;b  | count
-----+-------aaa |   100bbb |   100ccc |   100ddd |    10eee |     1
(5 rows)
demo=# explain (analyze) execute pre_stmt('ddd');QUERY PLAN
----------------------------------------------------------------------------------------------Seq Scan on t1  (cost=0.00..5.89 rows=62 width=8) (actual time=0.059..0.070 rows=10 loops=1)Filter: (b = $1)Rows Removed by Filter: 301Planning Time: 0.021 msExecution Time: 0.184 ms
(5 rows)

當數據量增加、數據分布不均勻，并且在某一列（比如“b”列）上建立了索引時，查詢的執行計劃可能會發生變化，PostgreSQL 會根據這些因素選擇不同的執行策略。

demo=# insert into t1 select i, 'aaa' from generate_series (1,2000000) i;
INSERT 0 1000000
demo=# insert into t1 select i, 'bbb' from generate_series (1000001,3000000) i;
INSERT 0 1000000
demo=# insert into t1 select i, 'ccc' from generate_series (2000001,3000000) i;
INSERT 0 1000000
demo=# insert into t1 select i, 'eee' from generate_series (3000001,3000010) i;
INSERT 0 10
demo=# create index idx_b on t1(b);
CREATE INDEX
demo=#  select b,count(*) from t1 group by b order by b;b  |  count
-----+---------aaa | 1000000bbb | 1000000ccc | 1000000eee |      10(4 rows)

無論我們執行這個查詢多少次（查詢的是參數‘eee’），PostgreSQL 都不會使用通用計劃，而是會持續使用自定義計劃。

demo=# explain (analyze) execute pre_stmt('eee');QUERY PLAN
-----------------------------------------------------------------------------------------------------------Index Scan using idx_b on t1  (cost=0.43..4.45 rows=1 width=8) (actual time=0.039..0.048 rows=10 loops=1)Index Cond: (b = 'eee'::text)Planning Time: 0.287 msExecution Time: 0.076 ms
(4 rows)
------>重復執行多次，但至少執行 10 次。
demo=# explain (analyze) execute pre_stmt('eee');QUERY PLAN
-----------------------------------------------------------------------------------------------------------Index Scan using idx_b on t1  (cost=0.43..4.45 rows=1 width=8) (actual time=0.037..0.045 rows=10 loops=1)Index Cond: (b = 'eee'::text)Planning Time: 0.137 msExecution Time: 0.066 ms
(4 rows)

在某些情況下，當數據分布不均勻時（比如某列中有很多重復值，只有少數值是稀有的，比如上面例子中出現的b=‘eee’），即使考慮到重新生成執行計劃的開銷，自定義計劃的執行成本仍然比通用計劃低。這樣，PostgreSQL 會優先選擇使用自定義計劃，而不會使用通用計劃，甚至在重新規劃的開銷考慮進去之后，通用計劃也可能永遠不會被使用。

順便提一下啊，在postgresql后，explain增加了generic_plan選型來供我們方便的查看通用執行計劃

demo=# EXPLAIN (GENERIC_PLAN) SELECT * FROM t1 WHERE t1 = $1;QUERY PLAN
------------------------------------------------------------------------Gather  (cost=1000.00..31400.05 rows=15000 width=8)Workers Planned: 2->  Parallel Seq Scan on t1  (cost=0.00..28900.05 rows=6250 width=8)Filter: (t1.* = $1)
(4 rows)

總的來說，PostgreSQL 15 引入了 EXPLAIN (GENERIC_PLAN) 選項，使得開發者可以明確地查看通用計劃，而不受參數變化的影響。

如果覺得文章有點價值，請幫忙點關注，謝謝

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/64707.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/64707.shtml
英文地址，請注明出處：http://en.pswp.cn/web/64707.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

dockfile 配置 /etc/apt/source.list.d/debian.list 清華鏡像

dockfile 配置 /etc/apt/source.list.d/debian.list 清華鏡像

docker:3.12.7 鏡像使用的是 debian 系統&#xff0c;比 ubuntu 更輕量。debian 系統內&#xff0c;apt 鏡像源列表位于 /etc/apt/source.list.d/debian.list&#xff08;作為對比&#xff0c;ubuntu 的鏡像列表位于 /etc/apt/source.list&#xff0c;二者語法相同&#xff09;…
閱讀更多...
程序員測試日常小工具

程序員測試日常小工具

作為一名程序員&#xff0c;或者測試人員&#xff0c;日常工作最常用的工具有哪些&#xff0c;截圖&#xff0c;截圖漂浮&#xff0c;翻譯&#xff0c;日期處理&#xff0c;api調用...&#xff0c; 當你拿到一串報文后&#xff0c;想要json轉換時&#xff0c;是不是要打…
閱讀更多...
【MySQL高級】第1-4章

【MySQL高級】第1-4章

第1章 存儲過程 1.1 什么是存儲過程&#xff1f; 存儲過程可稱為過程化SQL語言&#xff0c;是在普通SQL語句的基礎上增加了編程語言的特點&#xff0c;把數據操作語句(DML)和查詢語句(DQL)組織在過程化代碼中&#xff0c;通過邏輯判斷、循環等操作實現復雜計算的程序語言。 換…
閱讀更多...
深入淺出：AWT事件監聽器及其應用

深入淺出：AWT事件監聽器及其應用

前言 在Java的GUI編程中&#xff0c;事件處理是非常重要的一環。AWT&#xff08;Abstract Window Toolkit&#xff09;框架提供了靈活的事件處理機制&#xff0c;使得開發者能夠響應用戶的操作&#xff0c;例如點擊按鈕、鍵盤輸入、鼠標點擊等。AWT的事件監聽器就是實現這一機…
閱讀更多...
【Rust自學】8.5. HashMap Pt.1：HashMap的定義、創建、合并與訪問

【Rust自學】8.5. HashMap Pt.1：HashMap的定義、創建、合并與訪問

8.5.0. 本章內容 第八章主要講的是Rust中常見的集合。Rust中提供了很多集合類型的數據結構&#xff0c;這些集合可以包含很多值。但是第八章所講的集合與數組和元組有所不同。 第八章中的集合是存儲在堆內存上而非棧內存上的&#xff0c;這也意味著這些集合的數據大小無需在編…
閱讀更多...
混合合并兩個pdf文件

混合合并兩個pdf文件

混合兩個pdf 1、在線免費交替和混合奇數和偶數PDF頁面2、有什么軟件把兩個 PDF 交叉合并&#xff1f;3、pdfsam本地合并 如何Google翻譯的原文和譯文合并&#xff0c;&#xff08;沉浸式翻譯效果相對較好&#xff09; 1、在線免費交替和混合奇數和偶數PDF頁面 https://deftpd…
閱讀更多...
Hutool 發送 HTTP 請求的幾種常見寫法

Hutool 發送 HTTP 請求的幾種常見寫法

最簡單的 GET 請求&#xff1a; String result HttpUtil.get("https://www.baidu.com");帶參數的 GET 請求&#xff1a; // 方法1: 直接拼接URL參數 String result HttpUtil.get("https://www.baidu.com?name張三&age18");// 方法2: 使用 HashMap…
閱讀更多...
獲取用戶詳細信息-ThreadLocal優化

獲取用戶詳細信息-ThreadLocal優化

Thread全局接口可用&#xff0c;不用再重復編寫。所以為了代碼的復用&#xff0c;使用Thread。把之前的內容&#xff08;函數的參數和map與username&#xff09;注釋掉&#xff0c;換為Thread傳過來的內容&#xff08;map與username&#xff09;。 因為Thread需要在攔截器里面…
閱讀更多...
THUCNews解壓/THUCNews數據集解壓出問題

THUCNews解壓/THUCNews數據集解壓出問題

省流&#xff1a;使用zip64進行解壓&#xff0c;文件數目太多windows默認zip16裝不下 我在使用THUCNews中文文本數據集時出現了問題&#xff0c;原數據集解壓后應該包含以下兩個文件夾: 其中THUCNews文件夾下有以新聞類別命名的子文件。官網下載的是一個1.56GB的zip壓縮包 而我…
閱讀更多...
MySQL使用通用二進制文件安裝到Unix/Linux

MySQL使用通用二進制文件安裝到Unix/Linux

Oracle提供了一組MySQL的二進制發行版。其中包括用于許多平臺的壓縮tar文件&#xff08;擴展名為.tar.xz的文件&#xff09;形式的通用二進制發行版&#xff0c;以及用于選定平臺的特定平臺包格式的二進制文件。 本節介紹在Unix/Linux平臺上從壓縮的tar文件二進制分布安裝MySQ…
閱讀更多...
安卓/system/bin下命令中文說明（AI）

安卓/system/bin下命令中文說明（AI）

ATFWD-daemon&#xff1a;AT指令轉發守護進程&#xff0c;用于將AT指令從應用層轉發到調制解調器。 PktRspTest&#xff1a;數據包響應測試工具。 StoreKeybox&#xff1a;存儲密鑰盒工具&#xff0c;用于安全地存儲加密密鑰。 WifiLogger_app&#xff1a;WiFi日志記錄應用&…
閱讀更多...
Git操作總結

Git操作總結

可以直接看實踐 總結自施磊老師課程 Git與SVN對比 svn操作流程 寫代碼。 從服務器拉回服務器的當前版本庫&#xff0c;并解決服務器版本庫與本地代碼的沖突。 將本地代碼提交到服務器。 Git操作流程 寫代碼&#xff0c; 然后添加&#xff08;add&#xff09;到暫存區。 …
閱讀更多...
直流開關電源技術及應用二

直流開關電源技術及應用二

文章目錄 8 PFC8.1 基本概念8.1.1 功率因數8.1.2 功率因數偏低帶來的影響8.1.3 特點 8.2 有源功率因數校正原理8.2.1不連續工作模式的矯正原理恒頻控制技術控制目標控制關鍵要素控制過程實現方式公式Boost電路和boost pfc電路的聯系和區別聯系區別 恒導通時間控制 8.2.2 連續工…
閱讀更多...
UNI-APP_i18n國際化引入

UNI-APP_i18n國際化引入

官方文檔&#xff1a;https://uniapp.dcloud.net.cn/tutorial/i18n.html vue2中使用 1. 新建文件 locale/index.js import en from ./en.json import zhHans from ./zh-Hans.json import zhHant from ./zh-Hant.json const messages {en,zh-Hans: zhHans,zh-Hant: zhHant }…
閱讀更多...
typora+picgo core+minio自動上傳圖片

typora+picgo core+minio自動上傳圖片

1. 在服務器上安裝docker版本minio 創建/docker/minio文件夾 mkdir -p /docker/minio在此文件夾創建docker-compose.yml version: "3.5" services:minio:image: quay.io/minio/minio:latestcontainer_name: minioprivileged: truerestart: alwaysports:# API接口訪…
閱讀更多...
論文筆記：DepthLab: From Partial to Complete

論文筆記：DepthLab: From Partial to Complete

是一篇很精煉的論文&#xff0c;不知道咋總結了&#xff0c;就差全文翻譯了&#xff0c;不過在這里我主要關注3D部分&#xff0c;因為他的pipeline是基于SD的&#xff0c;框圖也比較清晰易懂&#xff0c;非常細節的內容可以回頭看論文&#xff0c;哈哈哈&#xff0c;給作者大佬…
閱讀更多...
LeetCode--排序算法（堆排序、歸并排序、快速排序）

LeetCode--排序算法（堆排序、歸并排序、快速排序）

排序算法 歸并排序算法思路代碼時間復雜度 堆排序什么是堆&#xff1f;如何維護堆&#xff1f;如何建堆&#xff1f;堆排序時間復雜度 快速排序算法思想代碼時間復雜度 歸并排序 算法思路 歸并排序算法有兩個基本的操作&#xff0c;一個是分&#xff0c;也就是把原數組劃分成…
閱讀更多...
ShardingSphere-Proxy分表場景：go測試案例

ShardingSphere-Proxy分表場景：go測試案例

接續上篇文章《ShardingSphere-Proxy分表場景測試案例》 go測試用例&#xff1a; package mainimport ("fmt""math/rand""time""github.com/bwmarrin/snowflake""gorm.io/driver/mysql""gorm.io/gorm""gor…
閱讀更多...
主流在售AI電子寵物產品

主流在售AI電子寵物產品

市面上已經有許多類型的AI電子寵物產品&#xff0c;它們各具特色&#xff0c;旨在提供情感陪伴、教育娛樂以及智能互動等功能。以下是幾款在市場上較為知名的AI電子寵物玩具&#xff0c;涵蓋了不同的形態和技術特點&#xff1a; 1. Moflin 制造商&#xff1a;日本消費電子公司…
閱讀更多...
Debian-linux運維-docker安裝和配置

Debian-linux運維-docker安裝和配置

騰訊云搭建docker官方文檔&#xff1a;https://cloud.tencent.com/document/product/213/46000 阿里云安裝Docker官方文檔&#xff1a;https://help.aliyun.com/zh/ecs/use-cases/install-and-use-docker-on-a-linux-ecs-instance 天翼云常見docker源配置指導&#xff1a;htt…
閱讀更多...
最新文章

Copyright @ 2022~2023