Vacuum统计信息查看

查看当前schema下所有表的vacuum历史

select 
n.nspname as schema_name,
c.relname as table_name,
c.reltuples as row_count,
c.relpages as page_count,
s.n_dead_tup as dead_row_count,
s.last_vacuum,
s.last_autovacuum,
s.last_analyze,s.last_autoanalyze
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_stat_user_tables s on s.relid = c.oid
where c.relkind = 'r' and n.nspname = 'public';

 

查看自上一次vacuum后被修改过的表

SELECT
	n.nspname AS schema_name,
	c.relname AS table_or_index_name,
	c.relkind AS table_or_index,
	c.reltuples AS row_count,
	s.last_vacuum,
	s.last_autovacuum,
	s.last_analyze,
	s.last_autoanalyze 
FROM
	pg_class c
	JOIN pg_namespace n ON n.oid = c.relnamespace
	LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid 
WHERE
	( c.relkind = 'r' OR c.relkind = 'i' ) 
	AND ( s.last_vacuum < s.last_autovacuum OR s.last_vacuum < s.last_analyze );

查看dead行比较多的表和索引

SELECT
n.nspname as schema_name,
c.relname as table_name,
c.reltuples as row_count,
s.n_dead_tup as dead_row_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r' AND s.n_dead_tup > 0;