|
WITH constants AS(
SELECT
current_setting('block_size')::NUMERIC AS bs,
23 AS hdr,
4 AS ma
),
bloat_info AS(
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,
constants
GROUP BY
1,
2,
3,
4,
5
) AS foo
),
table_bloat AS(
SELECT
schemaname,
tablename,
cc.relpages,
bs,
CEIL(
(
cc.reltuples*(
(
datahdr + ma -(
CASE
WHEN datahdr % ma = 0 THEN ma
ELSE datahdr % ma
END
)
)+ nullhdr2 + 4
)
)/(
bs - 20::FLOAT
)
) AS otta
FROM
bloat_info
JOIN pg_class cc ON
cc.relname = bloat_info.tablename
JOIN pg_namespace nn ON
cc.relnamespace = nn.oid
AND nn.nspname = bloat_info.schemaname
AND nn.nspname <> 'information_schema'
),
index_bloat AS(
SELECT
schemaname,
tablename,
bs,
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
) AS iotta -- very rough approximation, assumes all cols
FROM
bloat_info
JOIN pg_class cc ON
cc.relname = bloat_info.tablename
JOIN pg_namespace nn ON
cc.relnamespace = nn.oid
AND nn.nspname = bloat_info.schemaname
AND nn.nspname <> 'information_schema'
JOIN pg_index i ON
indrelid = cc.oid
JOIN pg_class c2 ON
c2.oid = i.indexrelid
) SELECT
TYPE,
schemaname,
object_name,
bloat,
pg_size_pretty(raw_waste) AS waste
FROM
(
SELECT
'table' AS TYPE,
schemaname,
tablename AS object_name,
round(
CASE
WHEN otta = 0 THEN 0.0
ELSE table_bloat.relpages / otta::NUMERIC
END,
1
) AS bloat,
CASE
WHEN relpages < otta THEN '0'
ELSE(
bs*(
table_bloat.relpages - otta
)::BIGINT
)::BIGINT
END AS raw_waste
FROM
table_bloat
UNION SELECT
'index' AS TYPE,
schemaname,
tablename || '::' || iname AS object_name,
round(
CASE
WHEN iotta = 0
OR ipages = 0 THEN 0.0
ELSE ipages / iotta::NUMERIC
END,
1
) AS bloat,
CASE
WHEN ipages < iotta THEN '0'
ELSE(
bs*(
ipages - iotta
)
)::BIGINT
END AS raw_waste
FROM
index_bloat
) bloat_summary
ORDER BY
raw_waste DESC,
bloat DESC;
|