List of tables not vacuumed in last 3 days

Post date: Oct 12, 2014 8:11:36 PM

SELECT

n.nspname, 

c.relname,

a.statime

FROM

pg_class c INNER JOIN pg_namespace n

ON (c.relnamespace = n.oid)

LEFT OUTER JOIN pg_partitions pgp

ON (

pgp.schemaname = n.nspname

AND pgp.tablename = c.relname

)

LEFT OUTER JOIN (

SELECT DISTINCT

objid,

statime

FROM

pg_stat_last_operation

WHERE

staactionname in ('VACUUM')

) a

ON (a.objid = c.oid)

WHERE

n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')

and c.relname not like 'stg%' and c.relname not like 'swap%'

AND relkind in ('r')

AND c.relstorage = 'h'

and date_trunc('day',statime) < date_trunc('day',localtimestamp- interval '3 day')

ORDER BY reltuples DESC;