PostgreSQL pg_show_plan扩展查看活动session的执行计划

PostgreSQL pg_show_plan扩展查看活动session的执行计划

refer:https://github.com/cybertec-postgresql/pg_show_plans


1，安装

git clone https://github.com/cybertec-postgresql/pg_show_plans.git
cd pg_show_plans
make
make install

git clone https://github.com/cybertec-postgresql/pg_show_plans.git
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package# cd pg_show_plans/
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pg_show_plans# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -Wformat -fPIC -fvisibility=hidden -I. -I./ -I/usr/local/pgsql16/server/include/server -I/usr/local/pgsql16/server/include/internal  -D_GNU_SOURCE   -c -o pg_show_plans.o pg_show_plans.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -Wformat -fPIC -fvisibility=hidden -shared -o pg_show_plans.so pg_show_plans.o -L/usr/local/pgsql16/server/lib    -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql16/server/lib',--enable-new-dtags  -fvisibility=hidden
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package/pg_show_plans# make install
/usr/bin/mkdir -p '/usr/local/pgsql16/server/lib'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/doc/extension'
/usr/bin/install -c -m 755  pg_show_plans.so '/usr/local/pgsql16/server/lib/pg_show_plans.so'
/usr/bin/install -c -m 644 .//pg_show_plans.control '/usr/local/pgsql16/server/share/extension/'
/usr/bin/install -c -m 644 .//pg_show_plans--1.0--1.1.sql .//pg_show_plans--1.1--2.0.sql .//pg_show_plans--2.0--2.1.sql .//pg_show_plans--2.1.sql  '/usr/local/pgsql16/server/share/extension/'
/usr/bin/install -c -m 644 .//pg_show_plans.md '/usr/local/pgsql16/server/share/doc/extension/'


2，配置
2.1,修改配置文件
	shared_preload_libraries = 'pg_stat_statements, pg_cron, pgaudit,pg_show_plans'	# (change requires restart)
2.2，创建扩展
	CREATE EXTENSION pg_show_plans;
	select * from pg_catalog.pg_extension  where extname like '%pg_show_plans%';


3,查看活动session的执行计划
	#1 执行查询pg_show_plans
	SELECT * FROM pg_show_plans;
	
	#2 结合pg_stat_activity系统表，查询活动session更详细的信息
	SELECT p.pid, p.level, a.query, p.plan
	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;

	
4, 相关变量
pg_show_plans.enable 是否可以显示计划。
pg_show_plans.plan_format 它控制查询计划的输出格式。可以选择文本或JSON。默认为文本。
pg_show_plans.max_plan_length 它设置查询计划的最大长度。默认值为8192[字节]。此参数必须设置为整数

ALTER SYSTEM SET pg_show_plans.is_enabled  = on;
ALTER SYSTEM set pg_show_plans.plan_format = 'JSON';

select * from pg_catalog.pg_settings where name like '%pg_show_plans%'