pgstattuple 模块提供了统计信息函数，能精确查询表和索引的详细信息，包括dead tuples 信息，今天测试了 pgstattuple 的用法， 安装和使用比较简单，下面是实验过程。
安装 Pgstattuple
1
2
|
mydb=# create extension pgstattuple;
CREATE EXTENSION
|
创建测试表
创建测试表和插入测试数据，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mydb=> create table test_27 (id integer primary key ,name varchar(32));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_27_pkey" for table "test_27"
CREATE TABLE
mydb=> /d test_27
Table "mydb.test_27"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(32) |
Indexes:
"test_27_pkey" PRIMARY KEY, btree (id)
mydb=> insert into test_27 select generate_series(1,100000),'a';
INSERT 0 100000
|
查询表信息
查询表信息，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mydb=# /x
Expanded display is on.
mydb=# select * from pgstattuple('mydb.test_27');
-[ RECORD 1 ]------+--------
table_len | 3629056
tuple_count | 100000
tuple_len | 3000000
tuple_percent | 82.67
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 16652
free_percent | 0.46
|
备注：上面显示了表的长度 table_len ，表的记录数 tuple_count，和 dead_tuple 等信息。
1
2
3
4
5
6
7
8
9
10
11
12
|
mydb=# /dt+ mydb.test_27
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------+-------+-------+---------+-------------
mydb | test_27 | table | mydb | 3568 kB |
(1 row)
mydb=# select 3629056/1024 kB;
kb
------
3544
(1 row)
|
查询索引信息
1
2
3
4
5
6
7
8
9
10
11
12
|
mydb=# select * From pgstatindex('mydb.test_27_pkey');
-[ RECORD 1 ]------+--------
version | 2
tree_level | 1
index_size | 1802240
root_block_no | 3
internal_pages | 0
leaf_pages | 219
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 89.87
leaf_fragmentation | 0
|
删除数据
1
2
|
mydb=# delete from mydb.test_27 where id < 50001;
DELETE 49000
|
再次查询表信息
1
2
3
4
5
6
7
8
9
10
11
|
mydb=# select * from pgstattuple('mydb.test_27');
-[ RECORD 1 ]------+--------
table_len | 3629056
tuple_count | 50000
tuple_len | 1500000
tuple_percent | 41.33
dead_tuple_count | 49000
dead_tuple_len | 1470000
dead_tuple_percent | 40.51
free_space | 48652
free_percent | 1.34
|
备注：字段 tuple_count，tuple_len，dead_tuple_count 值发生了变化。
查询索引信息
1
2
3
4
5
6
7
8
9
10
11
12
|
mydb=# select * From pgstatindex('mydb.test_27_pkey');
-[ RECORD 1 ]------+--------
version | 2
tree_level | 1
index_size | 1802240
root_block_no | 3
internal_pages | 0
leaf_pages | 219
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 89.87
leaf_fragmentation | 0
|
备注：字段详细信息可参考后面的附件，其中 leaf_fragmentation 字段可作为索引膨胀的依据。
查询表 page 信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mydb=# select * from pg_relpages('mydb.test_27');
-[ RECORD 1 ]----
pg_relpages | 443
mydb=# select relname,relpages from pg_class where relname='test_27';
relname | relpages
---------+----------
test_27 | 0
(1 row)
mydb=# analyze mydb.test_27;
ANALYZE
mydb=# select relname,relpages from pg_class where relname='test_27';
relname | relpages
---------+----------
test_27 | 443
(1 row)
|
备注：表未分析前，使用 pg_relpages 函数就能精确查询表的 page 数据，而此时 pg_class 还没数据说明 pg_relpages 查询了表的 page 物理文件信息。
Vacuum 表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mydb=# select * From pgstattuple('mydb.test_27');
-[ RECORD 1 ]------+--------
table_len | 3629056
tuple_count | 50000
tuple_len | 1500000
tuple_percent | 41.33
dead_tuple_count | 49000
dead_tuple_len | 1470000
dead_tuple_percent | 40.51
free_space | 48652
free_percent | 1.34
mydb=# vacuum mydb.test_27;
VACUUM
mydb=# select * From pgstattuple('mydb.test_27');
-[ RECORD 1 ]------+--------
table_len | 3629056
tuple_count | 50000
tuple_len | 1500000
tuple_percent | 41.33
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 1616652
free_percent | 44.55
|
备注：vacuum 后， dead_tuple 相关信息改变。
附一: Pgstattuple 函数信息
附二: Pgstatindex 函数信息
总结
- pgstattuple 能精确查询表和索引的page 信息，包括表的 free_space ，dead_tuple_count 信息，和索引的
deleted_pages ，leaf_fragmentation 信息，在SQL优化方面，这个模块比较有效，找出膨胀较大的索引和表。
- 由于 pgstattuple 模块会物理查询表和索引的 page 信息，如果是大表，这个步骤花费时间较长，今天只是在
虚拟机上测试，这方面没有准确测试。
原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/237843.html
