Postgres数据库分析工具

Postgres号称是开源免费DBMS最强大的，并且支持二次开发。本文就该DBMS的分析工具进行讨论。 1. 判断磁盘的使用量 pg_class 表记载表和几乎所有有 字段或者是那些类似表的东西。包括索引（不过还要参阅 pg_index ），序列，视图，复合类型和一些特殊关系类型

Postgres号称是开源免费DBMS最强大的，并且支持二次开发。本文就该DBMS的分析工具进行讨论。

1. 判断磁盘的使用量

pg_class表记载表和几乎所有有 字段或者是那些类似表的东西。包括索引（不过还要参阅 pg_index），序列，视图，复合类型和一些特殊关系类型。也有pg_index这张表，包含关于索引的一部分信息。 其他的信息大多数在 pg_class。

常用的字段有

relname（表，索引，视图等的名字); relpages(以页的此表在磁盘上的形式的大小。它只是规划器用的一个近似值，是由VACUUM， ANALYE, 和几个DLL命令，比如Crate Index更新) reltuples（表中元组的数据，也是规划器的近似值）

每个表都有一个主堆的磁盘文件，大多数数据都存储在这里。如果一个表有着可能会很长的字段， 则另外还有一个TOAST文件与这个表相关联， 用于存储因为数值太长而不能存储在主表里面的数据，如果有这个扩展表，那么表上会有一个TOAST索引。

这里对TOAST进行简单说明： TOAST即超尺寸字段存储技巧(The Oversized-Attribute Storage Technique), 因为DBMS的页面设定一般是固定的，PostgreSQL为8kb, 并且不允许跨越多个页面，因此不可能存储非常大的字段。在以前的Postgres版本里，代码里强制一个表中一个数据行存储的数据的总大小为刚好略小于一个页面；在新版本的Postgres，允许大的字段值被压缩或打碎成多个物理行，但这些操作对用户都是透明的。如果一个表中有任何一个字段是可以TOAST的，那么该表将有一个关联的TOAST表，其 OID （对象标识符）存储在表的pg_class.reltoastrelid 记录里，线外TOAST过的数值保存在TOAST表里。

查看方法：从 psql 里使用 VACUUM 的信息，从psql 里使用 contrib/dbsize 里面的工具，以及在命令行上使用contrib/oid2name 里面的工具垃圾收集以及可选地分析一个数据库。（这里涉及到其他关于数据库维护、性能的知识将在笔者之后的博客里介绍)。由于我们在分析前一般会完成数据库的连接操作，所有可以执行查询语句，

select relpages, reltuples from pg_class where relname='待分析表名';

也可以很容易地显示索引的用量：

select c2.relname, c2.relpages
     from pg_class c, pg_class c2, pg_index i
    where c.relname = '表名'
     and c.oid = i.indrelid
     and c2.oid = i.indexrelid
 Order by c2.relname;

2. 查询计划

使用时只需在原来的语句上加上 explian关键字。

explain select * from customer where customer_id=12;

结果如下：

注:笔者数据库里的customer有24999个元组。

Cost指示了磁盘-页面存取的估计次数，和CUP在磁盘页面的计算时间。在上面的例子里，先使用了顺序扫描（Seq Scan）。在customer表里的行平均有16byte的宽度，这个查询只访问了1行，没有启动代价(start-up cost)，总代价为4565.99（以磁盘-页面存取单元表示）。

使用explain analyze命令同时可以显示时间运行时间(毫秒）和实际磁盘页面存取次数，

结果表明，估计需要4565.99次磁盘页面存取次数，而实际只有108.643次.