/**
 * The view shows bloat in tables and indexes.
 */
CREATE OR REPLACE VIEW pg_bloat_info AS
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 AS 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
		INNER JOIN pg_class cc
			ON cc.relname = bloat_info.tablename
		INNER JOIN pg_namespace nn
			ON cc.relnamespace = nn.oid
			AND nn.nspname = bloat_info.schemaname
			AND nn.nspname NOT IN
				(
					'information_schema',
					'pg_catalog'
				)
	)
, 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
		INNER JOIN pg_class AS cc
			ON cc.relname = bloat_info.tablename
		INNER JOIN pg_namespace AS nn
			ON cc.relnamespace = nn.oid
			AND nn.nspname = bloat_info.schemaname
			AND nn.nspname NOT IN
				(
					'information_schema',
					'pg_catalog'
				)
		INNER JOIN pg_index AS i
			ON indrelid = cc.oid
		INNER JOIN pg_class AS 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
	) AS bloat_summary
ORDER BY raw_waste DESC
	, bloat DESC
;
COMMENT ON VIEW pg_bloat_info IS 'The view shows bloat in tables and indexes.';