postgresql查看表和索引的情况,判断是否膨胀的操作

索引膨胀的几个来源：

1 大量删除发生后，导致索引页面稀疏，降低了索引使用效率。

2 postgresql 9.0之前的版本，vacuum full 会同样导致索引页面稀疏。

3 长时间运行的事务，禁止vacuum对表的清理工作，因而导致页面稀疏状态一直保持。

查看重复索引

select pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
  (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
  (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
from (
 select indexrelid::regclass as idx, (indrelid::text ||e'\n'|| indclass::text ||e'\n'|| indkey::text ||e'\n'||
           coalesce(indexprs::text,'')||e'\n' || coalesce(indpred::text,'')) as key
 from pg_index) sub
group by key having count(*)>1
order by sum(pg_relation_size(idx)) desc;

表的大小和表中索引个数

select
 t.tablename,
 indexname,
 c.reltuples as num_rows,
 pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) as table_size,
 pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) as index_size,
 case when indisunique then 'y'
  else 'n'
 end as unique,
 idx_scan as number_of_scans,
 idx_tup_read as tuples_read,
 idx_tup_fetch as tuples_fetched
from pg_tables t
left outer join pg_class c on t.tablename=c.relname
left outer join
 ( select c.relname as ctablename, ipg.relname as indexname, x.indnatts as number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique from pg_index x
   join pg_class c on c.oid = x.indrelid
   join pg_class ipg on ipg.oid = x.indexrelid
   join pg_stat_all_indexes psai on x.indexrelid = psai.indexrelid )
 as foo
 on t.tablename = foo.ctablename
where t.schemaname='public'
order by 1,2;

获取每个表的行数，索引和一些关于这些索引的信息（比较详细）

select
 pg_class.relname,
 pg_size_pretty(pg_class.reltuples::bigint) as rows_in_bytes,
 pg_class.reltuples as num_rows,
 count(indexname) as number_of_indexes,
 case when x.is_unique = 1 then 'y'
  else 'n'
 end as unique,
 sum(case when number_of_columns = 1 then 1
    else 0
   end) as single_column,
 sum(case when number_of_columns is null then 0
    when number_of_columns = 1 then 0
    else 1
   end) as multi_column
from pg_namespace 
left outer join pg_class on pg_namespace.oid = pg_class.relnamespace
left outer join
  (select indrelid,
   max(cast(indisunique as integer)) as is_unique
  from pg_index
  group by indrelid) x
  on pg_class.oid = x.indrelid
left outer join
 ( select c.relname as ctablename, ipg.relname as indexname, x.indnatts as number_of_columns from pg_index x
   join pg_class c on c.oid = x.indrelid
   join pg_class ipg on ipg.oid = x.indexrelid )
 as foo
 on pg_class.relname = foo.ctablename
where 
  pg_namespace.nspname='public'
and pg_class.relkind = 'r'
group by pg_class.relname, pg_class.reltuples, x.is_unique
order by 2;

补充：postgresql查看表膨胀

查看表膨胀（对所有表产进行膨胀率排序）

sql文如下：

select
 schemaname||'.'||relname as table_name,
 pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
 n_dead_tup,
 n_live_tup,
 round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) as dead_tup_ratio
from
 pg_stat_all_tables
where
 n_dead_tup >= 1000
order by dead_tup_ratio desc
limit 10;

以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方，望不吝赐教。