PostgreSQL数据库统计信息

查看数据库及数据库的字符集。

SQL> select datname,pg_encoding_to_char(encoding) from  pg_database;

统计数据库当前库中用户创建的对象及个数。

SQL> select 'database' as type,datname as db,count(*) from pg_catalog.pg_database group by datname
union  all
select 'schemas' as type,catalog_name as db,count(*) from information_schema.schemata where schema_name not in ('pg_catalog','pg_toast','information_schema') group by catalog_name
union  all
select 'tables' as type,table_catalog as db,count(*) from information_schema.tables where table_type='BASE TABLE' and table_schema not in ('pg_catalog','pg_toast','information_schema') group by table_catalog 
union  all
select 'views' as type,table_catalog as db,count(*) from information_schema.views where table_schema not in ('pg_catalog','pg_toast','information_schema') group by table_catalog
union  all
select 'sequences' as type,sequence_catalog as db,count(*) from information_schema.sequences group by sequence_catalog
union  all
select 'procedures' as type,specific_catalog as db,count(*) from information_schema.routines where  routine_type='PROCEDURE' and specific_schema not in ('pg_catalog','pg_toast','information_schema') group by specific_catalog
union  all
select 'functions' as type,specific_catalog as db,count(*) from information_schema.routines where routine_type='FUNCTION' and specific_schema not in ('pg_catalog','pg_toast','information_schema') group by specific_catalog
union  all
select 'triggers' as type,trigger_catalog as db,count(*) from information_schema.triggers group by trigger_catalog
union all 
select 'indexes' as type,s.catalog_name as db,count(*) from pg_catalog.pg_indexes pi left join information_schema.schemata s on pi.schemaname=s.schema_name where schemaname not in ('pg_catalog','pg_toast','information_schema') group by s.catalog_name 
union all
select 'partition table' as type,current_database() as db,count(*) from (select distinct inhparent from pg_inherits) tb

统计表数据量（包括分区表中各分区的表数据量，父表数据量需要求和其对应分区表的数据量总和，可以使用select count(* ) from 分区表父表查询出分区表的总数据量）。
```
SQL> select schemaname,relname, n_live_tup  from pg_catalog.pg_stat_user_tables psst order by relname;
```

查看所有用户下的表对象使用空间大小。

SQL> select schemaname,relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables  order by relname,pg_relation_size(relid) desc;

查看数据库表对象使用的列名称（关键字排查）。

SQL> select distinct column_name from information_schema.columns where table_schema not in ('pg_catalog','information','pg_toast') order by column_name;

查看数据库表对象使用的数据类型，示例为public模式。

SQL> select  data_type,count(*) from information_schema.columns where table_schema='public' group by data_type order by count(*) desc;

列出所有的非系统数据所有表的主键信息和外键信息。

SQL> select 
				kcu.constraint_schema  as 约束拥有者,
				pc.conname as 约束名称,
				kcu.table_schema as 表拥有者,
				kcu.table_name as 表名, 
				kcu.column_name as 列名,
				case when kcu.table_name=ccu.table_name then null else ccu.table_schema end as 外键表拥有者,
				case when kcu.table_name=ccu.table_name then null else ccu.table_name end as 外键表名,
				case when kcu.table_name=ccu.table_name then null else  ccu.column_name end as 外键表列名,
				case pc.confupdtype
					when 'a' then '无动作' 
					when 'r' then '限制' 
					when 'c' then '级联' 
					when 'n' then '设置为空' 
					when 'd' then '设置为缺省' 
					else null end as 约束更新规则,
				case pc.confdeltype
					when 'a' then '无动作' 
					when 'r' then '限制' 
					when 'c' then '级联' 
					when 'n' then '设置为空' 
					when 'd' then '设置为缺省' 
					else null end as 约束删除规则,
				pc.contype as 约束类型,
				pc.convalidated as 约束状态
			from pg_catalog.pg_constraint pc 
				inner join information_schema.key_column_usage kcu on kcu.constraint_name=pc.conname
			inner join information_schema.constraint_column_usage ccu on ccu.constraint_name=pc.conname
			where kcu.constraint_schema not in ('pg_catalog','information_schema','pg_toast');

PostgreSQL数据库统计信息 ​

PostgreSQL数据库统计信息