¿Qué es EXPLAIN y cómo analizar consultas con él?

Imagina que estás desarrollando una app y de repente una de tus consultas se vuelve la más costosa en tu base de datos. Empiezas a notar fallos y lentitud en la app. Aquí es donde entra en juego EXPLAIN, que te ayuda a entender dónde se fue todo al garete. Optimizar consultas usando el análisis de EXPLAIN te permite ahorrar recursos, ganar tiempo y mejorar la experiencia de tus usuarios.

EXPLAIN es tu forma de mirar dentro de PostgreSQL y ver exactamente cómo la base de datos planea ejecutar la consulta. Te muestra si se va a usar un índice o si todo se reduce a un escaneo completo de la tabla, qué pasos va a seguir el optimizador, en qué orden y cuán grandes serán los resultados intermedios.

En otras palabras, EXPLAIN te deja entender qué esperar de la ejecución de la consulta: cuán "pesada" es, cuántas filas se espera procesar y qué recursos se van a usar. Es una herramienta imprescindible cuando una consulta empieza a ir lenta y necesitas averiguar el porqué.

EXPLAIN es como una linterna en la oscuridad: con ella ves qué pasa bajo el capó y dónde exactamente está el problema.

Sintaxis del comando EXPLAIN

Vamos a ver la sintaxis básica del comando EXPLAIN:

EXPLAIN tu_consulta_SQL;

Ejemplo de consulta:

EXPLAIN SELECT * FROM students WHERE age > 20;

Cuando ejecutas este comando, PostgreSQL no ejecuta la consulta. En vez de eso, te muestra el plan de ejecución. Es como un boceto antes de construir algo — útil para ver qué se va a hacer antes de romper nada.

Aquí tienes un ejemplo de salida:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Esta salida puede parecer intimidante, pero tranqui — ahora vamos a ver los componentes principales.

Análisis de un plan de ejecución básico

Vamos a analizar ese resultado:

1. Seq Scan on students — esto significa que PostgreSQL va a escanear la tabla students entera (escaneo secuencial). No siempre es malo, pero en tablas grandes Seq Scan puede ser lento.

2. cost=0.00..35.00 — esto es la estimación del coste de la operación:

   • Startup Cost: coste inicial de la operación (en este caso 0.00).
   • Total Cost: coste total para terminar la operación (aquí 35.00).

3. rows=7 — PostgreSQL estima que la condición age > 20 devolverá 7 filas. Esto se llama "cardinalidad". Si ves estimaciones raras, puede ser señal de que las estadísticas de tu tabla están desactualizadas.

4. width=37 — esto es el tamaño medio de una fila en bytes.

5. Filter: (age > 20) — aclara que PostgreSQL aplicará el filtro comprobando cada fila.

Así, la salida de EXPLAIN te da una idea de las estrategias y suposiciones de PostgreSQL. Puedes usar esta info para optimizar.

Opciones del comando EXPLAIN

Aunque la salida básica de EXPLAIN ya es útil, puedes modificarla con estas opciones:

ANALYZE

Con esta opción, PostgreSQL no solo muestra el plan de ejecución, sino que también ejecuta la consulta y te da datos reales. Ejemplo:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Esto te permite comparar las suposiciones de PostgreSQL con la ejecución real, para ver si coinciden.

VERBOSE

Muestra más detalles, útil para un análisis profundo. Ejemplo:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Muestra el uso de buffers de memoria durante la ejecución de la consulta. Se usa junto con ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Si quieres ocultar o mostrar la info de costes (cost), usa esta opción:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

El plan puede mostrarse en otros formatos, como JSON o XML. Ejemplo:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Ejemplo de uso de EXPLAIN

Vamos a ver una base de datos university con la tabla students. Supón que quieres encontrar todos los estudiantes mayores de 20 años:

EXPLAIN SELECT * FROM students WHERE age > 20;

La salida puede ser así:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Como ya dijimos, esto es un escaneo secuencial Seq Scan, que puede no ser eficiente en tablas grandes.

Ahora vamos a crear un índice en la columna age y ver si el plan cambia:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Salida:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Ahora PostgreSQL usa un escaneo por índice (Index Scan), que suele ser más rápido, sobre todo en tablas grandes.

Preguntas y errores típicos

¿Por qué mi consulta va lenta aunque tenga un índice?

Puede que la consulta devuelva demasiadas filas, lo que hace que usar el índice no sea tan ventajoso. El índice puede ser de mala calidad o estar desactualizado.

¿Y si la salida de EXPLAIN es difícil de entender?

Empieza con consultas simples y estudia los nodos del plan de ejecución uno a uno.

¿Cómo saber si las estadísticas de la tabla están desactualizadas?

Ejecuta el comando ANALYZE students

¿Cuándo usar EXPLAIN sin ANALYZE?

Si solo quieres ver el plan sin ejecutar de verdad la consulta (por ejemplo, para consultas que modifican datos).