PostgreSQL扩展--pgstattuple提供统计信息函数

参考：The simple usage of pgstattuple extension

1. 描述
　　pgstattuple提供了pgstatetuple()和pgstatindex()两个统计表和索引的方法，较PostgreSQL系统表pg_class的表统计信息，pgstatetuple()还统计了表中的dead tuples。
2. 使用
　　在数据库中第一次使用pgstattuple时候，需要手动添加pgstattuple扩展

postgres=# create extension pgstattuple ;

• 添加测试表和数据

postgres=# create table tb3(id integer,name character varying);
CREATE TABLE postgres=# alter table tb3 add primary key(id);
ALTER TABLE

postgres=# insert into tb3 select generate_series(1,1000000),md5(random()::text);
INSERT 0 1000000

• 使用pgstattuple()查看该表的统计信息

postgres=# x 
Expanded display is on.
postgres=# select * from pgstattuple('tb3');
-[ RECORD 1 ]------+---------
table_len          | 68272128   --  Physical relation length in bytes
tuple_count        | 1000000    --  Number of live tuples
tuple_len          | 61000000   --  Total length of live tuples in bytes
tuple_percent      | 89.35      --  Percentage of live tuples
dead_tuple_count   | 0          --  Number of deal tuplse
dead_tuple_len     | 0          --  Total length of dead tuples in bytes
dead_tuple_percent | 0          --  Percentage of deadtuples
free_space         | 155452     --  Total free space in bytes
free_percent       | 0.23       --  Percentage of free space

• 使用pgstatindex()查索引的统计信息

postgres=# select * from pgstatindex('tb3_pkey');
-[ RECORD 1 ]------+---------
version            | 2        
tree_level         | 2
index_size         | 22347776    -- Total number of pages in index
root_block_no      | 821
internal_pages     | 2
leaf_pages         | 1361        -- Number of leaf pages
empty_pages        | 0           -- Number of empty pages
deleted_pages      | 0           -- Number of deleted pages
avg_leaf_density   | 90.06       -- Average density of leaf pages
leaf_fragmentation | 0           -- Leaf page fragmentation(可作为索引膨胀的依据。)

• 删除一些数据后看看统计信息

postgres=# delete from tb3 where id%5=0;
DELETE 200000 postgres=# select count(0) from tb3;
 count  
--------
 800000
(1 row)

postgres=# select * from pgstattuple('tb3');
-[ RECORD 1 ]------+---------
table_len          | 68272128
tuple_count        | 800000
tuple_len          | 48800000
tuple_percent      | 71.48
dead_tuple_count   | 200000
dead_tuple_len     | 12200000
dead_tuple_percent | 17.87
free_space         | 155452
free_percent       | 0.23

dead_tuple_count 就是刚刚删除还没有被释放的记录数。

postgres=# select * from pgstatindex('tb3_pkey');
-[ RECORD 1 ]------+---------
version            | 2
tree_level         | 2
index_size         | 22347776
root_block_no      | 821
internal_pages     | 2
leaf_pages         | 1361
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 90.06
leaf_fragmentation | 0

• vacuum

postgres=# vacuum tb3;
VACUUM

postgres=# select * from pgstattuple('tb3');
-[ RECORD 1 ]------+---------
table_len          | 68272128
tuple_count        | 800000
tuple_len          | 48800000
tuple_percent      | 71.48
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 12955452
free_percent       | 18.98

dead_tuple_count变0，free_space 增加。

postgres=# select * from pgstatindex('tb3_pkey');
-[ RECORD 1 ]------+---------
version            | 2
tree_level         | 2
index_size         | 22347776
root_block_no      | 821
internal_pages     | 2
leaf_pages         | 1361
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 72.08
leaf_fragmentation | 0

• vacuum full

postgres=# vacuum full tb3;
VACUUM

postgres=# select * from pgstattuple('tb3');
-[ RECORD 1 ]------+---------
table_len          | 54624256
tuple_count        | 800000
tuple_len          | 48800000
tuple_percent      | 89.34
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 130904
free_percent       | 0.24

空间释放了，free_space 变小。

postgres=# select * from pgstatindex('tb3_pkey');
-[ RECORD 1 ]------+---------
version            | 2
tree_level         | 2
index_size         | 17907712
root_block_no      | 577
internal_pages     | 2
leaf_pages         | 1090
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 89.96
leaf_fragmentation | 0

　　 备注：表未分析前，使用 pg_relpages 函数就能精确查询表的 page 数据，而此时 pg_class 还没数据，说明 pg_relpages 查询了表的 page 物理文件信息。