pg_show_plans 亦凉 2022-10-29 12:24 197阅读 0赞 os: centos 7.4 db: postgresql 12.6 pg\_show\_plans、pg\_store\_plans 都可以被用来查看 postgresql 当前执行语句的执行计划。 本篇blog介绍 pg\_show\_plans 的初步使用. pg\_store\_plans 可以参考另外一篇blog<<[pg\_store\_plans][pg_store_plans]>> # 版本 # # cat /etc/centos-release CentOS Linux release 7.4.1708 (Core) # yum list installed |grep -i postgresql postgresql12.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-contrib.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-devel.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-docs.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-libs.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-llvmjit.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-odbc.x86_64 13.00.0000-1PGDG.rhel7 @pgdg12 postgresql12-odbc-debuginfo.x86_64 12.02.0000-1PGDG.rhel7 @pgdg-common postgresql12-plperl.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-plpython.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-plpython3.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-pltcl.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-server.x86_64 12.6-1PGDG.rhel7 @pgdg12 postgresql12-tcl.x86_64 2.7.5-1.rhel7 @pgdg12 postgresql12-test.x86_64 12.6-1PGDG.rhel7 @pgdg12 # 下载安装 # # su - postgres $ cat ~/.bash_profile export PGHOME=/usr/pgsql-12 export PGDATA=/var/lib/pgsql/12/data export MANPATH=$PGHOME/share/man:$MANPATH export LD_LIBRARY_PATH=$PGHOME/lib export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib export PATH=$PGHOME/bin:$PATH export TEMP=/tmp export TMPDIR=/tmp $ cd /var/lib/pgsql $ git clone https://github.com/cybertec-postgresql/pg_show_plans.git $ cd pg_show_plans $ USE_PGXS=1 make $ sudo USE_PGXS=1 make /usr/bin/mkdir -p '/usr/pgsql-12/lib' /usr/bin/mkdir -p '/usr/pgsql-12/share/extension' /usr/bin/mkdir -p '/usr/pgsql-12/share/extension' /usr/bin/install -c -m 755 pg_show_plans.so '/usr/pgsql-12/lib/pg_show_plans.so' /usr/bin/install -c -m 644 .//pg_show_plans.control '/usr/pgsql-12/share/extension/' /usr/bin/install -c -m 644 .//pg_show_plans--1.0.sql '/usr/pgsql-12/share/extension/' /usr/bin/mkdir -p '/usr/pgsql-12/lib/bitcode/pg_show_plans' /usr/bin/mkdir -p '/usr/pgsql-12/lib/bitcode'/pg_show_plans/ /usr/bin/install -c -m 644 pg_show_plans.bc '/usr/pgsql-12/lib/bitcode'/pg_show_plans/./ cd '/usr/pgsql-12/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_show_plans.index.bc pg_show_plans/pg_show_plans.bc # 使用 # # vi /var/lib/pgsql/12/data/postgresql.conf shared_preload_libraries = 'pg_show_plans,pg_stat_statements' # systemctl restart postgresql-12.service # su - postgres $ psql psql (12.6) Type "help" for help. postgres=# CREATE EXTENSION pg_show_plans; postgres=# \dxS+ pg_show_plans Objects in extension "pg_show_plans" Object description ---------------------------------- function pg_show_plans() function pg_show_plans_disable() function pg_show_plans_enable() function pgsp_format_json() function pgsp_format_text() view pg_show_plans (6 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 ------+-------+------------------------------------------------------------------------------------------------+-------------------------------------------------------------- 3392 | 0 | Sort (cost=72.08..74.58 rows=1000 width=80) +| SELECT p.pid, p.level, p.plan, a.query + | | Sort Key: pg_show_plans.pid, pg_show_plans.level +| FROM pg_show_plans p + | | -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) +| LEFT JOIN pg_stat_activity a + | | Hash Cond: (pg_show_plans.pid = s.pid) +| ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level; | | 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) | (1 row) 使用 explain 查看 postgres=# explain analyze 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; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=72.08..74.58 rows=1000 width=80) (actual time=0.133..0.135 rows=1 loops=1) Sort Key: pg_show_plans.pid, pg_show_plans.level Sort Method: quicksort Memory: 26kB -> Hash Left Join (cost=2.25..22.25 rows=1000 width=80) (actual time=0.113..0.115 rows=1 loops=1) 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) (actual time=0.017..0.018 rows=1 loops=1) -> Hash (cost=1.00..1.00 rows=100 width=44) (actual time=0.079..0.080 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Function Scan on pg_stat_get_activity s (cost=0.00..1.00 rows=100 width=44) (actual time=0.068..0.071 rows=6 loops=1) Planning Time: 0.317 ms Execution Time: 0.527 ms (12 rows) 参考: https://github.com/cybertec-postgresql/pg\_show\_plans [pg_store_plans]: https://blog.csdn.net/ctypyb2002/article/details/88991144
还没有评论,来说两句吧...