Índices Duplicados y Redundantes

Los índices duplicados desperdician disco, ralentizan las escrituras y confunden a los DBA. Audita catálogos y pg_stat_user_indexes antes de añadir otro índice compuesto en PostgreSQL 18.4.

Receta

Tarjeta de receta de referencia rápida - lista para copiar y pegar.

SELECT indexrelid::regclass AS index_name, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'orders'
ORDER BY idx_scan;

Cuándo usar esto: La tabla tiene muchos índices, la latencia de inserción aumenta o autovacuum no puede seguir el ritmo.

Ejemplo de Trabajo

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
-- Redundante: prefijo izquierdo de un compuesto
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
 
-- Intención duplicada (mismas columnas, diferente nombre)
CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);
 
SELECT
  c.relname AS table_name,
  i.relname AS index_name,
  pg_get_indexdef(i.oid) AS definition,
  s.idx_scan
FROM pg_class c
JOIN pg_index ix ON ix.indrelid = c.oid
JOIN pg_class i ON i.oid = ix.indexrelid
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = i.oid
WHERE c.relname = 'orders'
ORDER BY pg_get_indexdef(i.oid);

Elimina orders_customer_idx si todas las consultas que necesitan customer_id también filtran o ordenan por created_at, o mantén el índice de una sola columna solo cuando existan consultas que usen solo el prefijo y muestren idx_scan > 0.

Lo que esto demuestra:

• Detección de redundancia de prefijo mediante pg_get_indexdef
• Evidencia de idx_scan antes de eliminar
• Colisiones de nombres que ocultan duplicados

Análisis Profundo

Cómo Funciona

• El índice A es prefijo-redundante del índice B si las columnas clave de A son un prefijo izquierdo de las de B y ninguna restricción única requiere A por sí sola.
• Los índices duplicados tienen definiciones de clave idénticas (posiblemente nombres diferentes debido a migraciones repetidas).
• Las restricciones PRIMARY KEY y UNIQUE crean índices; no dupliques con un INDEX simple sobre las mismas columnas.
• Los índices no utilizados (idx_scan = 0 desde el reinicio de las estadísticas) son candidatos para ser eliminados después de una ventana de observación.

Consultas de Auditoría

-- Encuentra índices nunca usados (desde el reinicio de las estadísticas)
SELECT relname, indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname NOT IN ('pg_catalog', 'information_schema');
 
-- Ranking de tamaño
SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Trampas Comunes

• Eliminar índice de prefijo mientras el ORM solo usa igualdad en la primera columna - El plan puede degradarse. Solución: Comprueba pg_stat_statements y idx_scan.
• Eliminar índice UNIQUE pensando que es redundante - La restricción también se elimina. Solución: Elimina la restricción o vuelve a añadir UNIQUE en el índice restante.
• Reiniciar estadísticas y luego eliminar inmediatamente - idx_scan cero engaña. Solución: Observa durante un ciclo comercial.
• Uso solo en réplica - Las estadísticas en el primario pueden no reflejar las consultas de informes en la réplica si no se enrutan. Solución: Agrega estadísticas también de las réplicas.
• Creación concurrente de duplicados - Herramientas de migración se ejecutan de nuevo. Solución: Migraciones idempotentes con IF NOT EXISTS.

Alternativas

Preguntas Frecuentes

Relacionado

• Orden de Índices Multicolumna - Reglas de prefijo
• REINDEX & REINDEX CONCURRENTLY - Reconstruir después de cambios
• Hinchazón de Tablas e Índices - Medición de tamaño
• Mejores Prácticas de Mantenimiento de Índices - Higiene continua

Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, y PostGIS 3.5+.