/**
 * Creates a view to get information about bloat in indexes.
 * The view requires the extension pgstattuple to be installed.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	pg_extension_installed BOOLEAN;
	view_source TEXT;
BEGIN

	SELECT count(*) > 0 AS pgstattuple_exists
	FROM pg_extension
	WHERE extname = 'pgstattuple'
	INTO pg_extension_installed
	;

	IF pg_extension_installed THEN
		-- The view is only created when pgstattuple is installed
		view_source :=
$string$
CREATE OR REPLACE VIEW pg_index_bloat AS
WITH index_list AS
	(
		SELECT i.schemaname AS schema_name
			, i.tablename AS table_name
			, i.indexname AS index_name
			, i.schemaname || '.' || i.indexname AS schema_index_name
		FROM pg_catalog.pg_indexes AS i
		LEFT OUTER JOIN pg_catalog.pg_partitioned_table AS pt
			ON (i.schemaname || '.' || i.tablename)::regclass::oid = pt.partrelid
		WHERE i.schemaname NOT IN
			(
				'pg_catalog',
				'information_schema'
			)
		AND pt.partrelid IS NULL
	)
SELECT index_list.schema_name
	, index_list.table_name
	, index_list.index_name
	, st.table_len AS table_length_in_bytes
	, st.tuple_count
	, st.tuple_len AS tuple_length_in_bytes
	, st.tuple_percent
	, st.dead_tuple_count
	, st.dead_tuple_len AS dead_tuple_length_in_bytes
	, st.dead_tuple_percent
	, st.free_space
	, st.free_percent
FROM index_list
CROSS JOIN LATERAL pgstattuple(index_list.schema_index_name) AS st
ORDER BY schema_name
	, index_name
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW pg_index_bloat IS 'The list of indexes and current bloat.';
		COMMENT ON COLUMN pg_index_bloat.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_index_bloat.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_index_bloat.index_name IS 'The name of the index.';
		COMMENT ON COLUMN pg_index_bloat.table_length_in_bytes IS 'The physical table size in bytes.';
		COMMENT ON COLUMN pg_index_bloat.tuple_count IS 'The number of live tuples.';
		COMMENT ON COLUMN pg_index_bloat.tuple_length_in_bytes IS 'The total length of live tuples in bytes.';
		COMMENT ON COLUMN pg_index_bloat.tuple_percent IS 'The percentage of live tuples compared to dead tuples.';
		COMMENT ON COLUMN pg_index_bloat.dead_tuple_count IS 'The number of dead tuples.';
		COMMENT ON COLUMN pg_index_bloat.dead_tuple_length_in_bytes IS 'The total length of dead tuples in bytes.';
		COMMENT ON COLUMN pg_index_bloat.dead_tuple_percent IS 'The percentage of dead tuples compared to live tuples.';
		COMMENT ON COLUMN pg_index_bloat.free_space IS 'The total free space in bytes of this table.';
		COMMENT ON COLUMN pg_index_bloat.free_percent IS 'The percentage of free space of this tuple compared to used space.';

	END IF;

END $$;