[PostgreSQL] Vaccum 수행 필요 대상 추출

-- Vacuum threshold(autovacuum 임계값) = vacuum base threshold + vacuum scale factor * number of tuples 

with     avt as ( 
	select setting as autovacuum_vacuum_threshold 
	from pg_settings 
	where name = 'autovacuum_vacuum_threshold' 
)
       , vsf as ( 
	select setting as autovacuum_vacuum_scale_factor 
	from pg_settings 
	where name = 'autovacuum_vacuum_scale_factor' 
)
       , fma as ( 
	select setting as autovacuum_freeze_max_age
	from pg_settings
	where name = 'autovacuum_freeze_max_age' 
)
      , sto as ( 
	select opt_oid, 
	            split_part(setting, '=', 1) as param, 
		 split_part(setting, '=', 2) as value 
	from (
		select oid opt_oid, 
		       unnest(reloptions) setting
		from pg_class
		) opt 
) 
select '''' || ns.nspname || '.' ||c.relname|| '''' as relation, 
           pg_size_pretty(pg_table_size(c.oid)) as table_size, 
           age(relfrozenxid) as xid_age,
           coalesce(cfma.value::float, autovacuum_freeze_max_age::float) as autovacuum_freeze_max_age,
           (coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + 
           coalesce(cvsf.value::float, autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples, 
	    n_dead_tup as dead_tuples
from pg_class c 
join pg_namespace ns 
on   ns.oid = c.relnamespace 
join pg_stat_all_tables stat 
on   stat.relid = c.oid 
join avt 
on   (1=1)
join vsf  
on   (1=1) 
join fma 
on   (1=1) 
left join sto cavt
on   cavt.param = 'autovacuum_vacuum_threshold'
and  c.oid = cavt.opt_oid 
left join sto cvsf 
on   cvsf.param = 'autovacuum_vacuum_scale_factor'
and  c.oid = cvsf.opt_oid 
left join sto cfma 
on cfma.param = 'autovacuum_freeze_max_age'
and c.oid = cfma.opt_oid 
where c.relkind = 'r' and nspname <> 'pg_catalog' 
and ( 
	age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)
	or 
	coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + 
	coalesce(cvsf.value::float, autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup
    )       
order by age(relfrozenxid) desc ;