pg数据库统计表大小和行数

select 
	table_schema,
	table_name,
	pg_size_pretty(size) as tsize,
	rowcount
from (
	SELECT 
		table_schema, 
		table_name, 
		pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size,
		(select reltuples from pg_class  pc where pc.relname = t1.table_name ) as rowcount
	FROM information_schema.tables t1
) t 
order by size desc;

查询所有模式\表名称\字段信息, 排除分区表

SELECT 
    pd.schemaname as 模式名,
    pc.relname as 表名,
	pa.attname AS 列名,
	format_type ( pa.atttypid, pa.atttypmod ) AS 类型,
    (CASE WHEN pa.attlen > 0 THEN pa.attlen ELSE pa.atttypmod - 4 END ) AS 长度,	
	pa.attnotnull AS 可空, 
	(    
    	CASE WHEN ( SELECT COUNT(*) FROM pg_constraint WHERE conrelid = pa.attrelid AND conkey[1]= attnum AND contype = 'p' ) > 0 THEN
        TRUE ELSE FALSE 
        END 
    ) AS 主键,
	col_description ( pa.attrelid, pa.attnum ) AS 描述    
FROM
	pg_class AS pc,
	pg_attribute AS pa,
	pg_tables as pd
WHERE
	pd.tablename  = pc.relname
AND pa.attrelid = pc.oid
AND pa.attnum > 0
and pc.relname  not in 
(
	select c.relname
	from pg_class c
    join pg_inherits i on i.inhrelid = c. oid
    join pg_class d on d.oid = i.inhparent
)

查询所有分群表

	select c.relname
	from pg_class c
    join pg_inherits i on i.inhrelid = c. oid
    join pg_class d on d.oid = i.inhparent
    where d.relname = '分区表';