添加图片注释，不超过 140 字（可选）

第1章简介

1.1 参考文档

https://www.percona.com/blog/postgresql-tuple-level-statistics-with-pgstattuple/

1.2 关于pgstattuble

由于Postgres表膨胀会降低数据库性能，因此我们可以通过消除表膨胀来提高其性能。我们可以使用pgstattuple扩展来识别膨胀的表。

这个扩展提供了几个函数来获取元级统计信息。因为pgstattuple函数产生大量的页面级信息，所以默认情况下对它们的访问是有限的。默认情况下，只有pg_stat_scan_tables角色有能力执行pgstattuple函数。

使用pgstattuple函数，我们可以列出死元组百分比高的表，并运行手动VACUUM来回收死元组占用的空间。

第2章 pgstattuble 插件安装 & 测试

2.1 PG15 & pg_tde编译

2.1.1 操作系统信息

我的操作系统是CentOS 8.5，其内核信息如下

[root@pg-server01 ~]# uname -aLinux pg-server01 4.18.0-348.el8.x86_64 #1 SMP Tue Oct 19 15:14:17 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux[root@pg-server01 ~]# cat /etc/redhat-release CentOS Linux release 8.5.2111

2.1.2 配置阿里云 yum 源

# 参考如下链接 # 操作步骤略

https://www.cnblogs.com/hunttown/p/16287988.html

2.1.3 安装相关依赖

yum -y install gcc readline readline-devel zlib-devel openssl-devel libicu-devel make json-c-devel git wget

2.1.4 下载PG15 源码&编译

mkdir -p /data/software/pg/;cd /data/software/pg/;wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz tar -xvf postgresql-15.4.tar.gz;cd ./postgresql-15.4;./configure \--prefix=/opt/pgsql \--with-openssl make && make install

2.1.5 编译 pgstattuble 插件

cd /data/software/pg/postgresql-15.4/;cd ./contrib/pgstattuple;make && make install

2.1.6 编译 btree_gin 插件

cd /data/software/pg/postgresql-15.4/;cd ./contrib/btree_gin;make && make install

2.2 安装配置PG

2.2.1 添加 postgres用户并授权

useradd postgres chown -R postgres:postgres /home/postgres;chown -R postgres:postgres /opt/pgsql;

2.2.2 配置环境变量

vi /etc/profile # 添加如下内容# pg envexport PGHOME=/opt/pgsqlexport PATH=$PATH:$PGHOME/bin

2.2.3 初始化数据库

# 1. 创建相关目录，并授权

mkdir -p /data/pgdata/;chown postgres:postgres /data/pgdata/;

# 2. 切换到 postgres 用户

su - postgres

# 3. 初始化数据库

initdb -D /data/pgdata/ -U postgres --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8

# 4. 启动数据库

pg_ctl -D /data/pgdata/ start

2.3 测试 pgstattuble 插件

2.3.1 开启 pgstattuble 插件

su - postgrespsqlselect * from pg_available_extensions where name='pgstattuple';create extension pgstattuple;select * from pg_available_extensions where name='pgstattuple'; # 我的操作输出类似如下：[root@pg-server01 opt]# su - postgres[postgres@pg-server01 ~]$ psqlpsql (15.4)Type "help" for help. postgres=# select * from pg_available_extensions where name='pgstattuple';    name     | default_version | installed_version |           comment           -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5             |                   | show tuple-level statistics(1 row) postgres=# create extension pgstattuple;CREATE EXTENSIONpostgres=# select * from pg_available_extensions where name='pgstattuple';    name     | default_version | installed_version |           comment           -------------+-----------------+-------------------+----------------------------- pgstattuple | 1.5             | 1.5               | show tuple-level statistics(1 row) postgres=#

# 注意：默认情况下，只有超级用户可以访问 pgstattuple 函数; 但是，您可以通过将 pg_stat_scan_tables 角色授予非超级用户来授予非超级用户访问权限。

grant pg_stat_scan_tables to <nonsuperuser>;

2.3.2 pgstattuble测试前准备

接下来，在使用 pgstattuple 函数之前，让我们创建一个表和索引来进行演示。

psqlcreate table workshop (jobno int);insert into workshop values (generate_series(1,80000));create index workshop_index on workshop (jobno);

# 我的操作输出类似如下：

[postgres@pg-server01 ~]$ psqlpsql (15.4)Type "help" for help.postgres=# create table workshop (jobno int);CREATE TABLEpostgres=# insert into workshop values (generate_series(1,80000));INSERT 0 80000postgres=# create index workshop_index on workshop (jobno);CREATE INDEXpostgres=#

