Un índice mal ajustado en PostgreSQL convirtió una consulta de 40 ms en cuatro segundos

En la tabla order_events, con unos cuatro millones de filas de eventos de pedidos de comercio electrónico, una consulta que debía traer los últimos 30 días de actividad de un customer_id concreto se quedó bloqueada durante una intervención nocturna. Tres semanas antes se había añadido un índice sobre customer_id, pero EXPLAIN ANALYZE mostró que PostgreSQL igualmente ejecutaba un escaneo secuencial de toda la tabla, con lo que una consulta que debía tardar 40 milisegundos pasó a tardar unos cuatro segundos.

SELECT * FROM order_events
WHERE customer_id = 8842
AND created_at > now() - interval '30 days'
ORDER BY created_at DESC;

La causa era un desajuste entre el índice y la cláusula ORDER BY de la consulta. El índice cubría solo customer_id, así que PostgreSQL localizaba rápido las filas del cliente, pero luego tenía que cargarlas todas en memoria y ordenarlas por created_at. Un índice compuesto sobre (customer_id, created_at DESC), ajustado al patrón real de acceso de la consulta, redujo el tiempo de ejecución a nueve milisegundos.

El mismo error ya había aparecido antes, en una aplicación de citas para el sector salud y en una herramienta de analítica para un cliente de logística, siempre porque un índice cubría solo la cláusula WHERE sin considerar las columnas usadas para ordenar, agrupar o unir tablas. Eso dio lugar a una rutina de cuatro pasos para cada consulta lenta: ejecutar EXPLAIN ANALYZE y buscar un Seq Scan en tablas de más de unos pocos miles de filas; revisar las columnas de ORDER BY, GROUP BY y JOIN para decidir si conviene un índice compuesto en lugar de varios índices simples; comprobar la selectividad de las columnas, ya que un campo de baja cardinalidad como un order_status con solo tres valores rara vez ayuda por sí solo y conviene combinarlo con una columna más selectiva; y por último registrar los tiempos reales en milisegundos que arroja EXPLAIN ANALYZE antes y después de cada cambio, en lugar de fiarse de una sensación.