列出greenplum中public下所有表,并显示最近的DDL与DML操作,以及显示表所占用空间大小以及表行数
select
tables.*,
po.last_staactionname,
po.last_statime,
po1.first_staactionname,
po1.first_statime,
reltuples
from
(select
schemaname,SPLIT_PART(tablename, '_1', 1) as tablename,tableowner,tablespace,hasindexes,hasrules,hastriggers,
round(sum(pg_total_relation_size(schemaname||'.'||SPLIT_PART(tablename, '_1', 1)))/1024/1024,2) as tablesize_KB
from pg_tables where schemaname in ('public')
group by 1,2 ,3,4,5,6,7 order by tablesize_KB desc
)tables
left join
(select objid,last_staactionname ,last_statime from (select objid,staactionname as last_staactionname,statime as last_statime,
ROW_NUMBER()OVER(PARTITION by objid order by statime desc) as ind from pg_stat_last_operation where staactionname not in ('ANALYZE' ,'VACUUM','PRIVILEGE'))a where ind = 1) po
on tables.tablename::regclass = po.objid
left join
(select objid,first_staactionname ,first_statime from (select objid,staactionname as first_staactionname,statime as first_statime,
ROW_NUMBER()OVER(PARTITION by objid order by statime) as ind from pg_stat_last_operation where staactionname not in ('ANALYZE' ,'VACUUM','PRIVILEGE'))a where ind = 1) po1
on tables.tablename::regclass = po1.objid
left join
(SELECT SPLIT_PART(relname, '_1', 1) as relname, sum(reltuples) as reltuples
FROM pg_class r
JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'public'
GROUP BY 1
)nsp
on tables.tablename = nsp.relname
;