pgsql使用积累系列_表空间占用分析过程

写在前面，这里只记录了部分。若想看完整版的可参考官网。

-- 查看pg活跃进程
select pid,query_start,wait_event_type,wait_event,query from pg_stat_activity where state='active'

-- 查看单表大小，根据官网信息可知pg_table_size 由表大小+TOAST大小+free space map（维护heap和（非hash索引）索引的映射关系）+visibility map（跟vacuum有关系，维护heap之间的关系，）可以通过上面提到的查找文档方法，根据关键字 free space map和visibility map到官网查到，为避免误导别人，也误导自己就不做过多的翻译了。

-- Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)

-- 用友好的方式查看表大小。也可以用pg_relation_size('relationName') relation可以包括index,table等
-- pg_total_relation_size() 查看表相关的所有relation占用空间
select pg_size_pretty(pg_table_size('crm_iope.crm_ex_order_detail'));

-- 查看表的实际存储位置及占用空间
SELECT oid,pg_relation_filepath(177130), relpages FROM pg_class WHERE relname = 'crm_ex_order_detail';

-- 查看用户表信息，根据表名和schema名找到relid
select * from pg_stat_user_tables where relname = 'crm_ex_order_detail' and schemaname='crm_iope'

-- 根据用户表id，查看对应用户表的Toast(pg存储优化技术,对于大数据效果更好)表oid
select utbl.relid,pc.relname,pc.relfilenode,pc.reltoastrelid from pg_stat_user_tables utbl, pg_class pc 
where utbl.schemaname='crm_iope' and utbl.relname='crm_ex_order_detail'
and utbl.relid = pc.oid ;

-- 查看toast表元数据信息,用户表pg_stat_user_tables查不到
select * from pg_stat_all_tables where relname = 'pg_toast_177130'

-- 查看toast数据信息
select * from pg_toast.pg_toast_177130

-- VACUUM Progress Reporting,优化执行计划进程报告,对于增删改操作,操作完成后,pg会默认开启执行计划优化方案,以提高查询性能。
select * from pg_stat_progress_vacuum;

-- 查询toast占用空间,结合具体的用户表才有意义
SELECT relname, relpages
FROM pg_class,
(SELECT reltoastrelid
FROM pg_class
WHERE relname = 'crm_ex_order_detail') AS ss
WHERE (oid = ss.reltoastrelid OR
oid = (SELECT indexrelid
FROM pg_index
WHERE indrelid = ss.reltoastrelid))
and oid = 242295
ORDER BY relname;

-- 查询索引暂用空间,结合表才有意义
SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'crm_ex_order_detail' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid and
c.oid = 177130
ORDER BY c2.relname;

-- 查看最近使用的表/或索引使用的情况
SELECT oid,relname, relpages
FROM pg_class
ORDER BY relpages DESC;