526互联

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

发布时间 2023-04-19 08:35:06作者: 酷酷-

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.6  访问信息

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

3  小结

还有很多很多的元信息哈，后续还会补充哈，有理解不对的地方欢迎指正哈。