PostgreSQL
CTEs y consultas recursivas
Common Table Expressions para organizar consultas, reutilizar resultados y recorrer estructuras jerárquicas con WITH RECURSIVE.
- Última actualización
- Actualizada
- Nivel
- Aplicación
PostgreSQL
Common Table Expressions para organizar consultas, reutilizar resultados y recorrer estructuras jerárquicas con WITH RECURSIVE.
Sintaxis:
WITH name AS (
SELECT ...
)
SELECT ...
FROM name;El nombre solo existe durante esa sentencia. PostgreSQL puede integrar el CTE en el plan principal o materializar su resultado, según versión, uso y opciones.
Un CTE sirve para separar etapas lógicas:
filtrar órdenes pagadas
→ agregar por customer
→ filtrar customers de alto valorUna consulta compleja puede repetir subqueries o mezclar varias granularidades. Un CTE permite nombrar cada transformación:
WITH paid_orders AS (
SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid'
),
customer_totals AS (
SELECT customer_id, sum(total_amount) AS total
FROM paid_orders
GROUP BY customer_id
)
SELECT customer_id, total
FROM customer_totals
WHERE total > 1000000;Esto mejora lectura, pero dividir una consulta en muchos CTEs triviales también puede dificultar seguir el flujo.
CTE no recursivo
→ relación intermedia nombrada
→ usada por la sentencia principal
CTE recursivo
→ anchor produce filas iniciales
→ recursive term usa resultado anterior
→ repite hasta no producir filasEstas formas pueden ser equivalentes:
WITH totals AS (...)
SELECT * FROM totals;SELECT *
FROM (...) AS totals;El CTE aporta:
RETURNING.La subquery puede ser mejor cuando el resultado solo se usa una vez y mantenerlo cerca del consumidor hace la consulta más clara.
Históricamente los CTEs actuaban como optimization fences. Desde PostgreSQL 12, un CTE SELECT no recursivo, side-effect-free y usado de forma compatible puede inlinearse.
El planner integra la definición y puede empujar filtros:
WITH products_base AS (
SELECT * FROM products
)
SELECT *
FROM products_base
WHERE business_id = 10;Puede convertirse conceptualmente en una sola scan filtrada.
PostgreSQL ejecuta el CTE, almacena el resultado intermedio y luego lo consume.
Ventajas:
Costes:
WITH expensive AS MATERIALIZED (
SELECT ...
)
SELECT ...;Fuerza una evaluación separada.
WITH filtered AS NOT MATERIALIZED (
SELECT ...
)
SELECT ...;Solicita integración cuando la semántica lo permite.
No elijas por regla fija. Compara EXPLAIN (ANALYZE, BUFFERS) y considera cuántas veces se usa el CTE.
WITH totals AS (
SELECT customer_id, sum(total_amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT
high.customer_id,
high.total,
average.average_total
FROM totals high
CROSS JOIN (
SELECT avg(total) AS average_total
FROM totals
) average
WHERE high.total > average.average_total;Si totals es costoso y se usa dos veces, materializar puede evitar recomputación. Pero el resultado completo debe almacenarse.
WITH moved AS (
DELETE FROM pending_orders
WHERE expires_at < now()
RETURNING id, customer_id, payload, now() AS expired_at
)
INSERT INTO expired_orders (id, customer_id, payload, expired_at)
SELECT id, customer_id, payload, expired_at
FROM moved;La sentencia mueve filas atómicamente:
Reglas importantes:
WITH created AS (
INSERT INTO orders (customer_id, total_amount)
VALUES ($1, $2)
RETURNING id, customer_id, total_amount
)
INSERT INTO outbox_events (event_type, aggregate_id, payload)
SELECT
'order.created',
id,
jsonb_build_object(
'orderId', id,
'customerId', customer_id,
'totalAmount', total_amount
)
FROM created
RETURNING aggregate_id;Esto mantiene order y evento en una misma sentencia/transacción. Un worker publica después del commit.
WITH RECURSIVE category_tree AS (
SELECT
id,
parent_id,
name,
0 AS depth,
ARRAY[id] AS path
FROM categories
WHERE id = $1
UNION ALL
SELECT
c.id,
c.parent_id,
c.name,
tree.depth + 1,
tree.path || c.id
FROM categories c
JOIN category_tree tree
ON c.parent_id = tree.id
WHERE NOT c.id = ANY(tree.path)
)
SELECT *
FROM category_tree
ORDER BY path;Partes:
Produce la raíz.
Une children con filas ya encontradas.
PostgreSQL mantiene internamente filas pendientes de expansión.
Finaliza cuando el recursive term no produce filas nuevas.
La sintaxis es recursiva; la implementación es iterativa internamente.
Conserva duplicados y suele ser más rápido. Requiere control explícito de ciclos.
Deduplica filas entre iteraciones y puede detener algunos ciclos, pero añade coste y solo ayuda si la fila completa repetida es idéntica.
Si incluyes depth, la misma node con otra profundidad ya no es duplicada; UNION no resuelve el ciclo por sí solo.
Descendientes:
parent_id = fila encontrada.idAncestros:
category.id = fila encontrada.parent_idDefine la dirección según la pregunta.
Guardar path durante la consulta permite:
Costo: arrays crecen con profundidad. Para grafos grandes, define límites y modelo apropiado.
Versiones modernas de PostgreSQL soportan cláusulas SQL estándar para orden de búsqueda y detección de ciclos:
... SEARCH DEPTH FIRST BY id SET order_path
CYCLE id SET is_cycle USING cycle_pathLa sintaxis exacta y soporte deben comprobarse en la major version desplegada. Estas clauses reescriben internamente lógica similar a arrays/path.
El orden en que el motor evalúa no debe confundirse con el orden de salida.
Para una presentación depth-first, genera una path y ordena.
Para breadth-first:
ORDER BY depth, idSiempre define desempate.
PostgreSQL no impone por defecto un límite de profundidad equivalente al de otros motores. Una recursión mal definida puede crecer hasta agotar recursos.
Controles:
WHERE tree.depth < 100statement_timeout.Una adjacency list con un parent por fila modela árbol/bosque.
Un grafo general usa tabla de edges:
CREATE TABLE graph_edges (
source_id bigint NOT NULL,
target_id bigint NOT NULL,
PRIMARY KEY (source_id, target_id)
);La recursión puede encontrar múltiples paths al mismo node. Decide:
WITH RECURSIVE reports AS (
SELECT id, manager_id, name, 0 AS level
FROM employees
WHERE id = $1
UNION ALL
SELECT e.id, e.manager_id, e.name, r.level + 1
FROM employees e
JOIN reports r ON e.manager_id = r.id
WHERE r.level < 20
)
SELECT *
FROM reports
ORDER BY level, id;Índice necesario:
CREATE INDEX employees_manager_id_idx
ON employees(manager_id);Sin él, cada expansión puede escanear la tabla.
CTE
→ vive una sentencia
→ no tiene índices propios creados por el usuario
→ no se reutiliza en otra sentencia
TEMP TABLE
→ vive una sesión/transacción
→ puede indexarse y analizarse
→ puede reutilizarsePara un resultado enorme reutilizado por varias sentencias, una temp table puede ser mejor, aunque añade DDL, catálogo y estado de sesión.
CTE → definición local
VIEW → objeto persistente del catálogoUna view ofrece permisos y reutilización; un CTE mantiene lógica cerca de una consulta específica.
La consulta consumidora recibe cero filas.
Puede usar archivos temporales y aumentar latencia.
Inlining/materialización cambia cuántas veces pueden evaluarse; PostgreSQL conserva semántica, pero tu elección de MATERIALIZED importa.
Puede no terminar o crecer hasta límites.
Cada acceso a tablas aplica policies; el árbol visible puede quedar incompleto para un rol.
Toda la sentencia falla; la transacción queda abortada hasta rollback.
Fragmenta la consulta sin modelo mental.
Cambió desde PostgreSQL 12.
Puede repetir un cálculo usado varias veces.
Riesgo de ciclos y agotamiento.
Depth/path pueden impedir deduplicación.
Sigue siendo una sola sentencia; no coordina servicios externos.
CTE Scan.Set operations: UNION, INTERSECT y EXCEPT combina resultados compatibles y obliga a distinguir deduplicación, tipos y orden final.