postgresql 9.1 查看表和索引的大小

查看表及所属索引的大小

with tmp_table as (
    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
;