2.3.3 pgstattuple 功能

2.3.3.1 pgstattuple(regclass)

这个函数返回一个关系的物理长度，“死亡”元组的百分比，以及其他信息。这可以帮助用户确定是否需要真空。参数是目标关系的名称(可选的模式限定)或OID。例如：

# 显示 pg_catalog.pg_proc 表 的元组统计信息。

SELECT * FROM pgstattuple('pg_catalog.pg_proc');

# 显示 workshop 表 的元组统计信息。

SELECT * FROM pgstattuple('workshop');

# 注意： Pgstattuple 只获得关系上的读锁。因此，pgstattuple 输出并不表示瞬时快照。并发更新将改变 pgstattuple 的输出。

# 我的操作输出类似如下：

postgres=# \xExpanded display is on.postgres=# SELECT * FROM pgstattuple('pg_catalog.pg_proc');-[ RECORD 1 ]------+-------table_len          | 786432tuple_count        | 3253tuple_len          | 731586tuple_percent      | 93.03dead_tuple_count   | 12dead_tuple_len     | 4790dead_tuple_percent | 0.61free_space         | 22564free_percent       | 2.87 postgres=# SELECT * FROM pgstattuple('workshop'); -[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 80000tuple_len          | 2240000tuple_percent      | 77.24dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 10056free_percent       | 0.35

2.3.3.2 下面是pgstattuple输出列及其说明

-------------------------------------------------------------------|        字段名       |   类型   |              说明                |-------------------------------------------------------------------| table_len          | bigint  | 物理关系长度，以字节为单位         |-------------------------------------------------------------------| tuple_count        | bigint  | 活动元组的数量                    |-------------------------------------------------------------------| tuple_len          | bigint  | 活元组的总长度(以字节为单位)       |-------------------------------------------------------------------| tuple_percent      | float8  | 活元组的百分比                    |-------------------------------------------------------------------| dead_tuple_count   | bigint  | 无效(死)元组的数量                |-------------------------------------------------------------------| dead_tuple_len     | bigint  | 无效(死)元组的总长度(以字节为单位) |-------------------------------------------------------------------| dead_tuple_percent | float8  | 无效(死)元组的百分比              |-------------------------------------------------------------------| free_space         | bigint  | 总可用空间(以字节为单位)          |------------------------------------------------------------------| free_percent       | float8  | 可用空间百分比                   |------------------------------------------------------------------

2.3.3.3 让我们在删除或更新行时检查元组统计信息

# 1. 下面是引用的当前元组统计信息。

postgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 80000tuple_len          | 2240000tuple_percent      | 77.24dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 10056free_percent       | 0.35 # 2. Delete the few ROWS from the table.DELETE FROM workshop WHERE jobno % 8 = 0;

# 3. 在下面的输出中，dead_tuple_count 显示 Postgres 将这些行标记为已删除，但没有从表中删除它们，因为删除这些行后表的长度是相同的。

postgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 70000tuple_len          | 1960000tuple_percent      | 67.59dead_tuple_count   | 10000dead_tuple_len     | 280000dead_tuple_percent | 9.66free_space         | 10056free_percent       | 0.35

2.3.3.4 在表上执行VACUUM命令

# 1. 运行普通VACUUM后，我们看到:

vacuum workshop;SELECT * FROM pgstattuple('workshop');

# 2. 在运行VACUUM FULL命令后，我们可以看到 table_len 减少了。显示运行VACUUM FULL后，os级空间被回收。

vacuum full workshop;SELECT * FROM pgstattuple('workshop');

# 我的操作输出类似如下：

postgres=# vacuum workshop;VACUUMpostgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2899968tuple_count        | 70000tuple_len          | 1960000tuple_percent      | 67.59dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 330412free_percent       | 11.39 postgres=# vacuum full workshop;VACUUMpostgres=# SELECT * FROM pgstattuple('workshop');-[ RECORD 1 ]------+--------table_len          | 2539520tuple_count        | 70000tuple_len          | 1960000tuple_percent      | 77.18dead_tuple_count   | 0dead_tuple_len     | 0dead_tuple_percent | 0free_space         | 10840free_percent       | 0.43

2.3.3.5 pgstattuple 查询检查表膨胀

# 1. 我们可以使用下面的查询列出死元组百分比高的表。

