Optimización de consultas en PostgreSQL: guía práctica para consultas lentas

Optimización de consultas en PostgreSQL: guía práctica para consultas lentas#

Una consulta de PostgreSQL puede volverse lenta sin cambiar una sola línea de SQL.

Supongamos que una tabla orders lleva meses creciendo. Una consulta que antes respondía con rapidez ahora tarda alrededor de 15 segundos y empieza a provocar timeouts en un dashboard.

El caso que utilizaremos es hipotético, pero el proceso de diagnóstico, las consultas SQL, las herramientas de PostgreSQL y las técnicas de indexación son reales. Los tiempos se utilizan como valores ilustrativos para mostrar con claridad el proceso de optimización.

La reacción inmediata podría ser crear un índice.

Eso sería empezar por la solución antes de entender el problema.

La pregunta más útil es:

¿Qué está haciendo PostgreSQL para ejecutar esta consulta y por qué necesita hacer tanto trabajo?

Un proceso razonable sigue este orden:

detectar → analizar → diagnosticar → optimizar → verificar.

Cómo detectar consultas lentas en PostgreSQL#

Si ya sabes qué consulta causa el problema, puedes ir directamente a su plan de ejecución.

En producción no siempre tenemos esa ventaja. Puede estar claro que un endpoint o dashboard tarda demasiado sin saber todavía qué sentencia SQL es responsable.

Hay dos mecanismos especialmente útiles para empezar.

Detectar consultas lentas con los logs#

log_min_duration_statement permite registrar las sentencias que superan un determinado tiempo de ejecución.

Por ejemplo:

log_min_duration_statement = 250ms

El umbral no define por sí mismo qué es una consulta lenta.

Una consulta analítica de 300 ms ejecutada ocasionalmente puede ser aceptable. Una consulta de 300 ms ejecutada cientos de veces por segundo puede tener un impacto completamente distinto.

El log responde una pregunta concreta:

¿Qué ejecuciones individuales están tardando más de lo esperado?

Encontrar consultas costosas con pg_stat_statements#

pg_stat_statements permite observar el problema desde el conjunto de la carga.

Una consulta no tiene que ser la más lenta individualmente para resultar costosa. Si se ejecuta con suficiente frecuencia, una consulta moderadamente lenta puede consumir más tiempo total de base de datos que otra mucho más lenta pero poco frecuente.

Un punto de partida útil es:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_statements debe estar habilitado antes de poder consultar esta vista. El módulo tiene que cargarse en PostgreSQL y la extensión debe estar creada en la base de datos.

Una vez localizada la consulta que merece atención, toca analizar cómo la está ejecutando PostgreSQL.

Cómo analizar el rendimiento con EXPLAIN ANALYZE#

Consideremos esta consulta:

SELECT id, total, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;

A nivel de SQL sabemos qué queremos:

• filtrar por una cuenta;
• obtener primero las filas más recientes;
• devolver solo 50 resultados.

Lo que no sabemos es cómo PostgreSQL encuentra esas filas.

Podemos inspeccionarlo con:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 50;

Cuando se analiza manualmente una consulta parametrizada, hay que sustituir $1 por un valor representativo o analizar la sentencia preparada correspondiente.

EXPLAIN muestra el plan elegido por PostgreSQL.

ANALYZE ejecuta realmente la sentencia y añade tiempos y cantidades de filas reales.

BUFFERS ayuda a observar la actividad de buffers asociada al plan.

Hay un detalle importante:

EXPLAIN ANALYZE ejecuta la sentencia.

Con un SELECT normal suele ser exactamente lo que buscamos. Con UPDATE, DELETE, INSERT, MERGE u otra operación con efectos secundarios, esos efectos también ocurren salvo que el análisis se proteja, por ejemplo dentro de una transacción que después se revierta.

Cómo leer un plan de ejecución de PostgreSQL#

No hace falta entender todos los nodos del planner para empezar a encontrar información útil.

Conviene comenzar por unas pocas preguntas.

¿Cuántas filas está procesando PostgreSQL?#

Imaginemos un plan con algo parecido a:

Seq Scan on orders
Rows Removed by Filter: 999550

Si PostgreSQL inspecciona cerca de un millón de filas para devolver solo unos cientos de registros, el problema no es devolver esos cientos.

El problema es el trabajo necesario para encontrarlos.

Una diferencia grande entre filas procesadas y filas devueltas merece atención.

¿Las filas estimadas se parecen a las reales?#

