GP - Report tables with missing statistics

List of tables with missing statistics

select 
a.table_schema as \"Schema\", a.table_nameas \"Table\" 
from ( 
select 
t.table_schema, 
coalesce(to_char(s.time,'MM/DD/YYYY'),'Never') as last_analyze, 
t.table_name 
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' 
) a 
where a.last_analyze = 'Never' 
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