/**
 * The view shows unused indexes with further information about the table.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_unused_indexes AS
SELECT schemaname AS schema_name
	, relname AS table_name
	, indexrelname AS index_name
	, idx_scan
	, pg_size_pretty (pg_table_size ('"' || schemaname || '"."' || relname || '"')) AS table_size
	, pg_size_pretty (pg_total_relation_size ('"' || schemaname || '"."' || relname || '"')) AS table_total_size
	, pg_size_pretty (pg_indexes_size ('"' || schemaname || '"."' || relname || '"')) AS all_indexes_size
	, pg_size_pretty (pg_relation_size (indexrelid)) AS index_size
	, pg_size_pretty (sum (pg_relation_size (indexrelid)) over ()) AS  size_of_all_indexes
FROM pg_stat_all_indexes
WHERE idx_scan = 0
AND schemaname NOT IN
	(
		'information_schema',
		'pg_catalog',
		'pg_toast'
	)
;

COMMENT ON VIEW pg_unused_indexes IS 'The view shows unused indexes with further information about the table.';
COMMENT ON COLUMN pg_unused_indexes.schema_name IS 'The name of the schema.';
COMMENT ON COLUMN pg_unused_indexes.table_name IS 'The name of the table.';
COMMENT ON COLUMN pg_unused_indexes.index_name IS 'The name of the index';
COMMENT ON COLUMN pg_unused_indexes.idx_scan IS 'The number of query executions where this index has been used, is always zero.';
COMMENT ON COLUMN pg_unused_indexes.table_size IS 'The size of the table without indexes.';
COMMENT ON COLUMN pg_unused_indexes.table_total_size IS 'The size of the table including all indexes.';
COMMENT ON COLUMN pg_unused_indexes.all_indexes_size IS 'The size of all indexes in this table.';
COMMENT ON COLUMN pg_unused_indexes.index_size IS 'The size of the index mentioned in index_name.';
COMMENT ON COLUMN pg_unused_indexes.size_of_all_indexes IS 'The size of all indexes over all tables.';