Vacuum统计信息查看

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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后被修改过的表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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行比较多的表和索引

?

1 2 3 4 5 6 7 8 9

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;