PostgreSQL 查詢當前執行中sql的執行計劃——pg_show_plans模組
執行計劃儲存
如果有同樣的SQL要執行很多遍，且每次都是同樣的執行計劃，每次都發生硬解析，則會消耗大量時間，類似於Oracle的存放執行計劃的library cache，PG也有一個類似的plan_cache概念,但實際上，PG提供的是預備語言(preparedstatement)，它實際是要求應用給這個語句進行標識，之後應用再透過這標識請求服務端執行，並且由應用負責回收。
對於無參的預備語句，在第一次在執行的時候就會生成執行計劃，之後再執行則會使用這個執行計劃。對於有參的預備語句，最優的執行計劃會因為變數的實際值而不一樣，因此，在PG裡，前5次執行預備語句，每一次都產生新的執行計劃，叫做custom plan，第6次再執行時，會生成一個不依賴於引數的執行計劃並儲存下來，叫做generic plan。之後每一次執行一個預備語句，也都會善生一個相對應的custom plan，如果generic plan小於之前執行過的custom plan的平均值的1.1倍，則使用generic plan，否則使用當前產生的對應的custom plan。
custom plan是指對於preapre語句，在執行execute的時候，把execute語句中的引數巢狀到語句之後生成的計劃。 custom plan會根據execute語句中具體的引數生成計劃，這種方案的優點是每次都按照具體的引數生成優選計劃，執行效能比較好； 缺點是每次執行前都需要重新生成計劃，存在大量的重複的最佳化器開銷。 generic plan是指對於preapre語句生成計劃，該計劃策略會在執行execute語句的時候把引數bind到plan中，然後執行計劃。 這種方案的優點是每次執行可以省去重複的最佳化器開銷；缺點是當bind引數欄位上資料存在傾斜時該計劃可能不是最優的， 部分bind引數場景下執行效能較差。
可以根據pg_prepared_statements檢視顯示當前會話所有可用的預備語句
postgres=# \d pg_prepared_statements View "pg_catalog.pg_prepared_statements" Column | Type | Collation | Nullable | Default -----------------+--------------------------+-----------+----------+--------- name | text | | | statement | text | | | prepare_time | timestamp with time zone | | | parameter_types | regtype[] | | | from_sql | boolean | | |
plan_cache_mode引數可以影響prepare語句選擇生成執行計劃的策略
auto表示按照預設的方式選擇custom plan或者generic plan force_generic_plan表示強制走generic plan force_custom_plan表示強制走custom plan
此引數只對prepare語句生效，一般用在prepare語句中引數化欄位存在比較嚴重的資料傾斜的場景下
通常情況，我們可以透過
explain，explain analyze，explain verbose來獲取執行計劃。
但是explain查詢的當前快取的執行計劃， 在實際中估算的成本可能是不準確的，因為很可能估算的成本和你實際執行的成本不一致。而，explain analyze，explain verbose則會實際執行sql，在某些場景不會允許。
（可以嘗試採用開啟一個事務後，explain analyze，explain verbose檢視執行計劃，最後rollback）
pg_show_plans模組
接下來的主題則是一個供PostgreSQL資料庫查詢當前執行中sql的執行計劃的模組—pg_show_plans，它可以動態查詢當前正在執行中的sql的執行計劃。
pg_show_plans 是一個顯示所有當前執行的SQL語句的查詢計劃的模組。它在plan結束位置, 截獲並儲存當前plan tree. 從而其他會話可以列印儲存的plan tree。此模組支援從9.5到12的PostgreSQL版本。它會在共享記憶體上建立一個雜湊表，以便臨時儲存查詢計劃。雜湊表大小不能更改，因此如果雜湊表已滿，則不會儲存計劃。
安裝及使用介紹
1.進到資料庫對應的contrib目錄下
[postgres@t1ysl opt]$ cd /opt/postgresql-12.1/contrib/
2.獲取pg_show_plans擴充套件包
[postgres@t1ysl contrib]$ git clone Cloning into 'pg_show_plans'... remote: Enumerating objects: 70, done. remote: Counting objects: 100% (2/2), done. remote: Compressing objects: 100% (2/2), done. remote: Total 70 (delta 0), reused 0 (delta 0), pack-reused 68 Unpacking objects: 100% (70/70), done.
3.編譯安裝
[postgres@t1ysl contrib]$ cd pg_show_plans/ [postgres@t1ysl pg_show_plans]$ make make -C ../../src/backend generated-headers make[1]: Entering directory `/opt/postgresql-12.1/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/opt/postgresql-12.1/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/opt/postgresql-12.1/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/opt/postgresql-12.1/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/opt/postgresql-12.1/src/backend/utils' make[1]: Leaving directory `/opt/postgresql-12.1/src/backend' gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -c -o pg_show_plans.o pg_show_plans.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_show_plans.so pg_show_plans.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pg12/lib',--enable-new-dtags [postgres@t1ysl pg_show_plans]$ make install make -C ../../src/backend generated-headers make[1]: Entering directory `/opt/postgresql-12.1/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/opt/postgresql-12.1/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/opt/postgresql-12.1/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/opt/postgresql-12.1/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/opt/postgresql-12.1/src/backend/utils' make[1]: Leaving directory `/opt/postgresql-12.1/src/backend' /bin/mkdir -p '/opt/pg12/lib/postgresql' /bin/mkdir -p '/opt/pg12/share/postgresql/extension' /bin/mkdir -p '/opt/pg12/share/postgresql/extension' /bin/install -c -m 755 pg_show_plans.so '/opt/pg12/lib/postgresql/pg_show_plans.so' /bin/install -c -m 644 ./pg_show_plans.control '/opt/pg12/share/postgresql/extension/' /bin/install -c -m 644 ./pg_show_plans--1.0.sql '/opt/pg12/share/postgresql/extension/'
4.在postgresql.conf檔案的shared_preload_libraries裡增加pg_show_plans，並重啟資料庫生效
vi postgresql.conf 增加 shared_preload_libraries = 'pg_show_plans' [postgres@t1ysl ~]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2021-07-25 08:52:08.402 CST [2990] LOG: starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2021-07-25 08:52:08.402 CST [2990] LOG: listening on IPv4 address "0.0.0.0", port 6000 2021-07-25 08:52:08.402 CST [2990] LOG: listening on IPv6 address "::", port 6000 2021-07-25 08:52:08.406 CST [2990] LOG: listening on Unix socket "/tmp/.s.PGSQL.6000" 2021-07-25 08:52:08.434 CST [2990] LOG: redirecting log output to logging collector process 2021-07-25 08:52:08.434 CST [2990] HINT: Future log output will appear in directory "/opt/pg_log6000". done server started
5.建立EXTENSION
postgres=# CREATE EXTENSION pg_show_plans; CREATE EXTENSION
6.透過pg_show_plans表可檢視當前正在執行中的sql的執行計劃
postgres=# \d pg_show_plans View "public.pg_show_plans" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+--------- pid | bigint | | | level | bigint | | | userid | oid | | | dbid | oid | | | plan | text | | |
模擬使用場景
1.開啟兩個session
一個session執行一條較慢sql(便於獲取到其執行計劃)
一個session在sql執行過程獲取其執行計劃
2.這裡我舉例的sql為對346MB的一張表的全表掃描
session1: postgres=# \dt+ t1_ysl List of relations Schema | Name | Type | Owner | Size | Description --------+--------+-------+----------+--------+------------- public | t1_ysl | table | postgres | 346 MB | (1 row) postgres=# select * from t1_ysl ; id --------- 3511203 5877715 7284053 4522491 3815961 6454179 2712063 ...
透過pg_show_plans和pg_stat_activity聯合查詢出當前執行中sql的執行計劃。
session2: postgres=# SELECT * FROM pg_show_plans; pid | level | userid | dbid | plan ------+-------+--------+-------+------------------------------------------------------------------ ----- 1812 | 0 | 10 | 13593 | Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width =56) 1899 | 0 | 10 | 13593 | Seq Scan on t1_ysl (cost=0.00..144247.77 rows=9999977 width=4) (2 rows) postgres=# SELECT p.pid, p.level, p.plan, a.query FROM pg_show_plans p LEFT JOIN pg_stat_activity a ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level; pid | level | plan | query ------+-------+----------------------------------------------------------------------------------- -------------+---------------------------------------------------------------- 1812 | 0 | Sort (cost=72.08..74.58 rows=1000 width=80) +| SELECT p.pid, p.level, p.plan, a.query FROM pg_show_plans p + | | Sort Key: pg_show_plans.pid, pg_show_plans.level +| LEFT JOIN pg_stat_activity a + | | -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) +| ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level; | | Hash Cond: (pg_show_plans.pid = s.pid) +| | | Join Filter: (pg_show_plans.level = 0) +| | | -> Function Scan on pg_show_plans (cost=0.00..10.00 rows=1000 width=48) +| | | -> Hash (cost=1.00..1.00 rows=100 width=44) +| | | -> Function Scan on pg_stat_get_activity s (cost=0.00..1.00 rows=1 00 width=44) | 1899 | 0 | Seq Scan on t1_ysl (cost=0.00..144247.77 rows=9999977 width=4) | select * from t1_ysl ; (2 rows)
相關引數
pg_show_plans.enable 是否可以顯示計劃。 pg_show_plans.plan_format 它控制查詢計劃的輸出格式。可以選擇文字或json。預設為文字。 pg_show_plans.max_plan_length 它設定查詢計劃的最大長度。預設值為8192[位元組]。此引數必須設定為整數。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69990629/viewspace-2783939/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 查詢當前執行的sql及相關內容SQL
- 查詢當前正在執行的SQL語句並KILLSQL
- explain 查詢執行計劃AI
- 透過查詢檢視sql執行計劃SQL
- 查詢當前正在執行的job的情況
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- MySQL中in（獨立子查詢）的執行計劃MySql
- 執行計劃-6：推入子查詢
- SQL的執行計劃SQL
- sql 執行計劃SQL
- oracle查詢語句執行計劃中的表消除Oracle
- SQL效能的度量 - 透過v$sql_plan查詢執行計劃SQL
- PostgreSQL執行計劃變化SQL
- 通過等待事件來獲得查詢SQl的執行計劃事件SQL
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle中檢視已執行sql的執行計劃OracleSQL
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL執行計劃分析SQL
- sql查詢是如何執行的？SQL
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- 查詢Oracle正在執行和執行過的SQL語句OracleSQL
- sql的執行計劃 詳解SQL
- oracle查詢sql執行耗時、執行時間、sql_idOracleSQL
- PostgreSQL SQL執行流程SQL
- 查詢執行慢的SQL語句SQL
- 一條查詢sql的執行之路SQL
- 查詢正在執行的SQL語句SQL
- 自適應查詢執行：在執行時提升Spark SQL執行效能SparkSQL
- 獲取SQL執行計劃SQL
- SQL 執行計劃案例1SQL
- 剖析SQL Server執行計劃SQLServer
- 檢視sql執行計劃SQL
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- 執行計劃-1：獲取執行計劃
- 用PostgreSQL執行檔案中的SQL程式SQL