CTEs en SQL: WITH, WITH RECURSIVE y cuándo usarlos en lugar de subconsultas

Las CTEs, o Common Table Expressions, son esas subconsultas con nombre que van al principio de una query con la cláusula WITH. Son muy útiles para organizar consultas complejas, pero también tienen un comportamiento específico en PostgreSQL que conviene entender bien para no llevarse sorpresas de rendimiento.

La forma básica: WITH nombre AS (...)

La sintaxis es simple: defines un bloque con nombre antes de la query principal, y luego lo usas como si fuera una tabla.

WITH pedidos_recientes AS (
  SELECT usuario_id, COUNT(*) AS total_pedidos
  FROM pedidos
  WHERE fecha >= NOW() - INTERVAL '30 days'
  GROUP BY usuario_id
)
SELECT u.nombre, pr.total_pedidos
FROM usuarios u
JOIN pedidos_recientes pr ON pr.usuario_id = u.id
WHERE pr.total_pedidos > 5;

Una ventaja práctica de las CTEs sobre las subqueries inline es que puedes referenciarlas varias veces en la misma consulta sin repetir el código. Si la misma subconsulta aparece en tres sitios, con una CTE la defines una sola vez.

El comportamiento en PostgreSQL: optimization fence

Aquí viene la parte que mucha gente no sabe. En PostgreSQL, las CTEs son por defecto optimization fences: el planificador las materializa, es decir, ejecuta la CTE de forma independiente, guarda el resultado en memoria y luego lo usa en el resto de la query. No puede empujar predicados del exterior hacia dentro de la CTE.

Esto significa que si escribes esto:

WITH todos_pedidos AS (
  SELECT * FROM pedidos
)
SELECT * FROM todos_pedidos WHERE usuario_id = 42;

PostgreSQL ejecutará SELECT * FROM pedidos entero, guardará todos los resultados, y luego filtrará por usuario_id = 42. No es lo mismo que un SELECT * FROM pedidos WHERE usuario_id = 42 directo.

Desde PostgreSQL 12 existe la opción NOT MATERIALIZED para que se comporte como una subquery inline:

WITH todos_pedidos AS NOT MATERIALIZED (
  SELECT * FROM pedidos
)
SELECT * FROM todos_pedidos WHERE usuario_id = 42;

Y también existe MATERIALIZED para forzar la materialización explícitamente, aunque es el comportamiento por defecto.

WITH RECURSIVE: jerarquías y grafos

La parte más potente de las CTEs es la variante recursiva. Con WITH RECURSIVE puedes recorrer estructuras jerárquicas o en grafo que con SQL normal serían imposibles o requerirían múltiples queries.

La estructura tiene siempre dos partes separadas por UNION ALL:

1. Anchor member: la consulta base, el punto de partida (filas raíz).
2. Recursive member: la consulta que se referencia a sí misma para ir bajando nivel a nivel.

El ejemplo clásico es una tabla de categorías con padre/hijo:

WITH RECURSIVE categoria_arbol AS (
  -- Anchor: empezamos desde la raiz (sin padre)
  SELECT id, nombre, padre_id, 0 AS nivel
  FROM categorias
  WHERE padre_id IS NULL

  UNION ALL

  -- Recursive member: unimos cada categoria con su padre
  SELECT c.id, c.nombre, c.padre_id, ct.nivel + 1
  FROM categorias c
  JOIN categoria_arbol ct ON ct.id = c.padre_id
)
SELECT * FROM categoria_arbol ORDER BY nivel, nombre;

El motor ejecuta el anchor, luego aplica el recursive member sobre los resultados, y así sucesivamente hasta que el recursive member no devuelve filas nuevas. Por defecto hay un límite de 100 iteraciones (max_recursion no existe, pero si hay ciclos el motor lo detecta), así que en grafos con ciclos hay que añadir una condición de parada explícita.

Cuándo usar CTEs y cuándo subconsultas

Usa CTEs cuando:

• Necesitas referenciar la misma subconsulta más de una vez en la query.
• Quieres separar una query compleja en pasos legibles.
• Necesitas recursión para árboles o grafos.
• Quieres controlar explícitamente la materialización para aislar efectos secundarios.

Usa subconsultas inline cuando:

• La subconsulta solo se usa en un sitio y el planificador puede optimizarla mejor integrada en la query principal.
• Tienes predicados del exterior que el planificador podría empujar hacia dentro (pushdown), algo que no hace con CTEs materializadas.

En general, para queries analíticas largas con varios pasos, las CTEs mejoran mucho la legibilidad. Para queries de producción muy críticas en rendimiento, mide siempre con EXPLAIN ANALYZE antes de asumir que una CTE es igual de rápida que una subquery equivalente. El artículo sobre EXPLAIN ANALYZE en PostgreSQL de esta misma serie cubre exactamente cómo interpretar esos planes.

Si vienes del mundo de Ruby y ActiveRecord, donde los N+1 son el problema habitual, las CTEs te dan una herramienta para resolver en una sola query lo que de otra forma requeriría varias. El contraste con los patrones de eager loading en ActiveRecord es interesante.

Imagen: Pexels / Myburgh Roux