Query lenta en PostgreSQL: ¿Cómo optimizar un JOIN con 3 tablas?

Surfing

Tengo una consulta en PostgreSQL que está tardando una eternidad (más de 5 segundos) y está afectando a una parte crítica de mi aplicación. Involucra 3 tablas: users, orders y products. La idea es obtener los últimos 10 productos comprados por un usuario específico.

La query es algo así:

SELECT p.name, o.created_at
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.user_id = 123
ORDER BY o.created_at DESC
LIMIT 10;

He revisado y todas las columnas id y *_id son claves primarias o foráneas, por lo que deberían tener índices. ¿Qué estoy pasando por alto? ¿Alguna idea para atacar este problema?

Mixology

Un clásico. Lo primero que debes hacer es ejecutar un EXPLAIN ANALYZE delante de tu consulta. Te mostrará el plan de ejecución que está usando PostgreSQL.

Busca si está haciendo "Sequential Scans" en tablas grandes. Aunque las columnas FK tengan índices, a veces el optimizador decide no usarlos. El EXPLAIN te dirá exactamente dónde está el cuello de botella.

KPop

Buen punto el del compañero. Añado algo más: el problema podría ser el orden del JOIN y el WHERE. La base de datos podría estar uniendo todas las tablas y LUEGO filtrando por usuario, lo cual es muy ineficiente.

Un índice compuesto en la tabla orders sobre (user_id, created_at) podría hacer magia aquí. Le permitiría encontrar rápidamente los pedidos del usuario y ya ordenados, antes de hacer los joins.

Surfing

¡Bingo! El EXPLAIN ANALYZE mostró un "Seq Scan" masivo en orders.

Creé el índice compuesto (user_id, created_at) como sugirió el segundo comentario y el tiempo de la consulta ha bajado a menos de 50 milisegundos. ¡Increíble la diferencia que puede hacer un buen índice!

Muchísimas gracias a los dos, me habéis salvado.