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
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
Post a Comment