Index size/usage statistics

indexstatistics.sql

SELECT
	T .tablename,
	indexname,
	C .reltuples AS num_rows,
	pg_size_pretty (
		pg_relation_size (quote_ident(T .tablename) :: TEXT)
	) AS table_size,
	pg_size_pretty (
		pg_relation_size (
			quote_ident(indexrelname) :: TEXT
		)
	) AS index_size,
	CASE
WHEN indisunique THEN
	'Y'
ELSE
	'N'
END AS UNIQUE,
 idx_scan AS number_of_scans,
 idx_tup_read AS tuples_read,
 idx_tup_fetch AS tuples_fetched
FROM
	pg_tables T
LEFT OUTER JOIN pg_class C ON T .tablename = C .relname
LEFT OUTER JOIN (
	SELECT
		C .relname AS ctablename,
		ipg.relname AS indexname,
		x.indnatts AS number_of_columns,
		idx_scan,
		idx_tup_read,
		idx_tup_fetch,
		indexrelname,
		indisunique
	FROM
		pg_index x
	JOIN pg_class C ON C .oid = x.indrelid
	JOIN pg_class ipg ON ipg.oid = x.indexrelid
	JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid
) AS foo ON T .tablename = foo.ctablename
WHERE
	T .schemaname = 'public'
ORDER BY
	1,
	2;