Cómo funciona EXPLAIN
EXPLAIN es la ventana de PostgreSQL a la toma de decisiones del planificador de consultas, mostrando el plan que eligió sin necesariamente ejecutarlo.
Cada sentencia SQL que envías a PostgreSQL pasa por un planificador que considera múltiples formas de ejecutarla y elige la que cree que es la más barata, y EXPLAIN es la forma de inspeccionar esa elección en lugar de adivinarla.
Comprender cómo funciona EXPLAIN conceptualmente, no solo qué indicadores pasar, es lo que te permite leer un plan y saber inmediatamente si el planificador razonó correctamente sobre tus datos.
Resumen
EXPLAINimprime el árbol de planes que el planificador de consultas seleccionó, junto con costes estimados o, conANALYZE, comportamiento medido real.- Por qué Importa: Los problemas de rendimiento de consultas son casi siempre problemas de planes, y el plan es invisible hasta que lo solicitas.
- Conceptos Clave: árbol de planes, estimación de coste, nodo de plan, estimación de filas, tiempo real, planificador vs. ejecutor.
- Cuándo Usar: Diagnosticar una consulta lenta, validar que un índice se utiliza realmente, comparar dos formas de consulta o investigar una regresión después de un cambio en los datos o las estadísticas.
- Limitaciones / Compensaciones:
EXPLAINmuestra un único plan elegido, no todos los planes que el optimizador consideró, yANALYZEcambia las características de tiempo al añadir una sobrecarga de instrumentación. - Temas Relacionados: estadísticas del planificador de consultas, selección de escaneo de índices, algoritmos de unión, caché de planes.
Fundamentos
En esencia, EXPLAIN pide al planificador que haga todo lo que normalmente hace, excepto omitir el paso final de ejecución.
El planificador analiza tu consulta, considera las rutas de acceso disponibles (escaneos secuenciales, escaneos de índices, varios órdenes de unión), asigna un coste numérico a cada una y devuelve la combinación más barata como un árbol de nodos.
Ese árbol es el "plan", y cada nodo en él representa una operación, como escanear una tabla, unir dos conjuntos de filas o ordenar un resultado.
Un nodo en la parte inferior del árbol alimenta filas hacia arriba al nodo que está encima de él, de forma similar a una pequeña línea de montaje de fábrica donde cada estación solo conoce su salida inmediata.
Cuando ejecutas EXPLAIN simple, PostgreSQL imprime este árbol junto con el coste estimado y el recuento de filas estimado para cada nodo, pero nunca ejecuta tu sentencia.
Esa distinción importa enormemente en la práctica, porque significa que EXPLAIN solo es seguro de ejecutar en producción, incluso para un DELETE o UPDATE.
EXPLAIN ANALYZE, por el contrario, sí ejecuta la sentencia, recorre el plan real y registra tiempos y recuentos de filas reales junto a las estimaciones originales.
Esta es la razón por la que EXPLAIN ANALYZE en una sentencia de escritura realmente realiza la escritura, que es la forma más común en que las personas mutan datos accidentalmente mientras "solo comprueban un plan".
Los dos números estimados que ves en cada nodo, coste y filas, provienen del modelo de costes del planificador, una fórmula que combina costes de acceso a páginas, CPU por fila y las estadísticas conocidas de la tabla.
Esos costes son números sin unidades calibrados contra seq_page_cost (la línea base), no milisegundos, por lo que un coste de 1000 no es "1000 de nada real", solo es significativo en comparación con el coste de un plan competidor.
Mecánica e Interacciones
El planificador no evalúa todos los planes concebibles para una consulta; poda el espacio de búsqueda utilizando heurísticas y programación dinámica sobre el orden de unión, especialmente a medida que crece el número de tablas unidas.
Para cada ruta de acceso candidata, consulta las estadísticas de la tabla, almacenadas en pg_stats, para estimar cuántas filas sobrevivirán a una cláusula WHERE dada, y esa estimación de filas luego alimenta todos los cálculos de costes por encima de ella en el árbol.
Este es el acoplamiento crítico que conecta EXPLAIN con la sección de estadísticas del planificador de este sitio: una estimación de filas incorrecta en la parte inferior del árbol se propaga hacia arriba y puede producir una forma de plan catastróficamente incorrecta varias uniones más tarde.
Cada tipo de nodo de plan tiene su propia fórmula de coste; un escaneo secuencial cuesta aproximadamente el número de páginas por seq_page_cost más filas por cpu_tuple_cost, mientras que un escaneo de índice añade una penalización de aleatoriedad por tupla a través de random_page_cost para las lecturas de página no secuenciales.
Cuando añades ANALYZE, el ejecutor instrumenta cada nodo con un temporizador y un contador de filas, por lo que lo que ves como actual time=0.012..4.531 es la ventana de tiempo de reloj medida durante la cual se ejecutó ese nodo específico, incluido el tiempo dedicado a sus hijos.
Esa contabilidad de padre-incluye-hijo es una fuente frecuente de mala interpretación: el límite superior del actual time de un nodo parece alarmante hasta que te das cuenta de que la mayor parte pertenece a un nodo hijo debajo de él, no al trabajo propio de ese nodo.
BUFFERS añade un segundo canal de instrumentación independiente que cuenta las páginas tocadas en los buffers compartidos frente a las páginas que tuvieron que leerse del sistema operativo o del disco, lo que es una señal mucho más estable entre ejecuciones repetidas que el tiempo solo.
La instrumentación de tiempos en sí misma no es gratuita; en consultas muy rápidas y de alta frecuencia, la sobrecarga de llamar al reloj del sistema millones de veces puede inflar visiblemente los números de ANALYZE en relación con el coste real no medido de la consulta.
Aquí es también donde la caché de planes interactúa con lo que observas: un plan genérico construido para una sentencia preparada puede diferir del plan único construido para un valor literal, porque el planificador a veces elige un plan personalizado por ejecución y solo recurre a un plan genérico después de suficientes repeticiones.
Los nodos de consulta paralela complican aún más la contabilidad, ya que Workers Launched y actual time por trabajador se informan por separado, y el proceso líder también puede realizar una parte del trabajo.
-- El patrón de dos números que lees en cada nodo: estimación vs. real
-- rows=<suposición del planificador> actual rows=<lo que realmente salió>
-- Una gran brecha aquí es la señal más fiable de un problema de estadísticas
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'shipped';
-- ^ la estimación alimenta cada unión por encima de este nodoConsideraciones Avanzadas y Aplicaciones
A escala, el coste de ejecutar EXPLAIN ANALYZE se convierte en una consideración de diseño, ya que instrumentar una consulta que escanea cientos de millones de filas puede añadir una sobrecarga significativa además del tiempo de ejecución de la consulta.
auto_explain aborda esto permitiendo que el servidor registre planes automáticamente para consultas que superan un umbral de duración, sin que tengas que capturar una consulta lenta interactivamente.
La compilación JIT, habilitada por defecto para planes suficientemente costosos, añade sus propias líneas de salida a EXPLAIN, y un plan que parece barato en tiempo de ejecución bruto aún puede mostrar una sobrecarga de compilación JIT no trivial para consultas simples y ejecutadas con frecuencia.
La estabilidad del plan entre versiones de PostgreSQL no está garantizada, ya que las mejoras del planificador entre versiones principales pueden cambiar las elecciones de nodos, las constantes de coste o incluso introducir nuevos tipos de nodos, por lo que un plan base capturado siempre debe fijarse a una versión específica cuando se utiliza para comparación de regresiones.
El estado de la caché es otra trampa avanzada: el primer EXPLAIN ANALYZE después de un reinicio del servidor o un gran cambio de datos lee principalmente del disco, mientras que una segunda ejecución contra la misma consulta se beneficia de una caché de buffers compartidos caliente, y comparar una ejecución fría con una caliente produce conclusiones engañosas sobre la latencia real de una consulta.
Las canalizaciones de observabilidad de producción suelen combinar tres herramientas complementarias en lugar de depender únicamente de EXPLAIN interactivo, ya que cada una responde a una pregunta diferente sobre el comportamiento del plan a lo largo del tiempo.
| Herramienta | Fortaleza | Debilidad | Mejor Ajuste |
|---|---|---|---|
EXPLAIN (sin ANALYZE) | Cero riesgo de ejecución, siempre seguro en producción | Sin tiempos reales ni verdad de filas | Comprobación rápida de cordura de una elección de ruta de acceso |
EXPLAIN (ANALYZE, BUFFERS) | Tiempos reales, recuentos de filas y E/S por nodo | Ejecuta la consulta; añade sobrecarga de instrumentación | Diagnóstico profundo y único de una consulta lenta específica |
auto_explain | Captura planes automáticamente por encima de un umbral de duración | Añade volumen de registro; necesita acceso a la configuración del servidor | Capturar consultas lentas intermitentes que no puedes reproducir bajo demanda |
pg_stat_statements | Coste agregado y recuentos de llamadas en todas las consultas | Sin árbol de planes ni detalle por nodo | Encontrar qué texto de consulta merece un EXPLAIN detallado |
Conceptos Erróneos Comunes
- "Un número de coste más alto significa que la consulta es lenta." - El coste es una estimación relativa y sin unidades del planificador calibrada para la comparación de planes, no una predicción de milisegundos.
- "EXPLAIN siempre ejecuta mi consulta." -
EXPLAINsimple nunca ejecuta nada; soloEXPLAIN ANALYZElo hace, y esa distinción es crítica para la seguridad en sentencias de escritura. - "El plan que veo hoy es el plan que siempre se ejecutará." - Los planes se reconstruyen por ejecución (o por umbral de plan genérico para sentencias preparadas) y pueden cambiar a medida que cambian las estadísticas, los datos o la configuración.
- "El tiempo real de un nodo es todo su propio trabajo." - El
actual timeinformado en cualquier nodo incluye todo el tiempo dedicado dentro de sus nodos hijos, no solo el trabajo propio de ese nodo. - "Los números de EXPLAIN ANALYZE son el coste real no medido de la consulta." - La instrumentación de tiempos y buffers añade su propia sobrecarga, que suele ser pequeña pero no cero, especialmente en consultas muy rápidas.
- "Un nodo superior de aspecto barato significa que toda la consulta es rápida." - El trabajo más costoso a menudo se esconde varios niveles por debajo dentro de un bucle anidado o una subconsulta repetida, no en la parte superior del árbol.
Preguntas Frecuentes
¿Cuál es la diferencia real entre EXPLAIN y EXPLAIN ANALYZE?
EXPLAIN solo muestra el plan elegido por el planificador y sus estimaciones de coste/filas sin ejecutar la consulta.
EXPLAIN ANALYZE ejecuta la sentencia y añade tiempos y recuentos de filas reales y medidos a cada nodo de ese mismo árbol de planes.
¿Es alguna vez inseguro ejecutar EXPLAIN simple en una base de datos de producción?
No, EXPLAIN simple nunca ejecuta la sentencia subyacente, por lo que no conlleva ningún riesgo de mutar datos o mantener bloqueos de larga duración por ejecución.
El único coste real es el tiempo de planificación (generalmente trivial) en sí.
¿Por qué los recuentos de filas estimados y reales en un nodo a veces difieren drásticamente?
La estimación proviene del modelo de costes basado en estadísticas del planificador, que puede ser incorrecto debido a estadísticas obsoletas, columnas correlacionadas o distribuciones de datos sesgadas.
Una gran brecha entre las filas estimadas y reales es la señal individual más fuerte de que las suposiciones del planificador sobre tus datos ya no son válidas.
¿Qué representan realmente los números de coste como cost=0.29..8.31?
Son el coste de inicio y el coste total estimados por el planificador en términos abstractos y sin unidades, calibrados contra seq_page_cost.
Solo son significativos al comparar dos planes candidatos entre sí, no como una predicción del tiempo de reloj.
¿Por qué el tiempo real de un nodo parece mayor de lo esperado para una operación tan simple?
El actual time informado en cualquier nodo incluye todo el tiempo dedicado dentro de sus nodos hijos, no solo el trabajo propio de ese nodo.
Lee el árbol de abajo hacia arriba para encontrar dónde se está gastando el tiempo realmente.
¿Añadir BUFFERS a EXPLAIN ralentiza aún más la consulta?
La contabilidad de buffers añade una cantidad de sobrecarga pequeña, generalmente insignificante, en comparación con la instrumentación de tiempos de ANALYZE.
Normalmente vale la pena incluirlo siempre que uses ANALYZE, ya que distingue los aciertos de caché de las lecturas reales del disco.
¿Puede la misma consulta producir dos planes diferentes en dos ejecuciones diferentes?
Sí, si utiliza una sentencia preparada, el planificador puede alternar entre un plan personalizado creado para valores de parámetros específicos y un plan genérico creado sin ellos.
Los planes también pueden cambiar legítimamente si las estadísticas, el tamaño de la tabla o la configuración cambian entre ejecuciones.
¿Por qué el primer EXPLAIN ANALYZE después de un reinicio parece mucho peor?
Una caché de buffers compartidos fría obliga a leer más páginas del disco o de la caché del sistema operativo en lugar de servirlas desde la memoria de PostgreSQL.
Ejecuta la misma consulta una segunda vez antes de sacar conclusiones sobre el rendimiento en estado estable.
¿EXPLAIN me informa sobre esperas de bloqueo o contención?
No, la salida de EXPLAIN describe la forma y el tiempo del plan, no las esperas de concurrencia de otras sesiones.
Consulta pg_stat_activity y su columna wait_event_type para retrasos relacionados con bloqueos por separado.
¿Qué está haciendo JIT en la salida de mi EXPLAIN?
La compilación Just-In-Time genera código nativo para expresiones y puede aparecer como su propia línea de tiempo cuando el plan es lo suficientemente costoso como para activarla.
Es una parte real del tiempo total de ejecución y vale la pena notarlo en consultas que se ejecutan con mucha frecuencia pero que individualmente siguen siendo baratas.
¿Por qué un plan que funcionó bien durante meses de repente se vuelve lento?
La distribución subyacente de los datos probablemente cambió lo suficiente como para que las estadísticas cacheadas del planificador ya no describan la realidad con precisión.
Un ANALYZE fresco, o en casos más complejos estadísticas extendidas, generalmente restaura un plan sensato.
¿Es un Seq Scan siempre un signo de un índice faltante?
No necesariamente, ya que un escaneo secuencial puede ser la opción genuinamente más barata cuando una consulta toca una gran fracción de una tabla pequeña o de tamaño moderado.
Si es un problema depende del tamaño de la tabla y de la selectividad de la consulta, no solo del nombre del nodo.
¿Cómo se diferencia la salida de EXPLAIN en formato JSON del formato TEXT?
Ambos describen el árbol de planes idéntico y los números idénticos, pero JSON está estructurado para herramientas mientras que TEXT está formateado para que los humanos lean en una terminal o ticket.
Las herramientas de comparación de planes y visualización generalmente esperan JSON como entrada.
Relacionados
- Conceptos Básicos de EXPLAIN - la receta práctica para ejecutar EXPLAIN con indicadores comunes
- Seq Scan vs Index Scan - cómo el planificador elige entre estas dos rutas de acceso
- Nested Loop, Hash Join, Merge Join - los tipos de nodos de unión que verás en un árbol de planes
- Estudios de Caso de Planes Malos - ejemplos reales de desajustes entre estimación y realidad
- Conceptos Básicos de Estadísticas - por qué las estimaciones de filas que alimentan EXPLAIN a veces son incorrectas
Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (línea estable 18, línea de mantenimiento 17); la mecánica general de EXPLAIN y el planificador descritos aquí son estables en versiones principales recientes.