## 注意：如果pg16.0 版本的话， 下面查询语句将报错 "ERROR:  only heap AM is supported"，是pg16 的一个 bug，参考：https://www.postgresql.org/message-id/202309200826.z3ckjb4g7auj%40alvherre.pgsql

select relname,(pgstattuple(oid)).dead_tuple_percent from pg_class where relkind = 'r' order by dead_tuple_percent desc limit 10;

## 我的查询输出类似如下：

postgres=# \xExpanded display is off.postgres=# postgres=# select relname,(pgstattuple(oid)).dead_tuple_percent postgres-# from pg_class postgres-# where relkind = 'r' postgres-# order by dead_tuple_percent desc limit 10;        relname        | dead_tuple_percent -----------------------+-------------------- pg_init_privs         |               2.56 pg_extension          |               1.28 pg_class              |               0.75 pg_proc               |               0.61 pg_depend             |               0.47 pg_statistic          |               0.36 pg_type               |               0.29 pg_attribute          |               0.06 pg_user_mapping       |                  0 pg_statistic_ext_data |                  0(10 rows)

# 2. 下面的查询将向您详细显示死元组百分比高的表的元组统计信息。

SELECT relname, oid, relowner,  (pgstattuple(oid)).* FROM pg_class WHERE relkind = 'r' order by dead_tuple_percent desc limit 5;

## 我的操作输出类似如下：

postgres=# SELECT relname, oid, relowner,postgres-#   (pgstattuple(oid)).* postgres-# FROM pg_class postgres-# WHERE relkind = 'r' order by dead_tuple_percent desc limit 5;-[ RECORD 1 ]------+--------------relname            | pg_init_privsoid                | 3394relowner           | 10table_len          | 24576tuple_count        | 222tuple_len          | 17316tuple_percent      | 70.46dead_tuple_count   | 9dead_tuple_len     | 630dead_tuple_percent | 2.56free_space         | 4568free_percent       | 18.59-[ RECORD 2 ]------+--------------relname            | pg_extensionoid                | 3079relowner           | 10table_len          | 8192tuple_count        | 2tuple_len          | 210tuple_percent      | 2.56dead_tuple_count   | 1dead_tuple_len     | 105dead_tuple_percent | 1.28free_space         | 7816free_percent       | 95.41-[ RECORD 3 ]------+--------------relname            | pg_classoid                | 1259relowner           | 10table_len          | 114688tuple_count        | 412tuple_len          | 79912tuple_percent      | 69.68dead_tuple_count   | 5dead_tuple_len     | 860dead_tuple_percent | 0.75free_space         | 28656free_percent       | 24.99-[ RECORD 4 ]------+--------------relname            | pg_procoid                | 1255relowner           | 10table_len          | 786432tuple_count        | 3253tuple_len          | 731586tuple_percent      | 93.03dead_tuple_count   | 12dead_tuple_len     | 4790dead_tuple_percent | 0.61free_space         | 22564free_percent       | 2.87-[ RECORD 5 ]------+--------------relname            | pg_dependoid                | 2608relowner           | 10table_len          | 114688tuple_count        | 1854tuple_len          | 90846tuple_percent      | 79.21dead_tuple_count   | 11dead_tuple_len     | 539dead_tuple_percent | 0.47free_space         | 2288free_percent       | 1.99

2.3.4 pgstatindex(regclass)

2.3.4.1 这个函数返回一条显示b树索引信息的记录

# 1. 例如

SELECT * FROM pgstatindex ('workshop_index');

## 我的查询输出类似如下：

postgres=# SELECT * FROM pgstatindex ('workshop_index');-[ RECORD 1 ]------+--------version            | 4tree_level         | 1index_size         | 1589248root_block_no      | 3internal_pages     | 1leaf_pages         | 192empty_pages        | 0deleted_pages      | 0avg_leaf_density   | 89.74leaf_fragmentation | 0

2.3.4.2 输出列及其描述

-------------------------------------------------------------------|        字段名       |   类型   |              说明                |-------------------------------------------------------------------| version           | integer  | b树版本号                        |-------------------------------------------------------------------| tree_level        | integer  | 根页面的树级别                    |-------------------------------------------------------------------| index_size        | bigint   | 总索引大小(以字节为单位)          |-------------------------------------------------------------------| root_block_no     | bigint   | 根页面的位置(如果没有则为零)       |-------------------------------------------------------------------| internal_pages    | bigint   | “内部”(上层)页面的数量            |-------------------------------------------------------------------| leaf_pages        | bigint   | 叶子结点的个数                    |-------------------------------------------------------------------| empty_pages       | bigint   | 空页的个数                       |-------------------------------------------------------------------| deleted_pages     | bigint   | 删除页面数                       |-------------------------------------------------------------------

