【PostageSQL】关于表的元数据信息比如行数、表空间大小、表索引访问次数、最近分析时间等信息收集

1 前言

最近看阿里云的RDS数据库的数据分析，发现人家统计信息很快，于是很好奇人家怎么收集的，其实就是调的PGSQL的语句来收集的，我们这节就来看看这些数据怎么来的哈。如下图RDS的收集信息：

2 数据收集

函数表

函数名	返回类型	描述
`pg_size_pretty(bigint\|numeric)`	text	转换为易读单位
`pg_database_size(oid\|name)`	bigint	根据OID或名称, 查询数据库所占的磁盘空间
`pg_table_size(regclass)`	bigint	根据表OID或表名查询表所占的磁盘空间，不包含索引（但是包含TOAST，自由空间映射和可视映射）
`pg_indexes_size(regclass)`	bigint	根据表OID或表名, 查询所有索引所占磁盘空间
`pg_relation_size(relation regclass, fork text)`	bigint	根据OID或名的表或索引，以及fork指定分叉树('main', 'fsm' 或'vm')查询所占的磁盘空间
`pg_relation_size(relation regclass)`	bigint	`pg_relation_size(..., 'main')`的缩写
`pg_total_relation_size(regclass)`	bigint	根据表OID或表名, 查询所占的总磁盘空间，包括所有索引和TOAST数据
`pg_tablespace_size(oid\|name)`	bigint	根据OID或名称, 查询表空间所占的磁盘空间
`pg_column_size(any)`	int	存储某个数据需要的字节数（可能压缩过）
`pg_partition_size(oid\|text, oid\|text)`	bigint	查询表分区所占的磁盘空间。其中，第一个参数为表名\|oid，第二个参数为分区名\|oid。
`pg_partition_indexes_size(oid\|text, oidtext)`	bigint	指定名称的分区的索引使用的磁盘空间。其中，第一个参数为表名\|oid，第二个参数为分区名\|oid。

2.1 查询数据库信息

SELECT
    * 
FROM
    pg_database 
ORDER BY
    datname;

2.2 查询表信息

SELECT
    schemaname,
    tablename 
FROM
    pg_tables 
WHERE
    schemaname = 'public'

2.3 查询列信息

SELECT
    table_schema,
    "table_name",
    "column_name",
    data_type,
    column_default,
    is_nullable 
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
    AND "table_name" = 'xxx'

2.4 表行数

SELECT
    relname,
    reltuples::INT
FROM
    pg_class r
    JOIN pg_namespace n ON ( relnamespace = n.oid ) 
WHERE
    relkind = 'r' 
    AND n.nspname = 'public';

2.5 空间大小

2.5.1 数据库空间大小

SELECT
    pg_database.datname,
    pg_size_pretty ( pg_database_size ( datname ) ) AS SIZE 
FROM
    pg_database;

2.5.2 表空间大小

SELECT
    relname,
    pg_size_pretty ( pg_total_relation_size ( relname :: TEXT ) ) AS SIZE 
FROM
    pg_stat_user_tables 
WHERE
    schemaname = 'public';

2.5.3 索引空间大小

SELECT
    indexrelname,
    pg_size_pretty ( pg_relation_size ( relid ) ) AS SIZE 
FROM
    pg_stat_user_indexes 
WHERE
    schemaname = 'public' and relname = '某个表';

2.5.4 列空间大小

--计算某个表的body列数据大小
SELECT
    pg_size_pretty ( pg_column_size ( 表名.body ) :: NUMERIC ) 
FROM
    表名
    LIMIT 100;
--计算某个表行数据大小
SELECT
    pg_size_pretty ( pg_column_size ( 表名.*) :: NUMERIC ) 
FROM
    表名
    LIMIT 100;

2.6 访问信息

SELECT
    idx_scan 索引访问次数,
    seq_scan 全表扫描次数,
    last_vacuum 最近回收时间,
    last_analyze 最近分析时间 
FROM
    pg_stat_user_tables 
WHERE
    schemaname = 'public';