Leer planes de ejecución — EXPLAIN QUERY PLAN

El dataset de este artículo — perf_sales (50 000 filas de ventas)

Cuando quieres saber si una consulta es rápida o lenta, lo primero que hay que mirar es el plan de ejecución (el query plan).

Dada una sentencia SQL, la base de datos decide internamente "qué tablas leer, en qué orden y cómo".

La herramienta que imprime ese procedimiento como texto es EXPLAIN QUERY PLAN.

Antepón esta palabra clave a una consulta y verás los pasos de ejecución en lugar de los resultados reales.

Antes de meterte en los ejercicios, revisa las definiciones de columna y una muestra de los datos de la tabla perf_sales que se usa a lo largo de este artículo. Generar los datos lleva un momento, así que la primera ejecución puede tardar unos segundos.

① Usa PRAGMA table_info(perf_sales); para ver los nombres de columna, los tipos y la clave primaria.

② Usa SELECT * FROM perf_sales LIMIT 5; para previsualizar las 5 primeras filas.

③ Usa SELECT COUNT(*) FROM perf_sales; para confirmar que hay 50 000 filas.

SCAN y SEARCH — barridos completos y búsquedas por índice

Las dos primeras palabras que hay que aprender en un plan de ejecución son SCAN y SEARCH.

SCAN perf_sales significa leer cada fila desde el principio hasta el final de la tabla (un barrido completo).

Cuando filtras por una columna que no tiene índice, la base de datos no sabe qué filas coinciden, así que revisa las 50 000 filas una por una.

Mientras tanto, SEARCH perf_sales USING INDEX index_name (column=?) significa seguir el índice para aterrizar directamente sobre las filas coincidentes (una búsqueda por índice).

Crea un índice con CREATE INDEX y los filtros sobre esa columna dejan de recorrer cada fila — el plan cambia de SCAN a SEARCH ... USING INDEX.

-- Filtra por region: sin índice, así que lee cada fila
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales

-- Construye un índice sobre region y mira el mismo plan
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)

Imagina el escenario: "contar ventas de un comercial específico es lento". Vas a comparar el plan de ejecución antes y después de añadir un índice. Ejecuta la creación del índice y la revisión del plan juntas en una sola ejecución. (Ejecútalo correctamente para revelar la explicación.)

① Primero, antepón EXPLAIN QUERY PLAN a una consulta que cuente filas donde emp_id sea igual a 7 y revisa el plan. Sin índice, verás SCAN.

② Luego elimina cualquier índice existente con DROP INDEX IF EXISTS, construye un índice sobre emp_id con CREATE INDEX y vuelve a ejecutar el mismo EXPLAIN QUERY PLAN. Confirma que cambia a SEARCH ... USING INDEX.

USE TEMP B-TREE — un área temporal para ordenar

USE TEMP B-TREE FOR ORDER BY (un B-tree temporal) significa que la columna del ORDER BY no tiene índice, así que la base de datos está construyendo un área de trabajo temporal para ordenar los resultados.

Las 50 000 filas van a esa área temporal para reordenarse, así que el coste crece con el número de filas.

Si hay un índice utilizable para la ordenación, esta línea desaparece del plan y se usa directamente el orden ya existente del índice.

-- Top 10 por amount ascendente: sin índice, hace falta un área temporal para ordenar
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SCAN perf_sales
--> USE TEMP B-TREE FOR ORDER BY

-- Construye un índice sobre amount y la ordenación puede reusar su orden directamente
DROP INDEX IF EXISTS ix_amount;
CREATE INDEX ix_amount ON perf_sales(amount);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales ORDER BY amount LIMIT 10;
--> SEARCH perf_sales USING INDEX ix_amount
-- USE TEMP B-TREE desaparece del plan

Mira el plan de ejecución para "top 10 de ventas por amount ascendente" mientras no hay índice. Los tres ejercicios que siguen se ejecutan en orden para que puedas comparar los planes antes y después del índice.

① En esta primera consola, antepón EXPLAIN QUERY PLAN a una consulta que ordene perf_sales por amount ascendente y extraiga las 10 primeras filas (sale_id, amount).

Confirma que la salida contiene dos líneas: SCAN perf_sales y la relacionada con la ordenación USE TEMP B-TREE FOR ORDER BY. Sin un índice sobre la columna de ordenación amount, la base de datos tiene que cargar 50 000 filas en un área temporal y reordenarlas.

② En esta segunda consola, ejecuta DROP INDEX IF EXISTS ix_amount; para limpiar el anterior, luego crea un índice llamado ix_amount sobre la columna amount de perf_sales.

La creación del índice es un coste único, así que no lo incluimos en la comparación pura del plan. La tercera consola vuelve a ejecutar la revisión del plan para la misma consulta.

③ En esta tercera consola, vuelve a ejecutar el mismo EXPLAIN QUERY PLAN que en ①.

Confirma que la línea USE TEMP B-TREE FOR ORDER BY ha desaparecido del plan, reemplazada por algo como SEARCH perf_sales USING INDEX ix_amount. El orden del índice se reusa directamente, así que la ordenación en el área temporal ya no es necesaria.

USING COVERING INDEX — un índice que no toca la tabla

USING COVERING INDEX (un Index-Only Scan (índice cubriente), también llamado covering index en inglés) significa que cada columna que la consulta necesita está contenida dentro del índice, así que la base de datos puede construir el resultado solo a partir del índice sin leer la tabla en sí.

Un índice es una copia pequeña solo de las columnas que extraes de la tabla, así que saltarse la lectura de la tabla es lo que lo hace más rápido.

Es un paso más allá de SEARCH ... USING INDEX — cuando el plan muestra USING COVERING INDEX, estás obteniendo la lectura más eficiente.

