Postgres中数据字典的用法（一）

86 阅读 0 评论 57 点赞

我是靠谱客的博主怕孤独嚓茶，最近开发中收集的这篇文章主要介绍Postgres中数据字典的用法（一），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

数据字典

数据字典（Data dictionary）是一种用户可以访问的记录数据库和应用程序元数据的目录。postgresql中常用的数据字典视图有：pg_class,pg_stat_all_indexes，pg_index，information_schema，pg_attribute，pg_type，pg_constraint
熟练运作这些数据字典视图，可以很快的得到数据库的数据表、索引、序列的详细信息。

1.pg_class

目录pg_class记录表和几乎所有具有列或者像表的东西。这包括索引（但还要参见pg_index）、序列（但还要参见pg_sequence）、视图、物化视图、组合类型和TOAST表，

postgres=# d pg_class;
                  数据表 "pg_catalog.pg_class"
        栏位         |     类型     |  可空的  | 含义
---------------------+--------------+----------+------
 relname             | name         | not null |表、索引、视图等的名字
 relnamespace        | oid          | not null |包含该关系的名字空间的OID
 reltype             | oid          | not null |可能存在的表行类型所对应数据类型的OID（对索引为0，索引没有pg_type项
 reloftype           | oid          | not null |对于有类型的表，为底层组合类型的OID，对于其他所有关系为0
 relowner            | oid          | not null |关系的拥有者
 relam               | oid          | not null |如果这是一个索引，表示索引使用的访问方法（B树、哈希等）
 relfilenode         | oid          | not null |该关系的磁盘文件的名字，0表示这是一个“映射”关系，其磁盘文件名取决于低层状态
 reltablespace       | oid          | not null |该关系所存储的表空间。如果为0，使用数据库的默认表空间。
 relpages            | integer      | not null |该表磁盘表示的尺寸，以页面计（页面尺寸为BLCKSZ）。这只是一个
            由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令（如CREATE INDEX）所更新
 reltuples           | real         | not null |表中的存活行数。这只是一个由规划器使用的估计值。
 relallvisible       | integer      | not null |在表的可见性映射表中被标记为全可见的页数。这只是一个由规划器使用的估计值。
 reltoastrelid       | oid          | not null |与该表相关联的TOAST表的OID，如果没有则为0。
 relhasindex         | boolean      | not null |如果这是一个表并且其上建有（或最近建有）索引则为真
 relisshared         | boolean      | not null |如果该表在集簇中的所有数据库间共享则为真。只有某些系统目录
                                                （如pg__database）是共享的。
 relpersistence      | "char"       | not null |p = 永久表，u = 无日志表， t = 临时表
 relkind             | "char"       | not null |r = 普通表， i = 索引， S = 序列， t =TOAST表， v = 视图，
            m = 物化视图， c =组合类型， f = 外部表， p = 分区表， I= 分区索引
 relnatts            | smallint     | not null |关系中用户列的数目
 relchecks           | smallint     | not null |表上CHECK约束的数目
 relhasoids          | boolean      | not null |如果为关系的每一行生成一个OID则为真
 relhasrules         | boolean      | not null |如果表有（或曾有）规则则为真
 relhastriggers      | boolean      | not null |如果表有（或曾有）触发器则为真
 relhassubclass      | boolean      | not null |如果表有（或曾有）任何继承子女则为真
 relrowsecurity      | boolean      | not null |如果表上启用了行级安全性则为真
 relforcerowsecurity | boolean      | not null |如果行级安全性（启用时）也适用于表拥有者则为真
 relispopulated      | boolean      | not null |如果表已被填充则为真（对于所有关系该列都为真
 relreplident        | "char"       | not null |用来为行形成“replica identity”的列： d = 默认(主键，如果存在), 
            n= 无, f = 所有列 i= 索引的indisreplident被设置或者为默认
 relispartition      | boolean      | not null |如果表或索引是一个分区，则为真
 relrewrite          | oid          | not null |对于在要求表重写的DDL操作期间被写入的新关系，这个域包含原始关系的OID，否则为0。
 relfrozenxid        | xid          | not null |在此之前的所有事务ID在表中已经被替换为一个永久的（“冻结的”) 事务ID。
 relminmxid          | xid          | not null |在此之前的多事务ID在表中已经被替换为一个事务ID。
 relacl              | aclitem[]    |          |访问权限
 reloptions          | text[]       |          |访问方法相关的选项，以“keyword=value”字符串形式
 relpartbound        | pg_node_tree |          |如果表示一个分区（见relispartition），分区边界的内部表达
