-
-
Save sahglie/2404a22a86da68bc9f7a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT * FROM ( | |
SELECT | |
schemaname, tablename, | |
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE ((sml.relpages/otta::numeric) * 100 - 100) END) AS tbloat, | |
CASE WHEN relpages < otta THEN 0 ELSE round(bs*(sml.relpages-otta)::numeric / (1024^2)::numeric , 2 ) END AS wastedmb, | |
ROUND(AVG(CASE WHEN iotta >= ipages THEN 0 | |
WHEN iotta = 0 THEN 0 | |
ELSE (ipages/iotta::numeric) * 100 - 100 END), -1) AS idxbloat, | |
SUM(CASE WHEN ipages < iotta THEN 0 ELSE round( bs*(ipages-iotta)::numeric / (1024^2)::numeric, 2 ) END) AS wastedidxmb | |
FROM ( | |
SELECT | |
schemaname, tablename, cc.reltuples, cc.relpages, bs, | |
CEIL((cc.reltuples*((datahdr+ma- | |
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) * 1.2 AS otta, | |
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, | |
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) * 1.1 AS iotta | |
FROM ( | |
SELECT | |
ma,bs,schemaname,tablename, | |
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, | |
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 | |
FROM ( | |
SELECT | |
schemaname, tablename, hdr, ma, bs, | |
SUM((1-null_frac)*avg_width) AS datawidth, | |
MAX(null_frac) AS maxfracsum, | |
hdr+( | |
SELECT 1+count(*)/8 | |
FROM pg_stats s2 | |
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename | |
) AS nullhdr | |
FROM pg_stats s, ( | |
SELECT | |
(SELECT current_setting('block_size')::numeric) AS bs, 23 AS hdr, | |
4 AS ma | |
FROM (SELECT version() AS v) AS foo | |
) AS constants | |
GROUP BY 1,2,3,4,5 | |
) AS foo | |
) AS rs | |
JOIN pg_class cc ON cc.relname = rs.tablename | |
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname | |
AND nn.nspname NOT IN ( 'information_schema', 'pg_catalog' ) | |
LEFT JOIN pg_index i ON indrelid = cc.oid | |
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid | |
) AS sml | |
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 | |
GROUP BY 1,2,3,4 | |
) as bloat | |
WHERE ( wastedmb > 1 AND tbloat > 20 ) | |
OR ( idxbloat >= 30 and wastedidxmb > 1 ) | |
ORDER BY wastedmb DESC LIMIT 50; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment