Ver índices no usados en PostgreSQL con pg_stat_user_indexes

¿Qué índices de PostgreSQL no reciben consultas? Revisa idx_scan en pg_stat_user_indexes y usa ese dato como primera señal antes de decidir si un índice sigue siendo necesario.

Consulta básica

SELECT
    schemaname,
    relname AS tabla,
    indexrelname AS indice,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;

idx_scan indica cuántas veces se ha usado ese índice en un escaneo. Si aparece a cero, ese índice no se ha usado desde que se reiniciaron las estadísticas.

Ver los índices con más contexto

SELECT
    s.schemaname,
    s.relname AS tabla,
    s.indexrelname AS indice,
    s.idx_scan,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS tamano
FROM pg_stat_user_indexes s
WHERE s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC;

Así puedes empezar por los índices que ocupan más espacio.

Antes de borrar un índice

No borres un índice solo porque salga en esta consulta. Puede ser un índice útil para consultas mensuales, cierres, informes o tareas que todavía no se han ejecutado desde el último reinicio de estadísticas.

Antes de eliminarlo, revisa al menos:

SELECT
    relname AS tabla,
    indexrelname AS indice,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'nombre_del_indice';

Si después de revisar la aplicación y un periodo suficiente sigue sin usarse, entonces ya tiene sentido valorar si sobra.