Eliminar trabajo repetido y optimizar subconsultas — ejemplos prácticos

Los datos que usamos en este artículo — perf_sales y employee

En el artículo anterior viste cómo reescribir IN / EXISTS y subconsultas correlacionadas.

Este artículo se construye sobre eso con reescrituras que evitan calcular dos veces la misma agregación (plegando agregaciones repetidas en un único resultado intermedio) y materializando tablas derivadas (construyendo una vez una subconsulta en una cláusula FROM y manteniéndola temporalmente) — practicado mediante ejemplos prácticos concretos.

Antes de meternos en los ejercicios, echa un vistazo a las definiciones de columna y los datos de muestra de las dos tablas que usa este artículo — perf_sales y employee.

① Usa PRAGMA table_info(perf_sales); y PRAGMA table_info(employee); para comprobar las definiciones de columna de ambas tablas.

② Usa SELECT * FROM perf_sales LIMIT 5; y SELECT * FROM employee LIMIT 5; para previsualizar las primeras 5 filas. perf_sales tiene 50 000 filas, así que añade siempre LIMIT al previsualizar.

No escribas la misma agregación dos veces — pliega la duplicación en un único resultado intermedio

Cuando quieres preguntar "¿el total de ventas de cada región está por encima del total medio entre todas las regiones?", escribir el total y la media como dos subconsultas separadas hace que perf_sales se escanee dos veces.

Si pliegas la misma agregación en una sola CTE y la calculas una vez, y luego reutilizas el resultado, solo escaneas una vez.

El patrón de reescritura es: detecta el caso donde "la misma agregación con GROUP BY aparece tanto en la consulta principal como en una subconsulta", eleva esa agregación a una sola CTE y haz que la consulta principal referencie la CTE en su lugar.

El resultado es idéntico, y el escaneo duplicado desaparece del plan.

-- Antes: el total y la "media entre regiones" se agregan por separado (perf_sales escaneada dos veces)
SELECT region, SUM(amount) AS total
FROM perf_sales
GROUP BY region
HAVING SUM(amount) > (
  SELECT AVG(t) FROM (
    SELECT SUM(amount) AS t FROM perf_sales GROUP BY region
  )
);

-- Después: agrega por región una vez en una CTE
WITH region_total AS (
  SELECT region, SUM(amount) AS total
  FROM perf_sales
  GROUP BY region
)
SELECT region, total
FROM region_total
WHERE total > (SELECT AVG(total) FROM region_total)
ORDER BY region;

Ejecuta la pregunta "¿qué regiones tienen un total de ventas por encima del total medio entre todas las regiones?" en dos formas — la versión con agregación duplicada y la versión con CTE — y compara los planes.

① Antepón EXPLAIN QUERY PLAN a la consulta y muestra el plan de la versión que escribe la agregación con GROUP BY region tanto en la consulta principal como dentro de la subconsulta del HAVING.

② Pliega la misma agregación por región en una sola CTE, toma la media de esa CTE con AVG y ordena las regiones por encima de ella con ORDER BY region. Muestra también el plan de esta versión con EXPLAIN QUERY PLAN.

③ Confirma que SCAN perf_sales aparece varias veces en la versión "antes" y baja a una sola vez en la versión "después".

Colapsa subconsultas correlacionadas en una agregación de una sola pasada

Cuando una condición como "¿el total de ventas de cada región está por encima del total máximo por empleado dentro de esa región?" se calcula como una subconsulta correlacionada por cada fila externa de región, la consulta interna se reevalúa una vez por cada región.

Esto también puede plegarse en una agregación de una sola vez con clave por región, y luego unirla con JOIN.

El patrón de reescritura es: toma una (SELECT aggregate ... WHERE child.key = parent.key) llamada por cada fila externa y conviértela en "construye una tabla agregada con GROUP BY key una vez, y luego únela con JOIN con la padre".

Las reevaluaciones por región se reemplazan por una sola agregación más un JOIN.

-- Antes: correlaciona por región para encontrar el "total máximo por empleado"
SELECT region,
  (SELECT MAX(s) FROM (
     SELECT SUM(amount) AS s FROM perf_sales p2
     WHERE p2.region = p1.region GROUP BY emp_id
  )) AS max_emp_total
FROM perf_sales p1
GROUP BY region;

-- Después: agrega una vez por (region, emp_id), luego toma el MAX por región
WITH emp_region AS (
  SELECT region, emp_id, SUM(amount) AS s
  FROM perf_sales
  GROUP BY region, emp_id
)
SELECT region, MAX(s) AS max_emp_total
FROM emp_region
GROUP BY region
ORDER BY region;

Ejecuta "el total máximo de ventas por empleado dentro de cada región" en dos formas — la versión correlacionada y la versión con agregación de una sola pasada — y compara los planes.

① Antepón EXPLAIN QUERY PLAN a la consulta y muestra el plan de la versión que usa una subconsulta correlacionada para calcular el "total máximo por empleado dentro de esta región" por cada fila externa de región.

② Construye una CTE que haga GROUP BY sobre `(region, emp_id)` exactamente una vez, y luego GROUP BY region sobre esa CTE para tomar el máximo. Muestra también el plan de esta versión con EXPLAIN QUERY PLAN.

③ Confirma que la palabra CORRELATED del plan correlacionado desaparece en el plan reescrito.

Materializar tablas derivadas — construye la subconsulta del FROM una vez

Una tabla derivada — una subconsulta escrita en una cláusula FROM — referenciada en dos sitios a veces puede ser recalculada dos veces por el optimizador.

Si la construyes una vez y la mantienes temporalmente (la materializas), las referencias no la reconstruyen cada vez.

En SQLite, referenciar varias veces una CTE hace que EXPLAIN QUERY PLAN muestre una línea MATERIALIZE — confirmando que la CTE se materializa exactamente una vez.

En el ejercicio final de este artículo tomarás una tabla derivada pesada que agrega perf_sales por emp_id y se referencia dos veces, la convertirás en una CTE materializada una vez y confirmarás que MATERIALIZE aparece en el plan y que el escaneo ocurre solo una vez.

-- Antes: la misma tabla derivada referenciada en dos cláusulas FROM
SELECT a.emp_id, a.tot, b.avg_tot
FROM (SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id) a
CROSS JOIN (
  SELECT AVG(tot) AS avg_tot FROM (
    SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id
  )
) b
ORDER BY a.emp_id;

-- Después: tabla derivada como una CTE referenciada dos veces (materializada una vez)
WITH emp_total AS (
  SELECT emp_id, SUM(amount) AS tot
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.tot, (SELECT AVG(tot) FROM emp_total) AS avg_tot
FROM emp_total e
ORDER BY e.emp_id;

Ejecuta "el total de ventas de cada empleado junto con la media global entre empleados" en dos formas — la versión que referencia la tabla derivada dos veces y la versión con CTE — y compara los planes. Este es el ejercicio final del artículo.

① Antepón EXPLAIN QUERY PLAN a la consulta y muestra el plan de la versión que referencia en dos sitios de la cláusula FROM la tabla derivada agregada por emp_id.

② Pliega la misma agregación en una sola CTE, toma la media de la misma CTE vía (SELECT AVG(tot) FROM misma_CTE) y ordena por emp_id. Muestra también el plan de esta versión con EXPLAIN QUERY PLAN.

③ Confirma que MATERIALIZE aparece en el plan reescrito y que SCAN perf_sales baja a una sola vez.