mysql查询表中的索引字段长度_查看Postgres数据库、表、索引、字段的大小

简介

在postgres里很多时候想要查看数据库、表、字段的大小，做分析。

示例

查看一个pg cluster里所有数据库的大小

SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.oid)) size

FROM pg_catalog.pg_database

WHERE datistemplate=false

ORDER BY pg_database_size(pg_database.oid) DESC;

查看连接的数据里所有用户表的大小

SELECT

relname AS "Table",

pg_size_pretty(pg_total_relation_size(relid)) as "Size",

pg_size_pretty(pg_total_relation_size(relid)-pg_relation_size(relid)) as "External Size"

FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Size表示整个表的总大小。

External Size表示与表关联的主键、索引的大小。

查看每个表、索引、toast表的大小

SELECT

relname AS objectname,

relkind AS objecttype,

reltuples AS "#entries",

pg_size_pretty(relpages::bigint*8*1024) AS size

FROM pg_catalog.pg_class

ORDER BY relpages DESC;

objecttype:

r 表示普通表

i 表示索引

t 表示toast(The Oversized-Attribute Storage Technique)行外存储表

只查看用户表相关的表、索引、toast表大小

WITH user_schema AS (

SELECT relnamespace FROM pg_catalog.pg_statio_user_tables urtb join pg_catalog.pg_class cls on urtb.relid=cls.oid group by relnamespace

)

SELECT

relname AS objectname,

relkind AS objecttype,

reltuples AS "#entries",

pg_size_pretty(relpages::bigint*8*1024) AS size

FROM pg_catalog.pg_class JOIN user_schema ON pg_class.relnamespace=user_schema.relnamespace

ORDER BY relpages DESC;

查询某表某列所有项的大小

SELECT pg_size_pretty(pg_column_size("Values")::bigint) valcolsize,"Id","Values" FROM mdm.concept ORDER BY pg_column_size("Values") DESC;

查询按外键聚合的字段的总大小

WITH concept_group AS(

SELECT "CodeSystemId",count(*) count,sum(pg_column_size("Values")) sumsize FROM mdm.concept GROUP BY "CodeSystemId" ORDER BY sumsize DESC

) SELECT "CodeSystemId",count "entries", pg_size_pretty(sumsize) FROM concept_group;

json、jsonb类型字段的大小

postgre中text、json、jsonb等类型的字段会在toast表里存储，故通过pg_column_size表查询出来的列数据是经过toast表序列化、压缩之后的大小，这个大小和它们的字符串表示(包括dump文件)会有较大出入，故建议查询大字段的字符串表示时大小，使用octet_length(col)：

SELECT pg_size_pretty(pg_column_size("Content")::bigint) columnSize,pg_size_pretty(octet_length("Content"::text)::bigint) octSize,* FROM mdm."ESB_mock" ORDER BY octet_length("Content"::text) DESC;

参考资料