postgresql查看表和索引的情况,判断是否膨胀的操作
程序员文章站
2023-12-31 08:17:52
索引膨胀的几个来源:1 大量删除发生后,导致索引页面稀疏,降低了索引使用效率。2 postgresql 9.0之前的版本,vacuum full 会同样导致索引页面稀疏。3 长时间运行的事务,禁止va...
索引膨胀的几个来源:
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;
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。