GP - report when analyze was last run

SQL to report when analyze was last run.

select 
t.table_schema as \"Schema\", 
coalesce(to_char(s.time,'MM/DD/YYYY'),'Never') as \"Last Analyze\", 
count(*) 
from information_schema.tables t 
left outer join ( 
select schemaname as schema_name, 
objname as table_name, 
statime as time 
from pg_stat_operations 
where actionname = 'ANALYZE' 
) s on s.schema_name = t.table_schema 
and s.table_name = t.table_name 
left outer join pg_catalog.pg_partitions p 
 on t.table_schema = partitionschemaname 
and t.table_name = partitiontablename 
inner join (select n.nspname, r.relname, r.relstorage from 
pg_catalog.pg_class r 
inner join pg_catalog.pg_namespace n 
on r.relnamespace = n.oid 
) c 
 on t.table_schema = c.nspname
and t.table_name = c.relname
where t.table_type = 'BASE TABLE'
and t.table_schema not in ('pg_catalog','gp_toolkit')
and p.partitiontablename is null
and c.relstorage != 'x'
group by 1,2 order by 1,2

Comments

Popular posts from this blog

How to set Optimizer at database level in greenplum

Changing the gpmon Password

IPMI Tool | IPMI Cheatsheet