The simple usage of Pgstattuple extension

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 函数信息

总结

  1. pgstattuple 能精确查询表和索引的page 信息,包括表的 free_space ,dead_tuple_count 信息,和索引的
    deleted_pages ,leaf_fragmentation 信息,在SQL优化方面,这个模块比较有效,找出膨胀较大的索引和表。
  2. 由于 pgstattuple 模块会物理查询表和索引的 page 信息,如果是大表,这个步骤花费时间较长,今天只是在
    虚拟机上测试,这方面没有准确测试。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%