Eliminación de índices no utilizados en PostgreSQL

En PostgreSQL, los índices son tablas de consulta especiales que el motor de búsqueda de la base de datos puede utilizar para acelerar las operaciones de recuperación de datos. Se utilizan para localizar datos rápidamente sin tener que buscar en cada fila de una tabla de una base de datos.

Utilización

Los índices se utilizan para mejorar la velocidad de las operaciones de recuperación de datos, pero pueden ralentizar las operaciones de escritura como INSERT, UPDATE, y DELETE debido a la sobrecarga adicional de mantenimiento, como la actualización del índice con cada modificación de los datos. Deben eliminarse si no se utilizan para mejorar el rendimiento de la base de datos y reducir los costes de almacenamiento.

DROP INDEX IF EXISTS index_name;

En esta sintaxis, DROP INDEX IF EXISTS index_name elimina el índice si existe, evitando mensajes de error si el índice no está presente.

Ejemplos

1. Creación de índices básicos

CREATE INDEX idx_customer_name
ON customers (customer_name);

Este ejemplo crea un índice en la columna customer_name de la tabla customers para mejorar el rendimiento de las consultas que impliquen este campo.

2. Eliminar un índice no utilizado

DROP INDEX IF EXISTS idx_customer_name;

Este comando elimina el índice idx_customer_name, liberando recursos si este índice no está proporcionando ninguna ventaja de rendimiento.

3. Identificar y eliminar índices no utilizados

SELECT indexrelid::regclass AS index_name,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

La primera consulta ayuda a identificar los índices que nunca se han utilizado (idx_scan = 0). Aquí, idx_scan representa el número de veces que se ha utilizado el índice en una consulta. Sustituye idx_unused por el nombre real del índice identificado en la consulta:

DROP INDEX IF EXISTS idx_unused;

Consejos y buenas prácticas

• Controla regularmente el uso del índice. Utiliza vistas del sistema como pg_stat_user_indexes para hacer un seguimiento del uso de los índices y detectar candidatos a ser eliminados.
• Evalúa la necesidad de un índice. Asegúrate de que cada índice beneficia significativamente el rendimiento de la consulta antes de conservarlo.
• Utiliza la cláusula IF EXISTS. Esto evita errores si el índice no existe al intentar eliminarlo.
• Considera el impacto en las operaciones de escritura. Demasiados índices pueden ralentizar las operaciones de INSERT, UPDATE, y DELETE debido a la sobrecarga adicional de mantenimiento.
• Tipos de índices. Comprende que los distintos tipos de índices (por ejemplo, B-tree, hash, GIN, GiST) pueden ser más adecuados para casos de uso específicos.
• Añade advertencias para DROP INDEX. Eliminar un índice puede aumentar el tiempo de consulta de las consultas que antes dependían de él; haz siempre primero una prueba en un entorno que no sea de producción.
• Ten en cuenta el almacenamiento. Los índices no utilizados consumen espacio en disco y deben eliminarse para optimizar la eficiencia del almacenamiento.