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

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

这里分享下问题搜索过程分享。性能分析必然离不开系统函数。像pg这样成熟的产品假设这一套是完善的（不完善也胜过我们大多数人），对于分析的视图或者脚本如果我知道了一个，那么根据局部相关性原理，相似的功能很可能都在一起。思考就讲到这。

接着打开百度搜索postgresql docs。打开官网文档，根据已知的功能函数/表/视图去找未知的功能，如我知道pg_table_size，就可以按照下图搜索,当然我只是截出了搜索的位置，点search的结果并不是这个页面。这里只是想强调一点如何利用官网查文档，而不用在百度反复找同样的东西浪费时间，当然如果你什么都不知道，还是要通过百度或者其他搜索引擎找到一个入口的。

-- 查看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;

vacuumdb: 清理vacuum 产生的数据。在频繁insert、update、delete的情况下这个值会很大，很耗磁盘。
而且这个值是pg_table_size的一部分。对单表操作影响很大
如果安装了postgresql的话在 bin目录下可以查看有哪些功能（包括vacuumdb）,windows环境下可以通过cmd切换到bin目下,
然后执行vacuumdb -?或者--help查看用法,其他命令(如备份)的使用方法查询都是一样的。