2.3.5 pgstatginindex(regclass)

2.3.5.1 这个函数返回一条显示GIN索引信息的记录

 
GIN索引参考：

https://pganalyze.com/blog/gin-index

# 1. 例1

## 1.1 前期 建表、索引等相关 SQL

-- DROP TABLE IF EXISTS test;CREATE TABLE test (  id bigserial PRIMARY KEY,  data jsonb);INSERT INTO test(data) VALUES ('{"field": "value1"}');INSERT INTO test(data) VALUES ('{"field": "value2"}');INSERT INTO test(data) VALUES ('{"other_field": "value42"}');-- CREATE INDEX ON test USING gin(data jsonb_path_ops);CREATE INDEX test_data_gin_idx ON test USING gin(data jsonb_path_ops);EXPLAIN SELECT * FROM test WHERE data @> '{"field": "value1"}';

## 1.2 测量GIN挂起列表开销和大小

### 1.2.1 首先，我们可以将pgstatginindex函数与类似psql的\watch命令一起使用，以密切关注特定索引

SELECT * FROM pgstatginindex('test_data_gin_idx');

# 我的操作输出类似如下：

postgres=# SELECT * FROM pgstatginindex('test_data_gin_idx'); version | pending_pages | pending_tuples ---------+---------------+----------------       2 |             0 |              0(1 row)

## 注意：如果在创建索引的时候不指定”索引名称”，类似语句 "CREATE INDEX ON test USING gin(data jsonb_path_ops);"，需要先执行类似如下查询，查到其信息（包括索引名称），再根据其查询到的索引名称，将其传参到上面查询（当然：其实如果不指定索引名称的话，其索引命名也是有规律的）。

select *from pg_indexeswhere tablename = 'test';

### 1.2.2 其次，如果您运行自己的数据库服务器，您可以使用 “perf”动态跟踪点 来测量对 Postgres 中 ginInsertCleanup 函数的调用

dnf install perfsudo perf probe -x /opt/pgsql/bin/postgres ginInsertCleanupsudo perf stat -a -e probe_postgres:ginInsertCleanup -- sleep 60

# 2. 例2

## 2.1 前期 建表、索引等相关 SQL

-- DROP TABLE IF EXISTS records;CREATE TABLE records (  id bigserial PRIMARY KEY,  customer_id int4,  data jsonb); CREATE EXTENSION btree_gin;CREATE INDEX ON records USING gin (data, customer_id); EXPLAIN SELECT * FROM records WHERE customer_id = 123 AND data @> '{ "location": "New York" }';EXPLAIN SELECT * FROM records WHERE customer_id = 123; select *from pg_indexeswhere tablename =  'records';

## 注意：如下查询中的 "records_data_customer_id_idx" 就是 上面查询结果的 indexname 字段值

SELECT * FROM pgstatginindex('records_data_customer_id_idx');

# 3. pganalyze索引顾问中的GIN索引支持

## 注意：在 https://pganalyze.com/index-advisor 页面测试

## 现在，我们已经向pganalyze index Advisor添加了对GIN和GIST索引建议的初始支持

## 下面是一个为现有的tsvector列推荐GIN索引的示例

CREATE TABLE post(  id SERIAL PRIMARY KEY,  title TEXT NOT NULL,  content TEXT NOT NULL,  author_id INT NOT NULL,  language text NOT NULL DEFAULT('english'),  document tsvector); SELECT id, title FROM postWHERE post.document @@ to_tsquery('english', 'Endangered & Species')ORDER BY ts_rank(post.document, to_tsquery('english', 'Endangered & Species')) DESC

2.3.6 pgstathashindex(regclass)

2.3.6.1 参考文档

https://postgrespro.com/blog/pgsql/4161321

https://postgrespro.com/docs/postgrespro/15/demodb-bookings-installation

2.3.6.2 测试

# 1. 例如

## 1.1 前期准备

### 1.1.1 从 https://postgrespro.com/docs/postgrespro/15/demodb-bookings-installation 下载 demo-small-en.zip 文件到 /data/software/pg/demo-data/ 并解压（解压后生成 demo-small-en-20170815.sql 文件

### 1.1.2 postgres 用户下执行如下命令

cd /data/software/pg/demo-data/;

psql -f demo-small-en-20170815.sql

## 1.2 为 demo 数据库的 flights 表的 flight_no 字段 创建 hash 索引 & 相关测试

