查看表及所属索引的大小
select pc.oid as taboid,
pc.*
from pg_class pc
where 1=1
and pc.relkind='r'
),tmp_index as (
select pin.indrelid as taboid,
pc.oid as idxoid,
pc.*
from pg_class pc,
pg_index pin -- select * from pg_index
--indexrelid oid pg_class.oid 此索引的pg_class项的OID
--indrelid oid pg_class.oid 此索引的基表的pg_class项的OID
where 1=1
and pc.relkind='i'
and pc.oid=pin.indexrelid
),tmp_database as (
select pdb.oid as datid,pdb.datname,
pts.oid as spcid,pts.spcname,pts.spclocation,
case when pdb.datname = current_database() then 0
else pts.oid
end as spcid_default
from pg_tablespace pts, -- select * from pg_tablespace
pg_database pdb -- select * from pg_database
where 1=1
and pdb.datname in ( select current_database() )
and pdb.dattablespace= pts.oid
)
select
tt.relowner as tab_ownerid,
(select usename from pg_user c where c.usesysid= tt.relowner) as tab_ownername,
tt.taboid as tab_id,
tt.relname as tab_name,
tt.relnamespace as tab_namespace_id,
(select pn.nspname from pg_namespace pn where pn.oid= tt.relnamespace) as tab_namespace_name,
tt.relkind as tab_kind,
tt.reltablespace as tab_tablesapce_id,
( case when tt.reltablespace = 0 then ( select '['||tdb.spcid||']'||'['|| tdb.spcname||']'||'['||tdb.spclocation||']'
from tmp_database tdb
where 1=1
and tdb.spcid_default = tt.reltablespace
)
else (select '['||pts.oid||']'||'['|| pts.spcname||']'||'['||pts.spclocation||']'
from pg_tablespace pts
where 1=1
and tt.reltablespace = pts.oid
)
end ) as tab_tablesapce_name,
round(pg_relation_size(tt.taboid)/1024/1024.0,2) as tab_size_mb,
tt.relpages as tab_pages,
tt.reltuples as tab_tuples,
tt.relhasindex as tab_hasindex,
'------' as flag0,
ti.relowner as idx_ownerid,
(select usename from pg_user c where c.usesysid= ti.relowner) as idx_ownername,
ti.idxoid as idx_id,
ti.relname as idx_name,
ti.relnamespace as idx_namespace,
(select pn.nspname from pg_namespace pn where pn.oid= ti.relnamespace) as idx_namespace_name,
ti.relkind as idx_kind,
ti.reltablespace as idx_tablesapce_id,
( case when ti.reltablespace = 0 then ( select '['||tdb.spcid||']'||'['|| tdb.spcname||']'||'['||tdb.spclocation||']'
from tmp_database tdb
where 1=1
and tdb.spcid_default = ti.reltablespace
)
else (select '['||pts.oid||']'||'['|| pts.spcname||']'||'['||pts.spclocation||']'
from pg_tablespace pts
where 1=1
and ti.reltablespace = pts.oid
)
end ) as idx_tablesapce_name,
round(pg_relation_size(ti.idxoid)/1024/1024.0,2) as idx_size_mb,
ti.relpages as idx_pages,
ti.reltuples as idx_tuples,
ti.relhasindex as idx_hasindex
from tmp_table tt
left outer join tmp_index ti
on tt.taboid=ti.taboid
where 1=1
order by tt.relpages desc,ti.relpages desc
;