Leer un plan
Un plan es un árbol de operaciones; la raíz devuelve el resultado y cada nodo extrae filas de sus hijos. EXPLAIN solo muestra las estimaciones del planificador (unidades de coste y filas esperadas); EXPLAIN ANALYZE ejecuta realmente la consulta y añade tiempos y recuentos de filas reales, que es lo que hace legible un plan. BUFFERS añade cuántas páginas de 8 kB vinieron de la caché (hit) o del disco (read). El camino habitual por un plan: encontrar el nodo con mayor tiempo exclusivo y luego ver por qué está ahí: una estimación de filas errónea por encima, un índice que falta o un ajuste de memoria.
Los números de un nodo
| Campo | Significado |
|---|---|
| cost=0.29..8.31 | Estimación del planificador en unidades arbitrarias: coste de devolver la primera fila .. coste de devolver todas. Solo comparable dentro de un mismo plan. |
| rows= | Filas estimadas por loop. La contrapartida real también es por loop; multiplica por loops para el total. |
| actual time=0.05..12.3 | Milisegundos hasta la primera fila .. última fila, por loop, incluidos los hijos. La herramienta resta los hijos para mostrar el tiempo propio de cada nodo. |
| loops= | Cuántas veces se ejecutó el nodo. El lado interno de un nested loop se ejecuta una vez por fila externa; bajo un Gather es igual al número de workers. |
| Buffers: shared hit=.. read=.. | Páginas encontradas en shared_buffers frente a leídas del SO/disco. Las lecturas son adonde va el tiempo de E/S; track_io_timing lo muestra como I/O Timings. |
| Rows Removed by Filter | Filas que el nodo leyó y luego descartó: la señal más clara de que falta un índice. |
Los cinco problemas habituales
- Estimaciones erróneas. Si un nodo espera 10 filas y recibe 100.000, el planificador eligió un nested loop o un sort que solo tenía sentido para 10. Ejecuta
ANALYZE; para predicados sobre columnas correlacionadas crea estadísticas extendidas; con CTE y funciones el planificador no tiene ni idea y recurre a suposiciones. - Sequential scan con un filtro selectivo. Bien en tablas pequeñas. En las grandes, la solución es un índice sobre las columnas filtradas, idealmente que cubra el WHERE y el ORDER BY.
- Spills. Sort external merge, Hash Batches > 1, HashAggregate en disco, bitmaps lossy: la operación no cupo en
work_mem. Subirlo para la sesión o la consulta es barato; subirlo globalmente se multiplica por las conexiones. - Nested loop sobre un scan. El lado interno se ejecuta una vez por fila externa; si es un Seq Scan o un Materialize de un conjunto grande, el tiempo explota. El lado interno necesita un índice, o hay que permitir al planificador un hash join.
- Index-only scans que no lo son. Heap Fetches cercano al número de filas significa que el visibility map está desactualizado: VACUUM de la tabla.
Obtener planes
-- PostgreSQL: la forma más útil
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT …;
-- para escrituras, dentro de una transacción que deshaces
BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE …; ROLLBACK;
-- registrar planes lentos automáticamente: auto_explain
LOAD 'auto_explain'; SET auto_explain.log_min_duration = '200ms'; SET auto_explain.log_analyze = on;
-- MySQL 8.0.18+
EXPLAIN ANALYZE SELECT …; -- árbol con tiempos reales
EXPLAIN FORMAT=JSON SELECT …; -- estimaciones con detalles de costePreguntas
¿Por qué los tiempos de los nodos no suman el total?
El tiempo real de cada nodo incluye a sus hijos, así que la herramienta muestra el tiempo exclusivo (nodo menos hijos). Los workers paralelos se ejecutan a la vez, por lo que un nodo bajo Gather con loops=3 se cuenta una vez, no tres; los lados internos de los nested loops se multiplican por loops. El tiempo de planificación y el de triggers se listan aparte.
¿Qué es un buen plan?
Uno en el que las estimaciones están dentro de un factor diez de los valores reales, ningún nodo vuelca a disco, el nodo más caro es el que esperas (la tabla grande que de verdad tienes que leer) y los buffers leídos del disco son pocos comparados con los hits. Para un plan así, la lista de hallazgos está vacía.
¿Admite tablas particionadas y planes paralelos?
Sí: Append / Merge Append sobre particiones, Gather y Gather Merge con Workers Planned/Launched, agregados Partial y Finalize. Los desgloses por worker (VERBOSE) se analizan pero se muestran como parte del nodo.
¿Puedo pegar un plan de pgAdmin, DataGrip o psql con bordes de tabla?
Sí. Las líneas se limpian de los caracteres de caja de psql, de la cabecera QUERY PLAN y del pie «(n rows)». El JSON copiado de una GUI como una sola cadena entrecomillada debe desentrecomillarse primero.
¿Cómo es el soporte de MySQL?
El árbol EXPLAIN ANALYZE de MySQL 8 tiene la misma forma que un plan de texto de PostgreSQL y está totalmente soportado; EXPLAIN FORMAT=JSON (solo estimaciones) se mapea sobre el mismo árbol: los tipos de acceso se convierten en tipos de scan, nested_loop en un nodo join y los indicadores de filesort / tabla temporal en hallazgos. ANALYZE FORMAT=JSON de MariaDB aún no está soportado.