PostgreSQL toma decisiones basándose en estimaciones.

EXPLAIN ANALYZE permite compararlas con la ejecución real.

Si el planner espera unos cientos de filas y durante la ejecución aparecen varios miles, esa diferencia puede afectar a decisiones como utilizar un índice o realizar un escaneo secuencial.

La diferencia no nos dice automáticamente cuál es la solución.

Nos dice que el modelo que utiliza el planner no representa completamente lo que está ocurriendo con los datos.

¿Un Seq Scan significa que algo está mal?#

No necesariamente.

Un escaneo secuencial puede ser la mejor opción si PostgreSQL espera leer una parte importante de la tabla.

El caso interesante aparece cuando una consulta es selectiva y, aun así, obliga a inspeccionar grandes cantidades de datos que después se descartan.

Supongamos esta segunda consulta:

SELECT DATE(created_at) AS day,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
  AND region = 'EMEA'
GROUP BY day
ORDER BY day DESC;

La tabla tiene 2,4 millones de filas y solo dispone de:

CREATE INDEX idx_created
ON orders(created_at);

La consulta filtra por status y region, mientras que el índice existente parte de created_at.

Existe un índice.

Simplemente no está alineado con el patrón de acceso de esta consulta.

Caso práctico: de 15 segundos a 8 ms#

Volvamos a la consulta principal:

SELECT id, total, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;

El patrón de acceso nos da una pista clara sobre el índice:

1. filtrar por account_id;
2. leer en orden created_at DESC;
3. detenerse después de 50 filas;
4. devolver id, total y created_at.

Un índice cubriente para esta consulta podría ser:

CREATE INDEX CONCURRENTLY idx_orders_account_date
ON orders (account_id, created_at DESC)
INCLUDE (id, total);

La estructura tiene una razón.

account_id corresponde al filtro por igualdad:

WHERE account_id = $1

created_at DESC coincide con el orden solicitado:

ORDER BY created_at DESC

id y total forman parte del resultado, pero no necesitan participar en la búsqueda del índice, por lo que pueden incluirse como columnas no clave.

Esto hace posible un Index Only Scan cuando PostgreSQL puede resolver también las comprobaciones de visibilidad necesarias sin visitar repetidamente el heap.

No significa que PostgreSQL vaya a elegir siempre un Index Only Scan.

En nuestro escenario hipotético, supongamos que el plan inicial tarda aproximadamente 15 segundos y que el plan optimizado completa la consulta en unos 8 ms.

La cifra sirve para ilustrar el cambio.

Lo importante es entender por qué mejora.

PostgreSQL no se volvió radicalmente más rápido ejecutando exactamente el mismo trabajo.

El nuevo acceso le permitió evitar gran parte de ese trabajo.

Cómo optimizar índices PostgreSQL según la consulta#

El segundo ejemplo utiliza el mismo principio.

La consulta es:

SELECT DATE(created_at) AS day,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
  AND region = 'EMEA'
GROUP BY day
ORDER BY day DESC;

Un índice más alineado con este patrón podría ser:

CREATE INDEX idx_orders_region_status_created
ON orders(region, status, created_at)
INCLUDE (total_amount);

y después:

ANALYZE orders;

Aquí:

• region y status ayudan a resolver los filtros por igualdad;
• created_at sigue formando parte del acceso indexado;
• total_amount está disponible porque la agregación lo necesita.

Esta estructura permite que PostgreSQL considere una estrategia basada únicamente en el índice cuando las demás condiciones también lo permiten.

La enseñanza no es copiar este índice.

Es construirlo a partir de la consulta.

Antes de crear uno, conviene responder:

• ¿Qué columnas reducen el conjunto de búsqueda?
• ¿Qué columnas determinan el orden?
• ¿Qué columnas necesita devolver o procesar la consulta?
• ¿Cuántas filas suelen coincidir?
• ¿El índice actual refleja ese patrón?

Un índice debe responder a una carga concreta.

No debería existir únicamente porque una columna parece importante.

Por qué PostgreSQL no usa un índice#

Que un índice exista no obliga al planner a utilizarlo.

PostgreSQL compara diferentes planes y elige el que estima más barato.

Las estadísticas participan en esa decisión.

ANALYZE actualiza información sobre el contenido y la distribución de los datos:

ANALYZE orders;

Este comando no acelera una consulta directamente.

Mejora la información disponible para que el planner tome sus decisiones.

Si PostgreSQL ignora un índice que parece relevante, conviene comparar primero:

