pg添加插件

下载：
https://github.com/cybertec-postgresql/pg_show_plans

 

[root@pg1 ~]# unzip pg_show_plans-master.zip
Archive: pg_show_plans-master.zip
b9f71815a911a186ffda53d356242a0150a1746b
creating: pg_show_plans-master/
inflating: pg_show_plans-master/.editorconfig
creating: pg_show_plans-master/.github/
creating: pg_show_plans-master/.github/ISSUE_TEMPLATE/
inflating: pg_show_plans-master/.github/ISSUE_TEMPLATE/bug_report.md
creating: pg_show_plans-master/.github/workflows/
inflating: pg_show_plans-master/.github/workflows/installcheck.yml
inflating: pg_show_plans-master/.gitignore
inflating: pg_show_plans-master/LICENSE
inflating: pg_show_plans-master/Makefile
inflating: pg_show_plans-master/README.md
creating: pg_show_plans-master/expected/
inflating: pg_show_plans-master/expected/formats.out
inflating: pg_show_plans-master/expected/formats_1.out
inflating: pg_show_plans-master/expected/pg_show_plans.out
inflating: pg_show_plans-master/pg_show_plans--1.0--1.1.sql
inflating: pg_show_plans-master/pg_show_plans--1.1--2.0.sql
inflating: pg_show_plans-master/pg_show_plans--2.0--2.1.sql
inflating: pg_show_plans-master/pg_show_plans--2.1.sql
inflating: pg_show_plans-master/pg_show_plans.c
inflating: pg_show_plans-master/pg_show_plans.control
linking: pg_show_plans-master/pg_show_plans.md -> README.md
creating: pg_show_plans-master/sql/
inflating: pg_show_plans-master/sql/formats.sql
inflating: pg_show_plans-master/sql/pg_show_plans.sql
finishing deferred symbolic links:
pg_show_plans-master/pg_show_plans.md -> README.md
[root@pg1 ~]# cd pg_show_plans-master/
[root@pg1 pg_show_plans-master]#


[root@pg1 ~]# cd pg_show_plans-master/
[root@pg1 pg_show_plans-master]# make
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 -Wformat -fPIC -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/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 -Wformat -fPIC -shared -o pg_show_plans.so pg_show_plans.o -L/opt/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags
[root@pg1 pg_show_plans-master]# make install
/usr/bin/mkdir -p '/opt/pgsql/lib'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/extension'
/usr/bin/mkdir -p '/opt/pgsql/share/doc/extension'
/usr/bin/install -c -m 755 pg_show_plans.so '/opt/pgsql/lib/pg_show_plans.so'
/usr/bin/install -c -m 644 .//pg_show_plans.control '/opt/pgsql/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 '/opt/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_show_plans.md '/opt/pgsql/share/doc/extension/'
[root@pg1 pg_show_plans-master]#

 


[root@pg1 pg_show_plans-master]# cat /opt/pgsql/data/postgresql.conf|grep shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements,powa,pg_show_plans' # (change requires restart)
[root@pg1 pg_show_plans-master]#

 


postgres=# CREATE EXTENSION pg_show_plans;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
btree_gist | 1.5 | public | support for indexing common datatypes in GiST
file_fdw | 1.0 | public | foreign-data wrapper for flat file access
pg_show_plans | 2.1 | public | show query plans of all currently running SQL statements
pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
powa | 4.1.2 | public | PostgreSQL Workload Analyser-core
(7 rows)

postgres=#

 

##本人工具查看！
CommSQLPlus > param %lib%
CommSQLPlus >
CommSQLPlus >

+-----------------------------+--------+-----------------------------------------+---------------------------------------------------------------------+
|name |unit |setting |short_desc |
+-----------------------------+--------+-----------------------------------------+---------------------------------------------------------------------+
|dynamic_library_path | |$libdir |Sets the path for dynamically loadable modules. |
|local_preload_libraries | | |Lists unprivileged shared libraries to preload into each backend. |
|session_preload_libraries | | |Lists shared libraries to preload into each backend. |
|shared_preload_libraries | |pg_stat_statements,powa,pg_show_plans |Lists shared libraries to preload into server. |
|ssl_library | | |Name of the SSL library. |
+-----------------------------+--------+-----------------------------------------+---------------------------------------------------------------------+


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


pid | level | userid | dbid | plan
------+-------+--------+-------+-------------------------------------------------------------------------------------------
2023 | 0 | 19733 | 13580 | Limit (cost=0.00..16548.12 rows=1 width=570) +
| | | | -> Nested Loop (cost=0.00..3309623738.00 rows=200000 width=570) +
| | | | Join Filter: (a.c = c.c) +
| | | | -> Nested Loop (cost=0.00..1654815832.00 rows=200000 width=380) +
| | | | Join Filter: (a.c = b.c) +
| | | | -> Seq Scan on sbtest1 a (cost=0.00..7926.00 rows=200000 width=190) +
| | | | -> Materialize (cost=0.00..13680.00 rows=200000 width=190) +
| | | | -> Seq Scan on sbtest2 b (cost=0.00..7406.00 rows=200000 width=190)+
| | | | -> Materialize (cost=0.00..13680.00 rows=200000 width=190) +
| | | | -> Seq Scan on sbtest3 c (cost=0.00..7406.00 rows=200000 width=190)
(1 row)

postgres=#

 

 

pg_show_plans.plan_format = text: query plans output format, either of text, json, yaml, and xml.
pg_show_plans.max_plan_length = 16384: query plan maximal length in bytes. This value affects the amount of shared memory the extension asks for, the server may not start if the value is too high.
pg_show_plans.is_enabled = true: enable or disable the extension by assigning to this variable.

 

在PostgreSQL中，可以通过查询系统视图和日志来查看用户执行的查询。

查询系统视图：
使用pg_stat_activity视图可以查看当前活动的数据库会话和查询信息。该视图包含了会话的进程ID、用户名、当前查询、查询开始时间等信息。可以通过以下查询语句查看：
使用pg_stat_activity视图可以查看当前活动的数据库会话和查询信息。该视图包含了会话的进程ID、用户名、当前查询、查询开始时间等信息。可以通过以下查询语句查看：
使用pg_stat_statements扩展可以查看数据库中执行的所有SQL语句的统计信息，包括执行次数、总执行时间、平均执行时间等。首先需要在postgresql.conf配置文件中启用该扩展，然后重新加载配置文件。可以通过以下查询语句查看：
使用pg_stat_statements扩展可以查看数据库中执行的所有SQL语句的统计信息，包括执行次数、总执行时间、平均执行时间等。首先需要在postgresql.conf配置文件中启用该扩展，然后重新加载配置文件。可以通过以下查询语句查看：