参考：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 物理文件信息。