-- Índice solo sobre emp_id -> encuentra filas vía el índice, luego lee amount de la tabla
DROP INDEX IF EXISTS ix_emp;
CREATE INDEX ix_emp ON perf_sales(emp_id);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING INDEX ix_emp (emp_id=?)
--   (también se lee la tabla)

-- Índice compuesto sobre (emp_id, amount) -> todas las columnas necesarias están en el índice, sin lectura de la tabla
DROP INDEX IF EXISTS ix_emp_amount;
CREATE INDEX ix_emp_amount ON perf_sales(emp_id, amount);
EXPLAIN QUERY PLAN
SELECT emp_id, amount FROM perf_sales WHERE emp_id = 7;
--> SEARCH perf_sales USING COVERING INDEX ix_emp_amount (emp_id=?)

Toma una consulta que "filtra por emp_id y devuelve solo amount" y cambia su plan a uno que termine dentro del índice sin tocar la tabla. Ejecuta la creación del índice y la revisión del plan juntas en una sola ejecución.

① Elimina cualquier índice existente con DROP INDEX IF EXISTS, luego usa CREATE INDEX para construir un índice compuesto sobre emp_id y amount, en ese orden.

② Antepón EXPLAIN QUERY PLAN a una consulta que filtre por emp_id = 7 y seleccione solo emp_id y amount, y revisa el plan. Lee que el plan termina solo dentro del índice, ya que cada columna necesaria vive en el índice.

Planes de subconsultas — CORRELATED y MATERIALIZE

Las consultas con subconsultas tienen su propio vocabulario dedicado en el plan.

CORRELATED SCALAR SUBQUERY significa que el valor de la subconsulta depende de la fila externa actual, así que se vuelve a ejecutar una vez por cada fila externa.

Con 50 000 filas externas, la subconsulta puede ejecutarse 50 000 veces — cuando esta palabra aparece en el plan, es candidata a una reescritura.

Mientras tanto, MATERIALIZE significa que el resultado de una subconsulta o CTE se calcula una vez, se guarda en una tabla temporal y se reutiliza.

Es una optimización que paga el coste de computación una vez para un resultado intermedio al que haces referencia varias veces.

Cuál de estas dos palabras aparece te dice si la subconsulta se está "computando cada vez" o si está "computada una vez y reutilizada".

-- Subconsulta escalar correlacionada: vuelve a obtener "el amount máximo de esta region" por cada fila externa
EXPLAIN QUERY PLAN
SELECT s.sale_id, s.amount
FROM perf_sales s
WHERE s.amount = (
  SELECT MAX(amount) FROM perf_sales x WHERE x.region = s.region
)
LIMIT 10;
--> aparece una línea CORRELATED SCALAR SUBQUERY

-- Una tabla derivada (subconsulta en la cláusula FROM) se computa una vez
EXPLAIN QUERY PLAN
SELECT t.region, t.s FROM (
  SELECT region, SUM(amount) AS s FROM perf_sales GROUP BY region
) t
WHERE t.s > 100000000;
--> puede aparecer una línea MATERIALIZE

Mira el plan para "para cada fila, elige solo ventas que superen el amount medio de ese comercial, top 10" y lee la estructura que vuelve a ejecutar la subconsulta por cada fila.

① Antepón EXPLAIN QUERY PLAN a una consulta que, para cada fila de perf_sales, use una subconsulta correlacionada para calcular "el amount medio para el mismo emp_id" y extraiga 10 filas donde amount lo supere.

② Lee, en la salida, la línea que indica una subconsulta escalar correlacionada y el SCAN perf_sales tanto para la consulta externa como para la interna. Ten en cuenta que esta es una forma pesada sobre tablas grandes porque la subconsulta interna se evalúa una vez por cada fila externa.

Cómo se mapea esto a otros RDBMS — MySQL / PostgreSQL / Oracle

El vocabulario de plan de este artículo viene del EXPLAIN QUERY PLAN de SQLite, pero MySQL / PostgreSQL / Oracle muestran los mismos conceptos con nombres distintos.

Empieza revisando cómo cada base de datos produce un plan de ejecución, luego compara la chuleta de la terminología de planes — podrás leer planes en otra base de datos sin mucho aprendizaje adicional.

Cómo imprimir un plan de ejecución en cada base de datos

• SQLite: antepón EXPLAIN QUERY PLAN a la consulta (p. ej. EXPLAIN QUERY PLAN SELECT ...;). No ejecuta la consulta, solo imprime el plan. El EXPLAIN pelado muestra el bytecode de bajo nivel, así que para lectura humana quieres EXPLAIN QUERY PLAN
• MySQL: antepón EXPLAIN a la consulta (p. ej. EXPLAIN SELECT ...;). MySQL 8.0+ también tiene EXPLAIN ANALYZE SELECT ...; para el tiempo real de ejecución y los recuentos reales de filas. Para salida JSON, usa EXPLAIN FORMAT=JSON SELECT ...;
• PostgreSQL: antepón EXPLAIN a la consulta (p. ej. EXPLAIN SELECT ...;). Eso es solo el plan estimado. Para reales, usa EXPLAIN ANALYZE SELECT ...;, y para información detallada de buffers de E/S, usa EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
• Oracle: un patrón de dos pasos — EXPLAIN PLAN FOR <query>; guarda el plan en una tabla interna, y SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); lo imprime. Para visualización rápida, la opción de sesión SET AUTOTRACE ON también funciona

Chuleta de la terminología de planes

La redacción difiere, pero lo que la base de datos está haciendo internamente es esencialmente lo mismo. El objetivo de este artículo es ser capaz de leer "¿está leyendo cada fila, está acotando con un índice, necesita un área de trabajo para ordenar, está ejecutando una subconsulta una y otra vez?" en la salida de EXPLAIN de cada base de datos.