View pg_unused_indexes

Creates a view to get the information about indexes, that have not been used by now.

Columns

Column Name	datatype	Description
schema_name	name	The name of the schema.
table_name	name	The name of the table.
index_name	name	The name of the index
idx_scan	bigint	The number of query executions where this index has been used, is always zero.
table_size	text	The size of the table without indexes.
table_total_size	text	The size of the table including all indexes.
all_indexes_size	text	The size of all indexes in this table.
index_size	text	The size of the index mentioned in index_name.
size_of_all_indexes	text	The size of all indexes over all tables.

Example

SELECT *
FROM pg_unused_indexes;

Result:

schema_name	table_name	index_name	idx_scan	table_size	table_total_size	all_indexes_size	index_size	size_of_all_indexes
timetravel	timetravel_2024	timetravel_2024_changed_idx	0	27 MB	49 MB	21 MB	16 MB	87 MB
timetravel	timetravel_2024	timetravel_2024_deleted_idx	0	27 MB	49 MB	21 MB	1424 kB	87 MB
timetravel	timetravel_2024	timetravel_2024_timetravelid_idx	0	27 MB	49 MB	21 MB	4408 kB	87 MB
timetravel	timetravel_part_vals	timetravel_part_vals_pk	0	8192 bytes	24 kB	16 kB	16 kB	87 MB
timetravel	timetravel_pk	timetravle_pk_pk	0	4360 kB	6568 kB	2208 kB	2208 kB	87 MB