Finding Unused Indexes in YugabyteDB (YSQL)

As your application evolves, it’s common to accumulate indexes that are no longer being used. These unused indexes take up storage, slow down write performance, and can make maintenance tasks like vacuuming and analyzing more expensive.

Fortunately, YugabyteDB (YSQL) offers PostgreSQL-compatible system views that help you identify indexes that may be gathering dust.

Why Care About Unused Indexes?

Unused indexes:

Consume storage unnecessarily
Add overhead on every INSERT, UPDATE, and DELETE
Can cause confusion in schema management

Regularly auditing your indexes can lead to performance and cost benefits.

Query to Identify Unused Indexes

Here’s a simple query you can run to find potentially unused indexes in your YugabyteDB cluster:

				
					SELECT schemaname,
       relname AS table_name,
       indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       idx_scan,
       i.indisunique AS is_unique,
       i.indisprimary AS is_primary
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON s.indexrelid = i.indexrelid
 WHERE idx_scan = 0
   AND NOT i.indisprimary
   AND NOT i.indisunique
 ORDER BY pg_relation_size(i.indexrelid) DESC;

This query filters out:

Indexes that have been scanned at least once (idx_scan > 0)
Primary key and unique constraint indexes (these are typically essential)

It returns the unused indexes, ordered by size, to help prioritize cleanup.

Example:

				
					rabbit=# CREATE TABLE annoying_critters(id BIGINT PRIMARY KEY, type TEXT);
CREATE TABLE

rabbit=# INSERT INTO annoying_critters VALUES (1, 'Rabbit'), (2, 'Mole'), (3, 'CockroachDB');
INSERT 0 3

rabbit=# UPDATE annoying_critters SET type = 'Cockroach' WHERE id = 3; -- Oops!
UPDATE 1

rabbit=# CREATE INDEX annoying_critters_type_idx ON annoying_critters(type);
CREATE INDEX

rabbit=# SELECT schemaname,
rabbit-#       relname AS table_name,
rabbit-#       indexrelname AS index_name,
rabbit-#       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
rabbit-#       idx_scan,
rabbit-#       i.indisunique AS is_unique,
rabbit-#       i.indisprimary AS is_primary
rabbit-#  FROM pg_stat_user_indexes s
rabbit-#  JOIN pg_index i ON s.indexrelid = i.indexrelid
rabbit-# WHERE idx_scan = 0
rabbit-#   AND NOT i.indisprimary
rabbit-#   AND NOT i.indisunique
rabbit-# ORDER BY pg_relation_size(i.indexrelid) DESC;
 schemaname |    table_name     |         index_name         | index_size | idx_scan | is_unique | is_primary
------------+-------------------+----------------------------+------------+----------+-----------+------------
 public     | annoying_critters | annoying_critters_type_idx | 0 bytes    |        0 | f         | f
(1 row)

rabbit=# SELECT * FROM annoying_critters WHERE type = 'Rabbit';
 id |  type
----+--------
  1 | Rabbit
(1 row)

rabbit=# SELECT schemaname,
rabbit-#       relname AS table_name,
rabbit-#       indexrelname AS index_name,
rabbit-#       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
rabbit-#       idx_scan,
rabbit-#       i.indisunique AS is_unique,
rabbit-#       i.indisprimary AS is_primary
rabbit-#  FROM pg_stat_user_indexes s
rabbit-#  JOIN pg_index i ON s.indexrelid = i.indexrelid
rabbit-# WHERE idx_scan = 0
rabbit-#   AND NOT i.indisprimary
rabbit-#   AND NOT i.indisunique
rabbit-# ORDER BY pg_relation_size(i.indexrelid) DESC;
 schemaname | table_name | index_name | index_size | idx_scan | is_unique | is_primary
------------+------------+------------+------------+----------+-----------+------------
(0 rows)

A Word of Caution

Just because an index hasn’t been scanned recently doesn’t mean it’s safe to drop. Consider:

Query patterns might change seasonally or infrequently
The stats collector may have been reset recently
Some indexes may be used by foreign key constraints or specialized queries

Always test index removal in a staging environment and monitor impact carefully!

Wrapping up

Index bloat is a silent performance killer. Keep your schema lean and efficient by checking for unused indexes periodically. YugabyteDB’s PostgreSQL compatibility makes it easy to leverage familiar tooling for this task.

Bonus

To make cleanup even easier, I’ve added a column that generates the exact DROP INDEX statement for each unused index:

				
					SELECT schemaname,
       relname AS table_name,
       indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       idx_scan,
       i.indisunique AS is_unique,
       i.indisprimary AS is_primary,
       'DROP INDEX IF EXISTS ' || quote_ident(schemaname) || '.' || quote_ident(indexrelname) || ';' AS drop_index_cmd
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON s.indexrelid = i.indexrelid
 WHERE idx_scan = 0
   AND NOT i.indisprimary
   AND NOT i.indisunique
 ORDER BY pg_relation_size(i.indexrelid) DESC;

Have Fun!