Query to check Bloated Index

 

/*Query to check Bloated Index*/

———————————————————————————————————–

WITH btree_index_atts AS (

SELECT nspname, relname, reltuples, relpages, indrelid, relam,

regexp_split_to_table(indkey::text, ‘ ‘)::smallint AS attnum,

indexrelid as index_oid

FROM pg_index

JOIN pg_class ON pg_class.oid=pg_index.indexrelid

JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace

JOIN pg_am ON pg_class.relam = pg_am.oid

WHERE pg_am.amname = ‘btree’

— AND pg_namespace.nspname IN (”)

AND pg_class.relname NOT LIKE ‘pk_%’

),

index_item_sizes AS (

SELECT

i.nspname, i.relname, i.reltuples, i.relpages, i.relam,

s.starelid, a.attrelid AS table_oid, index_oid,

current_setting(‘block_size’)::numeric AS bs,

/* MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) / CASE WHEN version() ~ ‘mingw32′ OR version() ~ ’64-bit’ THEN 8 ELSE 4 END AS maxalign, 24 AS pagehdr, / per tuple header: add index_attribute_bm if some cols are null-able / CASE WHEN max(coalesce(s.stanullfrac,0)) = 0 THEN 2 ELSE 6 END AS index_tuple_hdr, / data len: we remove null values save space using it fractionnal part from stats */

sum( (1-coalesce(s.stanullfrac, 0)) * coalesce(s.stawidth, 2048) ) AS nulldatawidth

FROM pg_attribute AS a

JOIN pg_statistic AS s ON s.starelid=a.attrelid AND s.staattnum = a.attnum

JOIN btree_index_atts AS i ON i.indrelid = a.attrelid AND a.attnum = i.attnum

WHERE a.attnum > 0

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

),

index_aligned AS (

SELECT maxalign, bs, nspname, relname AS index_name, reltuples,

relpages, relam, table_oid, index_oid,

( 2 +

maxalign – CASE /* Add padding to the index tuple header to align on MAXALIGN / WHEN index_tuple_hdr%maxalign = 0 THEN maxalign ELSE index_tuple_hdr%maxalign END + nulldatawidth + maxalign – CASE / Add padding to the data to align on MAXALIGN / WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr FROM index_item_sizes AS s1 ), otta_calc AS ( SELECT bs, nspname, table_oid, index_oid, index_name, relpages, coalesce( ceil((reltuples(4+nulldatahdrwidth))/(bs-pagehdr::float)) +

CASE WHEN am.amname IN (‘hash’,’btree’) THEN 1 ELSE 0 END , 0 — btree and hash have a metadata reserved block

) AS otta

FROM index_aligned AS s2

LEFT JOIN pg_am am ON s2.relam = am.oid

),

raw_bloat AS (

SELECT current_database() as dbname, nspname, c.relname AS table_name, index_name,

bs(sub.relpages)::bigint AS totalbytes, CASE WHEN sub.relpages <= otta THEN 0 ELSE bs(sub.relpages-otta)::bigint END

AS wastedbytes,

CASE

WHEN sub.relpages <= otta THEN 0 ELSE bs*(sub.relpages-otta)::bigint * 100 / (bs*(sub.relpages)::bigint) END AS realbloat, pg_relation_size(sub.table_oid) as table_bytes, stat.idx_scan as index_scans FROM otta_calc AS sub JOIN pg_class AS c ON c.oid=sub.table_oid JOIN pg_stat_user_indexes AS stat ON sub.index_oid = stat.indexrelid ) SELECT dbname as database_name, nspname as schema_name, table_name, index_name, round(realbloat, 1) as bloat_pct, wastedbytes as bloat_bytes, replace(pg_size_pretty(wastedbytes::bigint),’ ‘,”) as bloat_size, totalbytes as index_bytes, replace(pg_size_pretty(totalbytes::bigint),’ ‘,”) as index_size, table_bytes, replace(pg_size_pretty(table_bytes),’ ‘,”) as table_size, index_scans FROM raw_bloat WHERE realbloat > 40

— AND wastedbytes > 50000000

ORDER BY wastedbytes DESC;

/*Query to Check Bloated Table */

———————————————————————————————————–

WITH table_bloat AS (

SELECT current_database(),

schemaname,

tblid,

tblname,

bstblpages AS real_size, fillfactor, (tblpages-est_tblpages_ff)bs AS bloat_size,

CASE

WHEN tblpages – est_tblpages_ff > 0

THEN 100 * (tblpages – est_tblpages_ff)/tblpages::float

ELSE 0

END AS bloat_ratio,

is_na

— , (pst).free_percent + (pst).dead_tuple_percent AS real_frag

FROM (

SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,

ceil( reltuples / ( (bs-page_hdr)fillfactor/(tpl_size100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,

tblpages,

fillfactor,

bs,

tblid,

schemaname,

tblname,

heappages,

toastpages,

is_na

— , stattuple.pgstattuple(tblid) AS pst

FROM (

SELECT

( 4 + tpl_hdr_size + tpl_data_size + (2ma) – CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END – CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END ) AS tpl_size, bs – page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring(array_to_string(tbl.reloptions, ‘ ‘) FROM ‘%fillfactor=#”__#”%’ FOR ‘#’)::smallint, 100) AS fillfactor, current_setting(‘block_size’)::numeric AS bs, CASE WHEN version()~’mingw32′ OR version()~’64-bit|x86_64|ppc64|ia64|amd64′ THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count() ) / 8 ELSE 0::int END + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,

sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,

bool_or(att.atttypid = ‘pg_catalog.name’::regtype) AS is_na

FROM pg_attribute AS att

JOIN pg_class AS tbl ON att.attrelid = tbl.oid

JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace

JOIN pg_stats AS s ON s.schemaname=ns.nspname

AND s.tablename = tbl.relname

AND s.inherited=false AND s.attname=att.attname

LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid

WHERE att.attnum > 0 AND NOT att.attisdropped

— enable below filter for schema specific bloat detection

— AND ns.nspname = ‘${schema}’

AND tbl.relkind = ‘r’

GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids

ORDER BY 2,3

) AS s

) AS s2

) AS s3

ORDER BY bloat_size DESC

)

SELECT schemaname,

tblid,

tblname,

REPLACE(pg_size_pretty(real_size::numeric), ‘ ‘,”) as real_size,

fillfactor,

REPLACE(pg_size_pretty(bloat_size::numeric),’ ‘,”) as bloat_size,

round(bloat_ratio::numeric,2) as bloat_ratio,

is_na

FROM table_bloat

— modify the ratio based on your use case

WHERE bloat_ratio > 40

–WHERE tblname=”

ORDER BY bloat_ratio DESC