Postgresql统计所有表的基本信息(如行数、大小等)

pg_class
目录 pg_class 记录表和几乎所有具有列或者像表的东西。这包括索引（但还要参见 pg_index ）、序列、视图、物化视图、组合类型和TOAST表。
pg_class 中的一些逻辑标志被以一种懒惰的方式维护：在正确状态时它们被保证为真，但是当条件不再为真时它们并不会被立刻重置为假。例如， relhasindex 由 CREATE INDEX 设置，但它从不会被DROP INDEX 清除。作为替代， VACUUM 会在找到无索引表后清除其 relhasindex 。这种安排避免了竞争条件并且提高了并发性

本文来自 PostgreSQL_HighGoDB 的CSDN 博客 ，全文地址请点击：https://blog.csdn.net/pg_hgdb/article/details/79455123?utm_source=copy

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;

• 1

因懒惰的方式维护，当有新的数据插入表中时，上述命令执行结果不会改变，需要刷新数据表，需先执行：

vacuum tablename     #更新某个表
vacuum               #在某个数据库中执行直接更新该数据库所有表

• 1
• 2

vacuum的效果：
1.1释放,再利用 更新/删除的行所占据的磁盘空间.
1.2更新POSTGRESQL查询计划中使用的统计数据
1.3防止因事务ID的重置而使非常老的数据丢失。

第一点的原因是PostgreSQL数据的插入,更新,删除操作并不是真正放到数据库空间.如果不定期释放空间的话,由于数据太多,查询速度会巨降.
第二点的原因是PostgreSQL在做查询处理的时候,为了是查询速度提高,会根据统计数据来确定执行计划.如果不及时更新的话,查询的效果可能不如预期.
第三点的原因是PostgreSQL中每一个事务都会产生一个事务ID,但这个数字是有上限的. 当事务ID达到最大值后,会重新从最小值开始循环.这样如果不及时把以前的数据释放掉的话,原来的老数据会因为事务ID的丢失而丢失掉。

vacuum原理及用法可参考：
https://www.cnblogs.com/pengai/p/8073218.html
http://blog.itpub.net/29989552/viewspace-2128914/
2、查询表名和表注释

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c where   relname ='table_name' ;

• 1

2、查询字段名、字段类型及字段长度和字段注释

select a.attnum,a.attname,
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description from pg_class c, pg_attribute a , pg_type t, pg_description d where  c.relname = 'table_name' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and  d.objoid=a.attrelid and d.objsubid=a.attnum

• 1
• 2

pg_attribute可见：
https://www.cnblogs.com/stephen-liu74/archive/2012/05/25/2305415.html
3、查询表的大小

select pg_size_pretty(pg_relation_size('table_name'));

• 1

4、查询所有表并按大小排序

SELECT 
table_schema || '.' || table_name 
AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM 
information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC limit 20

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

可参考 http://blog.51yip.com/pgsql/1525.html
5、查出所有表按大小排序并分离data与index

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17

6、统计各数据库占用的磁盘大小

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12