filas estimadas
vs.
filas reales

antes de asumir que el planner se está equivocando.

Una estimación deficiente puede hacer que un escaneo secuencial parezca más barato que una estrategia basada en índices.

El problema puede estar en el índice, en las estadísticas, en la distribución de los datos o en una combinación de esos factores.

Cuándo conviene reescribir la consulta#

Los índices no resuelven todos los problemas de rendimiento.

La forma del SQL también importa.

Funciones o expresiones aplicadas sobre columnas indexadas pueden cambiar la utilidad de un índice para determinados predicados.

Si la expresión forma parte del patrón real de acceso, puede tener sentido estudiar un índice de expresión.

La paginación es otro ejemplo.

Incrementar continuamente OFFSET puede obligar a PostgreSQL a seguir trabajando con filas que la aplicación ya no necesita.

Cuando el modelo de navegación lo permite, keyset pagination puede continuar desde el último valor ya visto en lugar de saltar una cantidad cada vez mayor de filas.

El rendimiento depende de la relación entre:

SQL
+ distribución de datos
+ índices
+ estadísticas del planner
+ plan de ejecución

Optimizar solo una de esas piezas puede dejar intacto el cuello de botella real.

Herramientas de PostgreSQL para analizar consultas#

Las herramientas principales responden preguntas distintas.

Slow query logs ayudan a encontrar ejecuciones individuales que superan una duración relevante.

pg_stat_statements ayuda a localizar patrones costosos dentro de la carga acumulada.

EXPLAIN muestra el plan que PostgreSQL pretende utilizar.

EXPLAIN ANALYZE añade información de la ejecución real.

BUFFERS ayuda a entender cuánta actividad de buffers estuvo asociada al plan.

Funcionan mejor como partes de la misma investigación:

Detectar
↓
Analizar
↓
Diagnosticar
↓
Optimizar
↓
Verificar

Cuándo un índice no resuelve el problema#

Los índices tienen un coste.

Ocupan almacenamiento y deben mantenerse cuando las filas son insertadas, modificadas o eliminadas.

Un índice compuesto que mejora una ruta de lectura crítica puede ser una buena decisión.

Crear índices para todas las combinaciones posibles de filtros normalmente no lo es.

También existe un límite más fundamental.

Supongamos que una consulta necesita agregar realmente millones de filas.

Un índice puede mejorar cómo se localizan esas filas, pero no elimina el coste de procesar millones de registros.

En ese escenario, el cuello de botella puede trasladarse desde el acceso a los datos hacia la agregación.

Dependiendo de la carga, podrían tener más sentido estrategias como vistas materializadas o resúmenes precalculados.

Una optimización puede mover el cuello de botella.

Por eso importa más el nuevo plan de ejecución que el simple hecho de haber añadido un índice.

Checklist para optimizar consultas PostgreSQL#

Cuando una consulta empieza a degradarse, conviene seguir un proceso repetible:

1. Identifica el SQL costoso. Usa logs o estadísticas de carga.
2. Obtén el plan de ejecución. Ejecuta EXPLAIN (ANALYZE, BUFFERS) cuando sea seguro.
3. Compara filas procesadas y filas devueltas.
4. Compara filas estimadas y reales.
5. Entiende el método de acceso.
6. Compara los índices con el patrón real de la consulta.
7. Revisa las estadísticas del planner.
8. Revisa el SQL.
9. Vuelve a medir después del cambio.
10. Evalúa el coste del nuevo índice.

El proceso es más reutilizable que cualquier truco aislado de optimización.

Conclusión#

Optimizar consultas en PostgreSQL consiste, en gran medida, en entender dónde se está haciendo trabajo.

Una consulta lenta indica que PostgreSQL está gastando tiempo en alguna parte del camino. Los logs y pg_stat_statements ayudan a decidir qué investigar. EXPLAIN ANALYZE muestra lo que ocurrió realmente. Las filas procesadas, las estimaciones, los tipos de acceso y la actividad de buffers ayudan a explicar por qué.

En nuestro caso hipotético, la consulta pasa de aproximadamente 15 segundos a unos 8 milisegundos porque el nuevo índice corresponde con la forma en que la consulta filtra, ordena y recupera los datos.

La cifra exacta no es la enseñanza.

La enseñanza es que PostgreSQL deja de realizar gran parte del trabajo innecesario.

La secuencia útil es:

medir → entender → cambiar → volver a medir.

Un índice puede ser el resultado de ese proceso.

No debería ser la suposición inicial.