EXPLAIN ANALYZE en PostgreSQL: leer planes de ejecución y optimizar queries lentas

Cuando una query va lenta, el primer instinto es añadir un índice o reescribir el SQL. Pero sin saber qué está haciendo realmente el planificador, estás adivinando. EXPLAIN ANALYZE te muestra exactamente cómo ejecuta PostgreSQL tu consulta, cuánto tiempo tarda cada paso y dónde están los cuellos de botella.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN muestra el plan estimado sin ejecutar la consulta. Usa las estadísticas de la tabla (actualizadas por VACUUM/ANALYZE) para estimar cuántas filas producirá cada nodo y qué coste tendrá. Es rápido pero solo muestra estimaciones.

EXPLAIN ANALYZE ejecuta la consulta de verdad y muestra los tiempos reales junto a las estimaciones. Esto es lo que necesitas para diagnosticar problemas reales. Ojo: si la query hace modificaciones (UPDATE, DELETE, INSERT), las ejecuta de verdad, así que úsala dentro de una transacción que luego hagas rollback si no quieres afectar datos.

-- Plan estimado sin ejecutar
EXPLAIN SELECT * FROM pedidos WHERE usuario_id = 42;

-- Plan real con tiempos (ejecuta la query)
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE usuario_id = 42;

-- Con mas detalle: buffers de cache, formato JSON...
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM pedidos WHERE usuario_id = 42;

Los nodos principales del plan

El plan de ejecución es un árbol de nodos. Cada nodo representa una operación, y los nodos hijos alimentan a los padres. Los más habituales son:

• Seq Scan: escaneo secuencial de toda la tabla, fila a fila. No usa índice. En tablas pequeñas puede ser más rápido que un Index Scan, pero en tablas grandes es señal de que falta un índice.
• Index Scan: usa un índice para localizar filas y luego accede al heap (la tabla) para obtener el resto de columnas.
• Index Only Scan: usa un índice y no necesita acceder al heap porque todas las columnas necesarias están en el índice. El más eficiente.
• Bitmap Heap Scan: primero crea un bitmap de páginas a leer usando un índice, y luego accede al heap en orden. Se usa cuando hay muchas filas a recuperar y el acceso aleatorio al heap sería demasiado costoso.
• Hash Join / Nested Loop / Merge Join: las tres estrategias de join. Hash Join construye una tabla hash de la relación más pequeña; Nested Loop itera por el exterior y busca en el interior; Merge Join necesita ambas relaciones ordenadas.

Cómo leer la salida

EXPLAIN ANALYZE
SELECT u.nombre, COUNT(p.id) AS total
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
GROUP BY u.nombre;

La salida tiene este aspecto para cada nodo:

HashAggregate  (cost=1250.00..1280.00 rows=500 width=64)
               (actual time=45.123..46.789 rows=482 loops=1)
  ->  Hash Left Join  (cost=450.00..1100.00 rows=10000 width=32)
                      (actual time=12.345..38.901 rows=10000 loops=1)

En cada nodo hay dos líneas:

• cost=inicio..total: coste estimado (en unidades arbitrarias). El inicio es cuándo empieza a devolver filas; el total es cuando termina.
• rows: filas estimadas.
• actual time=inicio..total: tiempos reales en milisegundos.
• rows=X loops=Y: filas reales devueltas y veces que se ejecutó el nodo (en joins el nodo interior se ejecuta una vez por fila exterior).

Las señales de alarma

Hay tres patrones que indican un problema claro:

1. Seq Scan en tabla grande: si ves un Seq Scan sobre una tabla con millones de filas y hay un predicado WHERE, probablemente falta un índice o el que existe no se está usando.
2. rows estimadas muy diferentes de rows reales: si el planificador estima 10 filas y el plan real devuelve 100.000, las estadísticas están desactualizadas. Ejecuta ANALYZE nombre_tabla y vuelve a mirar.
3. loops muy altos en Nested Loop: si un nodo interior se ejecuta 50.000 veces, el join está haciendo un bucle sobre muchas filas. Quizás falta un índice en la columna de join del lado interior.

Herramientas para visualizarlo

El texto plano de EXPLAIN puede ser difícil de leer en planes complejos. Hay varias herramientas online que lo visualizan como árbol interactivo. La más usada es explain.dalibo.com: pegas la salida de EXPLAIN (ANALYZE, FORMAT JSON) y te muestra el árbol con los nodos coloreados por coste.

Para usar el formato JSON:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pedidos WHERE usuario_id = 42;

Este artículo complementa directamente el de índices en PostgreSQL de esta misma serie: crear el índice correcto es un paso, pero verificar con EXPLAIN ANALYZE que el planificador lo usa es el paso siguiente. Y si tu query usa CTEs y sospechas que la materialización está afectando el rendimiento, el artículo sobre CTEs con WITH y WITH RECURSIVE explica el comportamiento de optimization fence que puede sorprenderte en el plan de ejecución.

Imagen: Pexels / cottonbro studio