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

2023-07-06 15:21:03 73

索引膨胀的几个来源：

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

2PostgresQL9.0之前的版本，vacuumfull会同样导致索引页面稀疏。

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

查看重复索引

SELECTpg_size_pretty(SUM(pg_relation_size(idx))::BIGINT)ASSIZE,
(array_agg(idx))[1]ASidx1,(array_agg(idx))[2]ASidx2,
(array_agg(idx))[3]ASidx3,(array_agg(idx))[4]ASidx4
FROM(
SELECTindexrelid::regclassASidx,(indrelid::text||E'\n'||indclass::text||E'\n'||indkey::text||E'\n'||
COALESCE(indexprs::text,'')||E'\n'||COALESCE(indpred::text,''))ASKEY
FROMpg_index)sub
GROUPBYKEYHAVINGCOUNT(*)>1
ORDERBYSUM(pg_relation_size(idx))DESC;

表的大小和表中索引个数

SELECT
t.tablename,
indexname,
c.reltuplesASnum_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text))AStable_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text))ASindex_size,
CASEWHENindisuniqueTHEN'Y'
ELSE'N'
ENDASUNIQUE,
idx_scanASnumber_of_scans,
idx_tup_readAStuples_read,
idx_tup_fetchAStuples_fetched
FROMpg_tablest
LEFTOUTERJOINpg_classcONt.tablename=c.relname
LEFTOUTERJOIN
(SELECTc.relnameASctablename,ipg.relnameASindexname,x.indnattsASnumber_of_columns,idx_scan,idx_tup_read,idx_tup_fetch,indexrelname,indisuniqueFROMpg_indexx
JOINpg_classcONc.oid=x.indrelid
JOINpg_classipgONipg.oid=x.indexrelid
JOINpg_stat_all_indexespsaiONx.indexrelid=psai.indexrelid)
ASfoo
ONt.tablename=foo.ctablename
WHEREt.schemaname='public'
ORDERBY1,2;

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

SELECT
pg_class.relname,
pg_size_pretty(pg_class.reltuples::BIGINT)ASrows_in_bytes,
pg_class.reltuplesASnum_rows,
COUNT(indexname)ASnumber_of_indexes,
CASEWHENx.is_unique=1THEN'Y'
ELSE'N'
ENDASUNIQUE,
SUM(CASEWHENnumber_of_columns=1THEN1
ELSE0
END)ASsingle_column,
SUM(CASEWHENnumber_of_columnsISNULLTHEN0
WHENnumber_of_columns=1THEN0
ELSE1
END)ASmulti_column
FROMpg_namespace
LEFTOUTERJOINpg_classONpg_namespace.oid=pg_class.relnamespace
LEFTOUTERJOIN
(SELECTindrelid,
MAX(CAST(indisuniqueASINTEGER))ASis_unique
FROMpg_index
GROUPBYindrelid)x
ONpg_class.oid=x.indrelid
LEFTOUTERJOIN
(SELECTc.relnameASctablename,ipg.relnameASindexname,x.indnattsASnumber_of_columnsFROMpg_indexx
JOINpg_classcONc.oid=x.indrelid
JOINpg_classipgONipg.oid=x.indexrelid)
ASfoo
ONpg_class.relname=foo.ctablename
WHERE
pg_namespace.nspname='public'
ANDpg_class.relkind='r'
GROUPBYpg_class.relname,pg_class.reltuples,x.is_unique
ORDERBY2;

补充：postgresql查看表膨胀

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

SQL文如下：

SELECT
schemaname||'.'||relnameastable_name,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname))astable_size,
n_dead_tup,
n_live_tup,
round(n_dead_tup*100/(n_live_tup+n_dead_tup),2)ASdead_tup_ratio
FROM
pg_stat_all_tables
WHERE
n_dead_tup>=1000
ORDERBYdead_tup_ratioDESC
LIMIT10;

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