Optimización de consultas PostgreSQL: de 14 horas a minutos
Una consulta ejecutada 14.600 veces en 48 horas acumulaba 14,8 horas de tiempo total de ejecución. La solución no requirió cambiar hardware.
Antes de la intervención
- Ejecuciones en 48 horas
- 14.600
- Tiempo total acumulado
- 14,8 horas
- Tiempo promedio por ejecución
- 3,6 segundos
- Tipo de escaneo
- Seq Scan (full table)
Después de la intervención
- Tiempo promedio por ejecución
- < 2 ms
- Reducción de tiempo
- > 99,9%
- Tipo de escaneo
- Index Scan (selectivo)
- Cambio de hardware
- Ninguno
El contexto del problema
El cliente tenía un sistema de gestión empresarial con una base de datos PostgreSQL que había crecido significativamente en los últimos dos años. El sistema funcionaba, pero cada vez más lento: algunas operaciones que antes tomaban segundos ahora tomaban decenas de segundos, y en momentos de carga alta, la aplicación quedaba prácticamente inutilizable.
La primera propuesta del proveedor anterior fue actualizar el servidor: más RAM, más CPU. El cliente decidió consultar antes de invertir en hardware.
El diagnóstico
El primer paso fue habilitar pg_stat_statements, la extensión de PostgreSQL que registra estadísticas acumuladas de todas las consultas ejecutadas. Con esta extensión activa, se pudo identificar rápidamente cuáles eran las consultas que más tiempo consumían.
La consulta más costosa tenía estas características en 48 horas de observación:
calls: 14.600 total_exec_time: 53.280 segundos mean_exec_time: 3,648 ms → 3.648 ms (3,6 segundos promedio) rows: varía
Aclarando: 53.280 segundos son casi 14,8 horas de tiempo de CPU acumulado en solo dos días. Una sola consulta, ejecutándose repetidamente, consumiendo más tiempo de procesamiento del que habría en un día completo.
El siguiente paso fue analizar el plan de ejecución con EXPLAIN (ANALYZE, BUFFERS):
Seq Scan on pedidos_detalle (cost=0.00..45231.00 rows=1 width=...) Filter: (estado_id = 3 AND cliente_id = $1) Rows Removed by Filter: 2847193
El problema estaba claro: PostgreSQL estaba escaneando secuencialmente una tabla con casi 2,85 millones de filas, filtrando 2.847.193 filas para devolver solo las relevantes. Cada ejecución de la consulta leía toda la tabla completa.
La causa raíz
La tabla pedidos_detalletenía índices en pedido_idy en producto_id, pero no tenía un índice compuesto en (cliente_id, estado_id), que eran exactamente las columnas usadas en el filtro de la consulta más ejecutada.
Esto significa que cuando la aplicación buscaba los detalles de pedido de un cliente en un estado específico, PostgreSQL no tenía otra opción que revisar cada una de las 2,85 millones de filas de la tabla.
También se identificó que el autovacuum estaba configurado con los valores por defecto, lo que resultaba en una tabla con una cantidad significativa de filas muertas (dead tuples) que inflaban el tamaño físico y empeoraban los escaneos secuenciales.
La solución
La intervención fue simple pero requería conocer exactamente qué hacer:
- 01.
Índice compuesto en las columnas del filtro
CREATE INDEX CONCURRENTLY idx_pedidos_detalle_cliente_estado
ON pedidos_detalle (cliente_id, estado_id); - 02.
VACUUM ANALYZE para limpiar filas muertas y actualizar estadísticas
VACUUM ANALYZE pedidos_detalle; - 03.
Ajuste del autovacuum para la tabla
Se configuró un autovacuum más agresivo para esta tabla específica, reduciendo el umbral de activación y aumentando la frecuencia.
El índice se creó con CONCURRENTLYpara no bloquear la tabla durante la construcción — una operación que en producción no puede generar tiempo de inactividad.
El resultado
Después de la creación del índice, el plan de ejecución cambió a un Index Scan:
Index Scan using idx_pedidos_detalle_cliente_estado on pedidos_detalle Index Cond: ((cliente_id = $1) AND (estado_id = 3)) Execution Time: 1.8 ms
De 3.648 ms promedio a 1,8 ms. Una reducción del 99,95% en el tiempo de ejecución de la consulta más costosa del sistema. Sin cambiar una sola línea de código de la aplicación. Sin actualizar el servidor.
El mismo proceso de diagnóstico identificó dos consultas adicionales con problemas similares que también se optimizaron en la misma intervención:
| Consulta | Antes | Después | Mejora |
|---|---|---|---|
| Búsqueda de facturas por cliente y estado | 2,3 s | 3 ms | 99,9% |
| Reporte de movimientos por rango de fechas | 4,9 s | 147–676 ms | 85–97% |
El impacto para el usuario final fue inmediato: las operaciones que antes tardaban varios segundos quedaron imperceptibles. La carga del servidor bajó drásticamente y la sensación de lentitud general del sistema desapareció.
La lección
Los problemas de rendimiento en PostgreSQL rara vez requieren más hardware. Casi siempre tienen una causa específica en la configuración de índices, el plan de ejecución de las consultas o la configuración del motor.
El diagnóstico con pg_stat_statementsy EXPLAIN ANALYZEpermite identificar el problema exacto en lugar de adivinar. Una vez identificado, la solución suele ser quirúrgica: cambio mínimo, máximo impacto.
Servicios relacionados con este caso