#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)
