List of tables not analyzed in last N days

posted Apr 28, 2017, 11:59 AM by Sachchida Ojha
SELECT
n.nspname, 
c.relname,
a.statime,
'analyze '||n.nspname||'.'||c.relname||';'
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 ('ANALYZE')
) 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;
Comments