Entiende qué está haciendo realmente tu base de datos: Guía práctica de EXPLAIN ANALYZE en PostgreSQL

Si alguna vez has lanzado una consulta y te has quedado mirando la relojito de carga pensando “¿pero por qué tanto?”, es hora de dejar de adivinar.

Casi siempre que una query va lenta no es culpa de PostgreSQL. Es que no sabemos qué está haciendo internamente.

Y ahí es donde brilla EXPLAIN ANALYZE.

Es lo más parecido a abrir el capó del carro mientras el motor sigue encendido: ves exactamente qué pasa, cuánto tarda cada parte y dónde se está atascando todo.

¿Qué hace exactamente EXPLAIN ANALYZE?

EXPLAIN ANALYZE SELECT ...

Son dos comandos en uno:

• EXPLAIN –> te enseña el plan estimado que creó el optimizador (el planner). Es la apuesta teórica de cuál va a ser la forma más barata de ejecutar tu consulta.
• ANALYZE –> ejecuta de verdad la consulta y te añade datos reales:
  • tiempos que tardó cada paso
  • cuántas filas se procesaron realmente
  • cuántas veces se ejecutó cada nodo (loops)

Resumiendo:

EXPLAIN = teoría
ANALYZE = realidad

Y en rendimiento… la realidad siempre gana.

Cuidado importante (no te lo saltes)

EXPLAIN ANALYZE DELETE FROM usuarios WHERE ...

¡Esto borra de verdad! Igual con UPDATE, INSERT … SELECT, etc.

La forma segura de probar consultas de escritura es:

BEGIN;
EXPLAIN ANALYZE DELETE FROM usuarios WHERE email ILIKE '%@prueba.com';
ROLLBACK;

Siempre dentro de una transacción que puedas deshacer. Es un hábito que te ahorra muchos sustos.

Cómo leer el plan sin volverte loco

El plan de ejecución es un árbol invertido (se lee de abajo hacia arriba).

• Los nodos de abajo producen filas
• Los nodos de arriba las consumen / filtran / ordenan / agrupan
• El nodo de más arriba (el raíz) muestra el tiempo total real de toda la consulta

Piensa en él como una cadena de montaje: si una máquina de abajo está lenta, todo el proceso se resiente.

Los indicadores que realmente importan (míralos en este orden)

1. Actual Time –> el que manda

Aparece así:

Actual Time=0.015..45.872

• Primer número: tiempo hasta que el nodo empieza a devolver la primera fila
• Segundo número: tiempo total hasta que termina de entregar todas las filas

El segundo valor del nodo raíz es el tiempo real que tardó tu consulta.
Si un nodo intermedio tiene un actual time muy alto –> ahí está casi seguro tu problema.

2. Filas estimadas vs. filas reales (Rows vs actual rows)

Ejemplo clásico de alarma:

Rows=10000  (estimated)
Actual rows=12

El planner pensó que ibas a sacar 10.000 filas… y solo salieron 12. Cuando las estimaciones fallan tanto, el plan que elige suele ser pésimo.

Causas frecuentes:

• Estadísticas desactualizadas –> ANALYZE tabla;
• Datos muy sesgados (90% de los valores son “activo”, pero filtras “inactivo”)
• Correlaciones entre columnas que el planner no ve
• Funciones en el WHERE que impiden buenas estimaciones

Muchas veces un simple ANALYZE arregla el desastre. Otras veces necesitas índices parciales, estadísticas extendidas o reescribir el JOIN.

3. Buffers – usa siempre EXPLAIN (ANALYZE, BUFFERS)

Te dice si el cuello de botella es I/O o CPU:

• shared hit –> la página ya estaba en memoria (rápido)
• shared read –> tuvo que leer del disco (lento)
• temp read/write –> derramó a disco (sorts o hash joins muy grandes)

Patrones típicos:

• Mucho shared read –> problema de I/O –> quizás falta índice o la tabla no cabe en memoria
• Casi todo hit y sigue lento –> CPU, mala estrategia de join, o funciones caras en cada fila

4. Loops – el multiplicador silencioso

Actual rows=1  Loops=45000

Esto significa que ese nodo se ejecutó 45.000 veces. Casi siempre es señal de un Nested Loop mal elegido (el lado externo produce muchas filas y el interno no tiene índice rápido).

Los tres sospechosos de siempre

Seq Scan

Lee toda la tabla secuencialmente. No es malo por definición. Es la mejor opción si:

• Vas a leer gran parte de la tabla
• La tabla es pequeña
• Un índice sería más caro que leer todo

Es sospechoso cuando:

• Esperas pocas filas
• La tabla tiene millones de registros
• Hay un filtro muy selectivo sin índice –> ahí casi siempre es un error

Index Scan vs Bitmap Index Scan

• Index Scan –> perfecto para pocas filas. Va directo al grano.
• Bitmap Index Scan –> mejor cuando esperas muchas filas dispersas. Construye un mapa en memoria y luego lee bloques completos –> menos saltos aleatorios en disco.

El planner elige según cuántas filas cree que va a devolver.

Nested Loop

Muy común en JOINs pequeños.

Funciona genial cuando:

• El lado izquierdo (outer) devuelve pocas filas
• Hay índice bueno en la tabla derecha (inner)

Se vuelve pesadilla cuando:

• El lado outer devuelve miles/millones de filas
• No hay índice en el inner –> se convierte en miles de Seq Scan
• Loops altísimos

Ejemplo real (de los que duelen)

Antes – consulta lenta

Seq Scan on transacciones  
  (actual time=10.542..120.181 rows=50 loops=1)
  Filter: (monto > 5000)
  Rows Removed by Filter: 999950

Qué pasó:

• Leyó ~1 millón de filas
• Descartó 999.950
• Solo 50 eran útiles
• Muchísimo I/O inútil

Después – con índice en monto

Index Scan using idx_transacciones_monto on transacciones  
  (actual time=0.048..0.117 rows=50 loops=1)

Resultado: de ~120 ms –> 0.12 ms

De buscar una aguja en un pajar –> ir directo a la aguja.

EXPLAIN ANALYZE no opina, no especula, no te vende humo: te muestra lo que de verdad ocurrió.

Cuando una consulta va lenta, la respuesta casi siempre está en uno (o varios) de estos:

• Diferencia grande entre filas estimadas y reales
• Nodos con actual time muy alto en la mitad del árbol
• Muchos shared read o temp write
• Loops ridículamente altos

Antes de salir corriendo a crear índices «por si acaso», mira el plan.

Muchas veces el problema no es falta de índice, sino:

• Estadísticas desactualizadas
• JOINs mal ordenados
• Estimaciones de cardinalidad rotas
• Funciones en columnas que impiden usar índices
• Filtros escritos de forma que confunden al planner

Optimizar a ciegas es tirar dados. Optimizar con EXPLAIN ANALYZE es ingeniería de verdad.

¿Cuál fue la última consulta lenta que tuviste? ¿Ya le pasaste EXPLAIN (ANALYZE, BUFFERS)?

Pruébalo hoy mismo en esa query que te tiene pensando como mejorarla… te sorprenderá lo que descubres. Sigamos codificando.