PG自带插件安装 示例 pgstattuple

#1.检查插件包 和 脚本 postgres@s2ahumysqlpg01-> pwd /u01/postgresql/pg12/lib postgres@s2ahumysqlpg01-> ls -l pg* -rwxr-xrwx. 1 postgres postgres 13192 Nov 23 15:13 pg_buffercache.so -rwxr-xrwx. 1 postgres postgres 150112 Nov 23 15:13 pgcrypto.so -rwxr-xrwx. 1 postgres postgres 8584 Nov 23 15:13 pg_freespacemap.so -rwxr-xrwx. 1 postgres postgres 19112 Nov 23 15:13 pgoutput.so -rwxr-xrwx. 1 postgres postgres 30104 Nov 23 15:13 pg_prewarm.so -rwxr-xrwx. 1 postgres postgres 18264 Nov 23 15:13 pgrowlocks.so -rwxr-xr-x. 1 postgres postgres 42632 Nov 25 15:09 pgsentinel.so -rwxr-xr-x. 1 postgres postgres 29800 Nov 25 16:19 pg_stat_kcache.so -rwxr-xrwx. 1 postgres postgres 43880 Nov 23 15:13 pg_stat_statements.so -rwxr-xrwx. 1 postgres postgres 34440 Nov 23 15:13 pgstattuple.so # 自带的包都在 -rwxr-xrwx. 1 postgres postgres 57968 Nov 23 15:13 pg_trgm.so -rwxr-xrwx. 1 postgres postgres 23632 Nov 23 15:13 pg_visibility.so postgres@s2ahumysqlpg01-> pwd /u01/postgresql/pg12/share/extension postgres@s2ahumysqlpg01-> ls -l pgstat* -rw-r--rw-. 1 postgres postgres 400 Nov 23 15:13 pgstattuple--1.0--1.1.sql -rw-r--rw-. 1 postgres postgres 1435 Nov 23 15:13 pgstattuple--1.1--1.2.sql -rw-r--rw-. 1 postgres postgres 1126 Nov 23 15:13 pgstattuple--1.2--1.3.sql -rw-r--rw-. 1 postgres postgres 623 Nov 23 15:13 pgstattuple--1.3--1.4.sql -rw-r--rw-. 1 postgres postgres 5506 Nov 23 15:13 pgstattuple--1.4--1.5.sql -rw-r--rw-. 1 postgres postgres 3764 Nov 23 15:13 pgstattuple--1.4.sql -rw-r--rw-. 1 postgres postgres 147 Nov 23 15:13 pgstattuple.control -rw-r--rw-. 1 postgres postgres 457 Nov 23 15:13 pgstattuple--unpackaged--1.0.sql #2.查看是否安装 postgres=# select * from pg_available_extensions where name like '%pgstattuple%' ; name | default_version | installed_version | comment -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5 | | show tuple-level statistics (1 row) #3.安装插件 postgres=# create extension pgstattuple ; CREATE EXTENSION #4.查看已安装插件 postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+-------------------------------------------------------------- dblink | 1.2 | public | connect to other PostgreSQL databases from within a database pg_profile | 0.3.6 | profile | PostgreSQL load profile repository and report builder pg_stat_kcache | 2.2.0 | public | Kernel statistics gathering pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed pgsentinel | 1.0 | public | active session history pgstattuple | 1.5 | public | show tuple-level statistics plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (7 rows) #5.查看插件带的函数 postgres=# \dxS+ pgstattuple Objects in extension "pgstattuple" Object description --------------------------------------- function pg_relpages(regclass) function pg_relpages(text) function pgstatginindex(regclass) function pgstathashindex(regclass) function pgstatindex(regclass) function pgstatindex(text) function pgstattuple(regclass) function pgstattuple(text) function pgstattuple_approx(regclass) (9 rows) #6.使用函数 postgres=# select * from pgstattuple('public.test'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 16384 | 2 | 68 | 0.42 | 0 | 0 | 0 | 16268 | 99.29 (1 row)