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

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

2021-03-31 17:42奈何流年 PostgreSQL

这篇文章主要介绍了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;