Unused indexes

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

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

Medium (Medium number of false-positive results)

Query license:

MIT License

Fixing suggestion:

Remove the unused indexes.

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL query	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.

SQL query

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.

Categories where the query belongs to

Category name	Category 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.

Category name

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

Reference materials for further reading

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

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