current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes |
---|---|---|---|---|---|---|---|
evergreen | action | hold_copy_map | 2.7 | 86810624 | copy_once_per_hold | 7.4 | 130359296 |
evergreen | action | hold_copy_map | 2.7 | 86810624 | hold_copy_map_pkey | 5.2 | 85024768 |
evergreen | action | hold_copy_map | 2.7 | 86810624 | acm_copy_idx | 8.5 | 152723456 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_pickup_lib_idx | 0.7 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_prev_check_time_idx | 0.7 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_requestor_idx | 0.4 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_pkey | 0.2 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_usr_idx | 0.3 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_open_idx | 1.9 | 2433024 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_current_copy_before_cap_idx | 8.6 | 14450688 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_target_idx | 0.4 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_current_copy_idx | 0.5 | 0 |
evergreen | action | hold_request | 1.0 | 5758976 | hold_request_fulfillment_staff_idx | 0.5 | 0 |
13 rows with 8 columns retrieved.
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
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)) 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) AS iotta -- very rough approximation, assumes all cols
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,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END 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 <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
WHERE schemaname = 'action'
AND (tablename = 'hold_copy_map' OR tablename = 'hold_request')
ORDER BY wastedbytes DESC