Unused indexes

Goal	Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.
Type	Problem detection Each row in the result could represent a flaw in the design
Reliability	Medium Medium number of false-positive results
License	MIT (opens in new tab)
Fixing Suggestion	Remove the unused indexes.
Data Source	INFORMATION_SCHEMA+system catalog
SQL Query	SELECT A.schemaname AS schema, A.relname AS table, A.indexrelname AS index, pg_size_pretty(pg_relation_size(A.indexrelid)) AS index_size FROM pg_stat_user_indexes A JOIN pg_statio_user_indexes B USING (schemaname, relname, indexrelname) INNER JOIN information_schema.schemata C ON A.schemaname=C.schema_name WHERE A.idx_scan=0 AND A.idx_tup_read=0 AND A.idx_tup_fetch=0 AND B.idx_blks_read=0 AND B.idx_blks_hit=0 AND (A.schemaname = 'public' OR C.schema_owner<>'postgres') ORDER BY A.schemaname, A.relname, A.indexrelname;

SQL statements that help generate fixes for the identified problem.

Fix Action #1

SELECT format('DROP INDEX %1$I.%2$I;', A.schemaname, A.indexrelname) AS statements
FROM pg_stat_user_indexes A JOIN pg_statio_user_indexes B USING (schemaname, relname, indexrelname)
INNER JOIN information_schema.schemata C ON A.schemaname=C.schema_name
WHERE A.idx_scan=0
AND A.idx_tup_read=0
AND A.idx_tup_fetch=0
AND B.idx_blks_read=0
AND B.idx_blks_hit=0
AND (A.schemaname = 'public'
OR C.schema_owner<>'postgres')
ORDER BY A.schemaname, A.relname, A.indexrelname;

Drop the index.

SQL Query to Generate Fix Description

SQL Query to Generate Fix	Description
`SELECT format('DROP INDEX %1$I.%2$I;', A.schemaname, A.indexrelname) AS statements FROM pg_stat_user_indexes A JOIN pg_statio_user_indexes B USING (schemaname, relname, indexrelname) INNER JOIN information_schema.schemata C ON A.schemaname=C.schema_name WHERE A.idx_scan=0 AND A.idx_tup_read=0 AND A.idx_tup_fetch=0 AND B.idx_blks_read=0 AND B.idx_blks_hit=0 AND (A.schemaname = 'public' OR C.schema_owner<>'postgres') ORDER BY A.schemaname, A.relname, A.indexrelname;`	Drop the index.

SELECT format('DROP INDEX %1$I.%2$I;', A.schemaname, A.indexrelname) AS statements
FROM pg_stat_user_indexes A JOIN pg_statio_user_indexes B USING (schemaname, relname, indexrelname)
INNER JOIN information_schema.schemata C ON A.schemaname=C.schema_name
WHERE A.idx_scan=0
AND A.idx_tup_read=0
AND A.idx_tup_fetch=0
AND B.idx_blks_read=0
AND B.idx_blks_hit=0
AND (A.schemaname = 'public'
OR C.schema_owner<>'postgres')
ORDER BY A.schemaname, A.relname, A.indexrelname;

Drop the index.

Categories

This query is classified under the following categories:

Data at the database physical level

Queries of this category provide information about the disk usage.

Performance

Queries of this category provide information about indexes in a database.

Name	Description
Data at the database physical level	Queries of this category provide information about the disk usage.
Performance	Queries of this category provide information about indexes in a database.

Further reading and related materials:

Smell "Index abuse": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).

Reference
Smell "Index abuse": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).