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