Unused indexes

Back to Catalog
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 License
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.

SQL Query to Generate FixDescription
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:

NameDescription
Data at the database physical levelQueries of this category provide information about the disk usage.
PerformanceQueries of this category provide information about indexes in a database.

Further reading and related materials:

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).