WITH btree_index_atts AS (
SELECT
nspname,
indexclass.relname AS index_name,
indexclass.reltuples,
indexclass.relpages,
indrelid,
indexrelid,
indexclass.relam,
tableclass.relname AS tablename,
regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
indexrelid AS index_oid
FROM pg_index
JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
JOIN pg_am ON indexclass.relam = pg_am.oid
WHERE pg_am.amname = 'btree'
AND indexclass.relpages > 0
AND nspname NOT IN ('pg_catalog','information_schema')
),
index_item_sizes AS (
SELECT
ind_atts.nspname,
ind_atts.index_name,
ind_atts.reltuples,
ind_atts.relpages,
ind_atts.relam,
indrelid AS table_oid,
index_oid,
current_setting('block_size')::numeric AS bs,
8 AS maxalign,
24 AS pagehdr,
CASE
WHEN max(coalesce(pg_stats.null_frac,0)) = 0 THEN 2
ELSE 6
END AS index_tuple_hdr,
SUM((1 - coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024)) AS nulldatawidth
FROM pg_attribute
JOIN btree_index_atts AS ind_atts
ON pg_attribute.attrelid = ind_atts.indexrelid
AND pg_attribute.attnum = ind_atts.attnum
JOIN pg_stats
ON pg_stats.schemaname = ind_atts.nspname
AND (
(pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
OR
(pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname)
)
WHERE pg_attribute.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned_est AS (
SELECT
maxalign, bs, nspname, index_name, reltuples,
relpages, relam, table_oid, index_oid,
COALESCE (
CEIL (
reltuples * (
6 + maxalign
- CASE
WHEN index_tuple_hdr % maxalign = 0 THEN maxalign
ELSE index_tuple_hdr % maxalign
END
+ nulldatawidth
+ maxalign
- CASE
WHEN nulldatawidth::integer % maxalign = 0 THEN maxalign
ELSE nulldatawidth::integer % maxalign
END
)::numeric / (bs - pagehdr::NUMERIC) + 1
),
0
) AS expected
FROM index_item_sizes
),
raw_bloat AS (
SELECT
current_database() AS dbname,
nspname,
pg_class.relname AS table_name,
index_name,
bs * (index_aligned_est.relpages)::bigint AS totalbytes,
expected,
CASE
WHEN index_aligned_est.relpages <= expected THEN 0
ELSE bs * (index_aligned_est.relpages - expected)::bigint
END AS wastedbytes,
CASE
WHEN index_aligned_est.relpages <= expected THEN 0
ELSE bs * (index_aligned_est.relpages - expected)::bigint * 100
/ (bs * (index_aligned_est.relpages)::bigint)
END AS realbloat,
pg_relation_size(index_aligned_est.table_oid) AS table_bytes,
stat.idx_scan AS index_scans
FROM index_aligned_est
JOIN pg_class ON pg_class.oid = index_aligned_est.table_oid
JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
SELECT
dbname AS database_name,
nspname AS schema_name,
table_name,
index_name,
ROUND(realbloat) AS bloat_pct,
ROUND(wastedbytes / (1024^3)::NUMERIC, 3) AS bloat_gb,
ROUND(totalbytes / (1024^3)::NUMERIC, 3) AS index_gb,
ROUND(table_bytes / (1024^3)::NUMERIC, 3) AS table_gb,
index_scans
FROM raw_bloat
)
-- Final simplified selection
SELECT
table_name,
index_name,
bloat_pct,
bloat_gb,
index_gb
FROM format_bloat
WHERE bloat_pct > 30
AND bloat_gb > 0.01
ORDER BY bloat_gb DESC
LIMIT 20;
No comments:
Post a Comment