注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

The simple usage of pgstattuple extension  

2012-04-26 16:14:52|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

          
           pgstattuple 模块提供了统计信息函数,能精确查询表和索引的详细信息,包括dead tuples 信息,今天测试了下

pgstattuple 的用法,  安装和使用比较简单,下面是实验过程。
     

--1 安装 pgstattuple

 mydb=# create extension pgstattuple;
CREATE EXTENSION
   


--2 创建测试表和插入测试数据

 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

   


--3 查询表信息

 mydb=> \c mydb postgres

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 等信息。

 mydb=# \x
Expanded display is off.

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)

   
--4 查询索引信息

 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
   


--5 删除数据

 mydb=# delete from mydb.test_27 where id < 50001;
DELETE 49000


--6 再次查询表信息
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 值发生了变化。


--6  查询索引信息

 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 字段可作为索引膨胀的依据。

 

--7  查询表 page 信息

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

--8 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   | 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 相关信息改变。
       
--9  附 pgstattuple 函数信息

The simple usage of pgstattuple extension - francs - My DBA LIFE


--10  附 pgstatindex 函数信息        

The simple usage of pgstattuple extension - francs - My DBA LIFE

 

-11 总结

    1 pgstattuple 能精确查询表和索引的page 信息,包括表的 free_space ,dead_tuple_count 信息,和索引的

       deleted_pages ,leaf_fragmentation 信息,在SQL优化方面,这个模块比较有效,找出膨胀较大的索引和表。

    2  由于 pgstattuple 模块会物理查询表和索引的 page 信息,如果是大表,这个步骤花费时间较长,今天只是在

        虚拟机上测试,这方面没有准确测试。

  评论这张
 
阅读(26122)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016