索引膨脹的幾個(gè)來源：
1 大量刪除發(fā)生后，導(dǎo)致索引頁面稀疏，降低了索引使用效率。
2 PostgresQL 9.0之前的版本，vacuum full 會同樣導(dǎo)致索引頁面稀疏。
3 長時(shí)間運(yùn)行的事務(wù)，禁止vacuum對表的清理工作，因而導(dǎo)致頁面稀疏狀態(tài)一直保持。
查看重復(fù)索引
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;
表的大小和表中索引個(gè)數(shù)
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;
獲取每個(gè)表的行數(shù)，索引和一些關(guān)于這些索引的信息（比較詳細(xì)）
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;
補(bǔ)充：postgresql查看表膨脹
查看表膨脹（對所有表產(chǎn)進(jìn)行膨脹率排序）
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;
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL之INDEX 索引詳解
- PostgreSql 重建索引的操作
- PostgreSQL模糊匹配走索引的操作
- PostgreSQL的B-tree索引用法詳解
- postgresql通過索引優(yōu)化查詢速度操作
- postgresql 索引之 hash的使用詳解