### 1.2.1 创建 hash 索引

psql

\c demo

create index on flights using hash(flight_no);

### 1.2.2 创建 pgstattuple 扩展

#### 因为切换到 demo 数据库了，所以需要重新创建 pgstattuple 扩展

create extension pgstattuple;select * from pg_available_extensions where name='pgstattuple';

### 1.2.3 查询刚才创建的索引

select *from pg_indexeswhere tablename =  'flights';

### 1.2.4 查看相关 sql 执行计划 & 查看相关索引信息

explain (costs off) select * from flights where flight_no = 'PG0001';select * from pgstathashindex('flights_flight_no_idx');

# 最后：我的查询输出类似如下

demo=# explain (costs off) select * from flights where flight_no = 'PG0001';                     QUERY PLAN                     ---------------------------------------------------- Bitmap Heap Scan on flights   Recheck Cond: (flight_no = 'PG0001'::bpchar)   ->  Bitmap Index Scan on flights_flight_no_idx         Index Cond: (flight_no = 'PG0001'::bpchar)(4 rows) demo=# select * from pgstathashindex('flights_flight_no_idx'); version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent    ---------+--------------+----------------+--------------+--------------+------------+------------+-------------------       4 |          128 |             13 |            1 |            0 |      33121 |          0 | 42.36979656038809(1 row)

#PG数据库工程师的摇篮#PostgreSQL考试#PostgreSQL培训

添加图片注释，不超过 140 字（可选）

ITPub博客

PostgreSQL 元组统计与 pgstattuple 优化

第1章简介

1.1 参考文档

1.2 关于pgstattuble

第2章 pgstattuble 插件安装 & 测试

2.1 PG15 & pg_tde编译

2.1.1 操作系统信息

2.1.2 配置阿里云 yum 源

2.1.3 安装相关依赖

2.1.4 下载PG15 源码&编译

2.1.5 编译 pgstattuble 插件

2.1.6 编译 btree_gin 插件

2.2 安装配置PG

2.2.1 添加 postgres用户并授权

2.2.2 配置环境变量

2.2.3 初始化数据库

2.3 测试 pgstattuble 插件

2.3.1 开启 pgstattuble 插件

2.3.2 pgstattuble测试前准备

2.3.3 pgstattuple 功能

2.3.3.1 pgstattuple(regclass)

2.3.3.2 下面是pgstattuple输出列及其说明

2.3.3.3 让我们在删除或更新行时检查元组统计信息

2.3.3.4 在表上执行VACUUM命令

2.3.3.5 pgstattuple 查询检查表膨胀

2.3.4 pgstatindex(regclass)

2.3.4.1 这个函数返回一条显示b树索引信息的记录

2.3.4.2 输出列及其描述

2.3.5 pgstatginindex(regclass)

2.3.5.1 这个函数返回一条显示GIN索引信息的记录

2.3.6 pgstathashindex(regclass)

2.3.6.1 参考文档

2.3.6.2 测试

最新文章

ITPub博客

PostgreSQL 元组统计与 pgstattuple 优化

第1章 简介

1.1 参考文档

1.2 关于pgstattuble

第2章 pgstattuble 插件安装 & 测试

2.1 PG15 & pg_tde编译

2.1.1 操作系统信息

2.1.2 配置阿里云 yum 源

2.1.3 安装相关依赖

2.1.4 下载PG15 源码&编译

2.1.5 编译 pgstattuble 插件

2.1.6 编译 btree_gin 插件

2.2 安装配置PG

2.2.1 添加 postgres用户并授权

2.2.2 配置环境变量

2.2.3 初始化数据库

2.3 测试 pgstattuble 插件

2.3.1 开启 pgstattuble 插件

2.3.2 pgstattuble测试前准备

2.3.3 pgstattuple 功能

2.3.3.1 pgstattuple(regclass)

2.3.3.2 下面是pgstattuple输出列及其说明

2.3.3.3 让我们在删除或更新行时检查元组统计信息

2.3.3.4 在表上执行VACUUM命令

2.3.3.5 pgstattuple 查询检查表膨胀

2.3.4 pgstatindex(regclass)

2.3.4.1 这个函数返回一条显示b树索引信息的记录

2.3.4.2 输出列及其描述

2.3.5 pgstatginindex(regclass)

2.3.5.1 这个函数返回一条显示GIN索引信息的记录

2.3.6 pgstathashindex(regclass)

2.3.6.1 参考文档

2.3.6.2 测试

最新文章

第1章简介