Leer un plan EXPLAIN

Cuando llega a una entrevista para un puesto sénior, los entrevistadores dejan de preguntar escriba una consulta y empiezan a preguntar por qué esta consulta es lenta. La herramienta que responde a esa pregunta es EXPLAIN.

EXPLAIN muestra el plan de ejecución de la base de datos: la estrategia paso a paso que el planificador eligió para ejecutar su SQL. Revela qué tablas se recorren, en qué orden se combinan y cuál es aproximadamente el coste de cada paso.

Saber leer un plan demuestra que entiende el motor, no solo la sintaxis. Esa es precisamente la diferencia que los entrevistadores utilizan para distinguir a un perfil intermedio de uno sénior.

Hay dos variantes, y a los entrevistadores les encanta comprobar que conoce la diferencia.

• EXPLAIN muestra el plan estimado por el planificador sin ejecutar la consulta. Es rápido y seguro.
• EXPLAIN ANALYZE ejecuta realmente la consulta e informa de los recuentos de filas y los tiempos reales junto con las estimaciones.

La información más valiosa está en comparar las filas estimadas con las filas reales. Una gran discrepancia indica que el planificador dispone de estadísticas deficientes y probablemente está tomando una mala decisión.

Precaución: EXPLAIN ANALYZE ejecuta realmente la consulta, por lo que realizará cualquier INSERT o UPDATE, a menos que se ejecute dentro de una transacción revertida.

Un plan es un árbol, no una lista. Los nodos con mayor sangría son las hojas que se ejecutan primero; los resultados ascienden hasta la raíz, que produce la salida final.

Léalo desde el interior hacia fuera: busque el nodo más profundo; ahí comienza la ejecución. Cada nodo padre consume las filas que emiten sus hijos.

En una entrevista, explíquelo de esa manera: primero recorremos esta tabla, esas filas pasan a esta unión, la unión pasa a la ordenación y la ordenación pasa al límite. Eso es lo que quieren escuchar: una explicación de abajo arriba.

Cada nodo de un plan de Postgres contiene los mismos números clave:

• cost=0.00..35.50 coste de inicio..coste total en unidades arbitrarias del planificador
• rows=1000 número estimado de filas producidas
• width=64 tamaño medio estimado de cada fila, en bytes

El primer coste es el coste de inicio (el trabajo que se realiza antes de que aparezca la primera fila, como construir una tabla hash). El segundo es el coste total de devolver todas las filas. Un coste total más alto representa una mayor estimación del coste relativo para el planificador.

Considere una consulta sencilla con un filtro. El plan siguiente cuenta toda la historia en una sola línea.

Es un Seq Scan (lectura de la tabla completa) sobre orders, que aplica el filtro status = 'shipped'. El planificador estima que hay 1000 filas coincidentes.

Si orders tiene 10 millones de filas y solo 1000 coinciden, en una entrevista se espera que diga: un escaneo secuencial aquí es ineficiente; un índice sobre status (o sobre una columna más selectiva) nos permitiría evitar leer toda la tabla.

Con EXPLAIN ANALYZE también obtiene valores reales entre paréntesis.

Observe el ejemplo: el planificador estimó 1000 filas, pero en realidad obtuvo 480000. Es una subestimación de 480 veces. El planificador eligió su estrategia suponiendo que habría pocas filas, por lo que probablemente su elección no sea adecuada para los datos reales.

En las entrevistas, esta diferencia es su diagnóstico principal: las estadísticas están desactualizadas; ejecute ANALYZE en la tabla y, después, es probable que el planificador elija un plan mejor.

El valor de loops importa más de lo que esperan muchos candidatos. Es el número de veces que se ejecutó un nodo.

Este valor aparece en el lado interno de una combinación de bucles anidados: el nodo interno se ejecuta una vez por cada fila externa. Si loops=480000, ese paso interno se ejecutó 480 mil veces.

Importante: el tiempo por fila y el número de filas que se muestran son valores por bucle. Para obtener el total real, debe multiplicarlos por loops. Un nodo que parece barato, con 0.004 ms por bucle, se convierte en casi 2 segundos a lo largo de 480000 bucles.

Una trampa frecuente: los candidatos leen cost=18334 y dicen eso tarda 18 segundos. Es incorrecto.

El coste se expresa en unidades arbitrarias del planificador, calibradas de modo que una lectura secuencial de una página equivale a 1.0. Solo resulta útil para comparar planes entre sí, no como una medida de tiempo de reloj.

Para obtener tiempos reales necesita EXPLAIN ANALYZE y sus valores de actual time, medidos en milisegundos. Dígalo claramente en una entrevista; demuestra que realmente comprende la métrica.

Aquí tiene un plan para dos tablas. Léalo de abajo arriba.

Los dos primeros escaneos recopilan filas de orders y customers. Estas alimentan una Hash Join: se aplica un hash a un lado y el otro consulta la tabla hash. El resultado de la combinación alimenta después el resultado final.

Observe que la sangría muestra la estructura: ambos escaneos aparecen bajo el Hash Join. El entrevistador quiere que identifique el método de combinación (hash, en este caso) y qué tabla se utiliza para construir el hash (normalmente, la más pequeña).

Entrene la vista para detectar estas señales de advertencia en cualquier plan:

• Seq Scan sobre una tabla enorme con un filtro selectivo; un índice podría ayudar.
• Las filas estimadas distan mucho de las reales; las estadísticas están desactualizadas.
• Nested Loop con un número elevado de bucles sobre una tabla grande; a menudo falta un índice sobre la clave de combinación interna.
• Sort o Hash desbordándose al disco (se muestra como uso de Disk); work_mem es demasiado pequeño.
• Rows Removed by Filter muy elevado; se ha leído y descartado la mayor parte de la tabla.

Los planes están disponibles en varios formatos. El formato predeterminado, TEXT, es el que se lee en voz alta en las entrevistas. Sin embargo, también puede solicitar una salida estructurada.

EXPLAIN (FORMAT JSON) o FORMAT YAML producen planes legibles por máquinas que las herramientas y los paneles pueden analizar. Rara vez tendrá que leerlos manualmente, pero saber que existen es un buen detalle propio de un perfil sénior.

Añada las opciones entre paréntesis: EXPLAIN (ANALYZE, BUFFERS). La opción BUFFERS informa de los aciertos de caché frente a las lecturas de disco, lo que resulta muy útil para diagnosticar consultas limitadas por la E/S.

Un entrevistador le muestra un nodo de EXPLAIN ANALYZE con rows=1000 en la sección de costes, pero con actual ... rows=480000. ¿Cuál es el diagnóstico más probable?

Ahora ya puede leer un plan como un profesional sénior:

• EXPLAIN realiza estimaciones; EXPLAIN ANALYZE ejecuta y mide.
• Lea el árbol de abajo arriba; las hojas se ejecutan primero y la raíz produce la salida.
• Cada nodo muestra el coste (en unidades relativas), las filas y el ancho; actual time es el valor real en milisegundos.
• loops multiplica los valores por bucle; preste atención a los bucles anidados.
• La diferencia entre las filas estimadas y las reales es su señal de diagnóstico principal.

Describa el plan en voz alta y señale las señales de alerta; ese es el comportamiento que permite destacar en una entrevista.