索引：
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

2.pg_stat_all_indexes

pg_stat_all_indexes视图将为当前数据库中的每个索引包含一行，该行显示关于对该索引访问的统计信息。pg_stat_user_indexes和pg_stat_sys_indexes视图包含相同的信息，但是被过滤得只分别显示用户和系统索引。

postgres=# d+ pg_stat_all_indexes;
              视图 "pg_catalog.pg_stat_all_indexes"
     栏位      |  类型  | 存储  | 描述
---------------+--------+-------+------
 relid         | oid    | plain |这个索引的基表的 OID
 indexrelid    | oid    | plain |这个索引的 OID
 schemaname    | name   | plain |这个索引所在的模式的名称
 relname       | name   | plain |这个索引的基表的名称
 indexrelname  | name   | plain |这个索引的名称
 idx_scan      | bigint | plain |在这个索引上发起的索引扫描次数
 idx_tup_read  | bigint | plain |在这个索引上由扫描返回的索引项数量
 idx_tup_fetch | bigint | plain |被使用这个索引的简单索引扫描取得的活着的表行数量
视图定义:
 SELECT c.oid AS relid,
    i.oid AS indexrelid,
    n.nspname AS schemaname,
    c.relname,
    i.relname AS indexrelname,
    pg_stat_get_numscans(i.oid) AS idx_scan,
    pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
    pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
   FROM pg_class c
     JOIN pg_index x ON c.oid = x.indrelid
     JOIN pg_class i ON i.oid = x.indexrelid
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]);

3.pg_index

postgres=# d+ pg_index;
                              数据表 "pg_catalog.pg_index"
      栏位      |     类型     | 可空的     描述
----------------+--------------+-----------+------
 indexrelid     | oid          | not null  |此索引的pg_class项的OID
 indrelid       | oid          | not null  |此索引的基表的pg_class项的OID
 indnatts       | smallint     | not null  |索引中的总列数（与pg_class.relnatts重复），这个数目包括键和被包括的属性
 indnkeyatts    | smallint     | not null  |索引中键列的编号，不计入任何的内含列，它们只是被存储但不参与索引的语义
 indisunique    | boolean      | not null  |表示是否为唯一索引
 indisprimary   | boolean      | not null  |表示索引是否表示表的主键（如果此列为真，indisunique也总是为真）
 indisexclusion | boolean      | not null  |表示索引是否支持一个排他约束
 indimmediate   | boolean      | not null  |表示唯一性检查是否在插入时立即被执行（如果indisunique为假，此列无关）
 indisclustered | boolean      | not null  |如果为真，表示表最后以此索引进行了聚簇
 indisvalid     | boolean      | not null  |如果为真，此索引当前可以用于查询。为假表示此索引可能不完整
 indcheckxmin   | boolean      | not null  如果为真，直到此pg_index行的xmin低于查询的TransactionXmin视界之前，查询都不能使用此索引
 indisready     | boolean      | not null  |如果为真，表示此索引当前可以用于插入。
 indislive      | boolean      | not null  |如果为假，索引正处于被删除过程中，并且必须被所有处理忽略
 indisreplident | boolean      | not null  |如果为真，这个索引被选择为使用ALTERTABLE ... REPLICAIDENTITY USING INDEX ...的“replicaidentity”
 indkey         | int2vector   | not null  |这是一个indnatts值的数组，它表示了此索引索引的表列
 indcollation   | oidvector    | not null  |对于索引键（indnkeyatts值）中的每一列，这包含要用于该索引的排序规则的OID，
           如果该列不是一种可排序数据类型则为零。
 indclass       | oidvector    | not null  |对于索引键中的每一列 （indnkeyatts值），这里包含了要使用的操作符类的OID。
 indoption      | int2vector   | not null  |这是一个indnkeyatts值的数组，用于存储每列的标志位。
 indexprs       | pg_node_tree |           |这是一个indnkeyatts值的数组，用于存储每列的标志位。
 indpred        | pg_node_tree |           |部分索引谓词的表达式树（以nodeToString()形式）。
索引：
    "pg_index_indexrelid_index" UNIQUE, btree (indexrelid)
    "pg_index_indrelid_index" btree (indrelid)