大家好，今天大表哥带来一篇与PG查看执行计划相关的文章。
维护生产数据库的一线DBA经常需要及时查询分析一些数据库正在运行的SQL。或者去追溯一下历史某个时段的SQL的性能是否出现了问题。 熟悉ORACLE的朋友们都知道，作为业界RDBMS 的旗舰标杆产品，ORACLE 原生自带了查看历史执行计划，现行执行计划，和执行计划的绑定功能。
PG 系数据库作为数据库国产化的首选， 自然也会衍生出类似的功能，原生PG本身并不几具备相关功能， 但是PG拥有强大 extension 作为相关功能扩展。
首先我们来看如何记录和查看SQL的历史执行计划：
我们测试的extension 是业界著名的 auto-explain , 这个插件是官方源码 自带的插件不需要额外的下载。
官网首页： https://www.postgresql.org/docs/current/auto-explain.html
插件的使用方式可以作为2种 session 级别的或者是 设置到参数 shared_preload_libraries 里面的全局级别的。
关于auto-explain 这个插件的几个重要的参数：
auto_explain.log_min_duration： 定义了记录慢查询执行计划的时间。 默认是-1表示不记录， 0表示记录所有的（生产数据库上慎用0，以免撑满了磁盘）
auto_explain.log_verbose： 开启详细的日志，默认关闭。打开需要考虑磁盘的使用量
auto_explain.log_settings：是否记录参数改变，默认关闭
auto_explain.log_format：设置输出日志格式设置。默认 text . 可选项为： text, xml, json, and yaml
auto_explain.log_level：输出日志级别。 默认： LOG。 可选项有为 DEBUG, INFO, NOTICE, WARNING, and LOG等等
log_nested_statements： 是否记录嵌套的SQL语句执行计划。比如查询中带有函数的语句。默认是关闭
auto_explain.sample_rate： 设置记录执行计划的采样率。默认是1,100%采集统计信息。
auto_explain.log_analyze ：是否开启进行执行计划的分析。默认是关闭的。官方文档说明这个参数会严重影响数据库的性能，强烈不建议开启这个功能。
下面这几个参数生效的前提是 打开auto_explain.log_analyze 这个参数请况下。默认是关闭，我们一般也不会打开这个参数：
auto_explain.log_buffers： 在执行计划中记录额外的buffer内存使用情况，
auto_explain.log_wal：在执行计划中记录额外的wal的生成量大小
auto_explain.log_timing：在执行计划中记录额外的时间
auto_explain.log_triggers 在执行计划中记录 trigger 的信息
我们先看一下session 级别的设置统计信息回显： 类似于oracle中SQLPLUS客户端设置 set autotrace on 的功能：
DBA或者是开发测试人员想每次执行完SQL立即回显SQL的执行计划，这个还是比较实用的功能。
我们只需要在session 级别load一下这个插件即可：PSQL客户端的命令行上就可以看到执行计划的回显
postgres@[local:/tmp]:1992=#49910 load 'auto_explain';
LOAD
postgres@[local:/tmp]:1992=#49910 set auto_explain.log_min_duration = 0;
SET
postgres@[local:/tmp]:1992=#49910 set client_min_messages = log;
SET
postgres@[local:/tmp]:1992=#49910 select * from t1 where name = 'jason' limit 1;
LOG: duration: 0.018 ms plan:
Query Text: select * from t1 where name = 'jason' limit 1;
Limit (cost=0.00..0.02 rows=1 width=10)
-> Seq Scan on t1 (cost=0.00..18.50 rows=999 width=10)
Filter: ((name)::text = 'jason'::text)
id | name
----+-------
2 | jason
(1 row)
与此同时，我们的日志中也有了这条SQL的执行计划：
INFRA [postgres@wqdcsrv3352 log]# tail -f postgresql-2023-01-27.log
2023-01-27 17:43:31 CST [49910]: db=postgres,user=postgres,app=psql,client=[local] LOG: duration: 0.018 ms plan:
Query Text: select * from t1 where name = 'jason' limit 1;
Limit (cost=0.00..0.02 rows=1 width=10)
-> Seq Scan on t1 (cost=0.00..18.50 rows=999 width=10)
Filter: ((name)::text = 'jason'::text)
如果你想全局级别的记录慢SQL的执行计划，需要修改参数shared_preload_libraries：
我们把记录时间设置为60s, 目的是记录一些OLTP的分析和报表功能的执行计划。
auto_explain.log_min_duration 的值设置过低的话， OLTP的高并发SQL的执行计划会大规模写入，需要自己根据数据库的实际情况设置。
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '60s'
shared_preload_libraries 参数生效需要重启数据库：
INFRA [postgres@wqdcsrv3352 backups]# /opt/postgreSQL/pg15/bin/pg_ctl restart -D /data/postgreSQL/1992/backups
我们登陆PSQL客户端执行SQL测试：
postgres@[local:/tmp]:1992=#64074 select * , pg_sleep(60) from t1 limit 1;
查看日志：
2023-01-27 18:09:12 CST [64074]: db=postgres,user=postgres,app=psql,client=[local] LOG: duration: 60060.139 ms plan:
Query Text: select * , pg_sleep(60) from t1 limit 1;
Limit (cost=0.00..0.02 rows=1 width=14)
-> Seq Scan on t1 (cost=0.00..18.50 rows=1000 width=14)
关于收集和查看SQL历史执行计划就简单介绍到这里。熟悉 python 或者其他脚本语言的小伙伴，可以写成定时的JOB脚本来抽取PG日志中的历史统计信息保存到数据库中，这样未来回溯的时候比找日志文件更简单，方便，直观。
接下来，我们来看如何查看正在运行的SQL的执行计划： 这个对于DBA处理生产数据库的SQL性能故障帮助是十分重要的。
这回我们需要安装业界著名公司cybertec 的插件 PG_SHOW_PLANS, 项目的 github 主页： https://github.com/cybertec-postgresql/pg_show_plans
我们还是实战安装体验一下： github 上首页已经说明PG的版本必须是9.0以上的版本，我们这里还是采用PG13.8版本。
postgres@[local:/tmp]:2023=#80536 select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44.0.3), 64-bit
(1 row)
下载插件：
$ git clone https://github.com/cybertec-postgresql/pg_show_plans.git Cloning into 'pg_show_plans'... remote: Enumerating objects: 146, done. remote: Counting objects: 100% (78/78), done. remote: Compressing objects: 100% (54/54), done. remote: Total 146 (delta 48), reused 51 (delta 24), pack-reused 68 Receiving objects: 100% (146/146), 48.35 KiB | 1.24 MiB/s, done. Resolving deltas: 100% (90/90), done.
确保PG实例的机器上 pg_config 中的环境变量是正确的
INFRA [postgres@wqdcsrv3352 pg_show_plans]# pg_config |grep PGXS
PGXS = /opt/postgreSQL/pg13/lib/postgresql/pgxs/src/makefiles/pgxs.mk
编译安装源代码：
INFRA [postgres@wqdcsrv3352 postgreSQL]# cd pg_show_plans
INFRA [postgres@wqdcsrv3352 pg_show_plans]# USE_PGXS=true
INFRA [postgres@wqdcsrv3352 pg_show_plans]# make USE_PGXS=1
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/opt/postgreSQL/pg13/include/postgresql/server -I/opt/postgreSQL/pg13/include/postgresql/internal -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/opt/postgreSQL/pg13/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgreSQL/pg13/lib',--enable-new-dtags
INFRA [postgres@wqdcsrv3352 pg_show_plans]# make USE_PGXS=1 install
/bin/mkdir -p '/opt/postgreSQL/pg13/lib/postgresql'
/bin/mkdir -p '/opt/postgreSQL/pg13/share/postgresql/extension'
/bin/mkdir -p '/opt/postgreSQL/pg13/share/postgresql/extension'
/bin/install -c -m 755 pg_show_plans.so '/opt/postgreSQL/pg13/lib/postgresql/pg_show_plans.so'
/bin/install -c -m 644 .//pg_show_plans.control '/opt/postgreSQL/pg13/share/postgresql/extension/'
/bin/install -c -m 644 .//pg_show_plans--1.0.sql '/opt/postgreSQL/pg13/share/postgresql/extension/'
安装完毕后，接下来我们就可以在 shared_preload_libraries 参数中加载这个 pg_show_plans extension 了
参数文件 postgresql.conf：
shared_preload_libraries = 'pg_show_plans'
重启数据库:
INFRA [postgres@wqdcsrv3352 2023]# /opt/postgreSQL/pg13/bin/pg_ctl restart -D /data/postgreSQL/2023
登录数据库创建extension :
postgres@[local:/tmp]:2023=#93219 create extension pg_show_plans;
CREATE EXTENSION
我们可以看到这个插件会创建一张视图和几个函数
postgres@[local:/tmp]:2023=#93219 \d+ List of relationsSchema | Name | Type | Owner | Persistence | Access method | Size | Description--------+---------------+------+----------+-------------+---------------+---------+-------------public | pg_show_plans | view | postgres | permanent | | 0 bytes |(1 row)postgres@[local:/tmp]:2023=#93219 \df+ List of functionsSchema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description--------+-----------------------+------------------+-------------------------------------------------------------------------------+------+------------+----------+----------+----------+---------------------+----------+-----------------------+-------------public | pg_show_plans | SETOF record | OUT pid bigint, OUT level bigint, OUT userid oid, OUT dbid oid, OUT plan text | func | volatile | unsafe | postgres | invoker | | c | pg_show_plans |public | pg_show_plans_disable | void | | func | volatile | unsafe | postgres | invoker | postgres=X/postgres | c | pg_show_plans_disable |public | pg_show_plans_enable | void | | func | volatile | unsafe | postgres | invoker | postgres=X/postgres | c | pg_show_plans_enable |public | pgsp_format_json | void | | func | volatile | unsafe | postgres | invoker | postgres=X/postgres | c | pgsp_format_json |public | pgsp_format_text | void | | func | volatile | unsafe | postgres | invoker | postgres=X/postgres | c | pgsp_format_text |(5 rows)
pg_show_plans 这个视图是主要查看查询的正在运行SQL执行计划的
那几个函数则是设置视图中输出格式 json 或者 text ， 还有是否 disable , enable 这个 show plan 功能的
我们现在跑一个SQL： sleep 100秒
postgres@[local:/tmp]:2023=#93219 SELECT *, pg_sleep(100) FROM pg_stats;
我们打开另一个终端登录查看正在运行的SQL的执行计划：
postgres@[local:/tmp]:2023=#99880 \x
Expanded display is on.
postgres@[local:/tmp]:2023=#99880 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;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------
pid | 93219
level | 0
plan | Subquery Scan on pg_stats (cost=126.24..153.57 rows=5 width=405) +
| -> Nested Loop Left Join (cost=126.24..153.50 rows=5 width=401) +
| -> Hash Join (cost=126.11..152.20 rows=5 width=475) +
| Hash Cond: ((s.starelid = c.oid) AND (s.staattnum = a.attnum)) +
| -> Seq Scan on pg_statistic s (cost=0.00..22.02 rows=402 width=349) +
| -> Hash (cost=111.80..111.80 rows=954 width=142) +
| -> Hash Join (cost=22.65..111.80 rows=954 width=142) +
| Hash Cond: (a.attrelid = c.oid) +
| Join Filter: has_column_privilege(c.oid, a.attnum, 'select'::text) +
| -> Seq Scan on pg_attribute a (cost=0.00..81.61 rows=2861 width=70) +
| Filter: (NOT attisdropped) +
| -> Hash (cost=17.82..17.82 rows=386 width=72) +
| -> Seq Scan on pg_class c (cost=0.00..17.82 rows=386 width=72) +
| Filter: ((NOT relrowsecurity) OR (NOT row_security_active(oid))) +
| -> Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.13..0.18 rows=1 width=68)+
| Index Cond: (oid = c.relnamespace)
query | SELECT *, pg_sleep(100) FROM pg_stats;
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------
pid | 99880
level | 0
plan | Sort (cost=72.08..74.58 rows=1000 width=80) +
| Sort Key: pg_show_plans.pid, pg_show_plans.level +
| -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) +
| 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=100 width=44)
query | 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;
当然你也可以设置为 json 格式的输出： 调用函数 pgsp_format_json();
postgres@[local:/tmp]:2023=#93219 select pgsp_format_json();
pgsp_format_json
------------------
(1 row)
postgres@[local:/tmp]:2023=#99880 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;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------
pid | 93219
level | 0
plan | { +
| "Plan": { +
| "Node Type": "Subquery Scan", +
| "Parallel Aware": false, +
| "Alias": "pg_stats", +
| "Startup Cost": 126.24, +
| "Total Cost": 153.57, +
| "Plan Rows": 5, +
| "Plan Width": 405, +
| "Plans": [ +
| { +
| "Node Type": "Nested Loop", +
| "Parent Relationship": "Subquery", +
| "Parallel Aware": false, +
| "Join Type": "Left", +
| "Startup Cost": 126.24, +
| "Total Cost": 153.50, +
| "Plan Rows": 5, +
| "Plan Width": 401, +
| "Inner Unique": true, +
| "Plans": [ +
| { +
| "Node Type": "Hash Join", +
| "Parent Relationship": "Outer", +
| "Parallel Aware": false, +
| "Join Type": "Inner", +
| "Startup Cost": 126.11, +
| "Total Cost": 152.20, +
| "Plan Rows": 5, +
| "Plan Width": 475, +
| "Inner Unique": false, +
| "Hash Cond": "((s.starelid = c.oid) AND (s.staattnum = a.attnum))", +
| "Plans": [ +
| { +
| "Node Type": "Seq Scan", +
| "Parent Relationship": "Outer", +
| "Parallel Aware": false, +
| "Relation Name": "pg_statistic", +
| "Alias": "s", +
| "Startup Cost": 0.00, +
| "Total Cost": 22.02, +
| "Plan Rows": 402, +
| "Plan Width": 349 +
| }, +
| { +
| "Node Type": "Hash", +
| "Parent Relationship": "Inner", +
| "Parallel Aware": false, +
| "Startup Cost": 111.80, +
| "Total Cost": 111.80, +
| "Plan Rows": 954, +
| "Plan Width": 142, +
| "Plans": [ +
| { +
| "Node Type": "Hash Join", +
| "Parent Relationship": "Outer", +
| "Parallel Aware": false, +
| "Join Type": "Inner", +
| "Startup Cost": 22.65, +
| "Total Cost": 111.80, +
| "Plan Rows": 954, +
| "Plan Width": 142, +
| "Inner Unique": true, +
| "Hash Cond": "(a.attrelid = c.oid)", +
| "Join Filter": "has_column_privilege(c.oid, a.attnum, 'select'::text)", +
| "Plans": [ +
| { +
| "Node Type": "Seq Scan", +
| "Parent Relationship": "Outer", +
| "Parallel Aware": false, +
| "Relation Name": "pg_attribute", +
| "Alias": "a", +
| "Startup Cost": 0.00, +
| "Total Cost": 81.61, +
| "Plan Rows": 2861, +
| "Plan Width": 70, +
| "Filter": "(NOT attisdropped)" +
| }, +
| { +
| "Node Type": "Hash", +
| "Parent Relationship": "Inner", +
| "Parallel Aware": false, +
| "Startup Cost": 17.82, +
| "Total Cost": 17.82, +
| "Plan Rows": 386, +
| "Plan Width": 72, +
| "Plans": [ +
| { +
| "Node Type": "Seq Scan", +
| "Parent Relationship": "Outer", +
| "Parallel Aware": false, +
| "Relation Name": "pg_class", +
| "Alias": "c", +
| "Startup Cost": 0.00, +
| "Total Cost": 17.82, +
| "Plan Rows": 386, +
| "Plan Width": 72, +
| "Filter": "((NOT relrowsecurity) OR (NOT row_security_active(oid)))"+
| } +
| ] +
| } +
| ] +
| } +
| ] +
| } +
| ] +
| }, +
| { +
| "Node Type": "Index Scan", +
| "Parent Relationship": "Inner", +
| "Parallel Aware": false, +
| "Scan Direction": "Forward", +
| "Index Name": "pg_namespace_oid_index", +
| "Relation Name": "pg_namespace", +
| "Alias": "n", +
| "Startup Cost": 0.13, +
| "Total Cost": 0.18, +
| "Plan Rows": 1, +
| "Plan Width": 68, +
| "Index Cond": "(oid = c.relnamespace)" +
| } +
| ] +
| } +
| ] +
| } +
| }
query | SELECT *, pg_sleep(100) FROM pg_stats;
关于查看正在运行SQL的执行计划，我们就介绍到这里。
最后我们总结一下：
1.PG记录历史执行计划，需要安装插件 auto-explain来实现。 如果全局开启记录执行计划的功能，需要根据自己数据库运行的业务情况，设置合理的记录 参数值log_min_duration。
日志默认会记录到 PG的log中，如果公司有自动化平台的话，可以考虑和ELK集成，采用 Kibana 做可视化的监控。
2.PG的real time 实时统计信息，需要下载安装插件 PG_SHOW_PLANS 来实现。 是DBA 生产故障排除的很好用的工具。
3.Oracle vs Postgres (History & Real time SQL execution)
Have a fun 🙂 !
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
