Saltar al contenido
Engineer
Menú
Diseño
Español

Engineer / Glosario

CTE

consulta con nombre que una sentencia lee como una tabla

Qué es

Una CTE, abreviatura de common table expression o expresión de tabla común, es una consulta a la que se da nombre en una cláusula WITH al principio de una sentencia, para que el resto de esta pueda leer sus filas como si procedieran de una tabla. Una sentencia puede definir varias CTE, cada una capaz de leer las nombradas antes, y la consulta final las combina. El manual de PostgreSQL las describe como «tablas temporales que existen solo para una consulta», una imagen adecuada con una salvedad: no se almacena nada con ese nombre y la siguiente sentencia no puede verlo. Cada CTE puede ser un SELECT o, en PostgreSQL, un INSERT, UPDATE, DELETE o MERGE, y la sentencia a la que se añade la cláusula WITH también puede ser cualquiera de ellas.

Una pregunta como «qué contadores consumieron más del triple de su propio consumo habitual en algún día de los últimos 30 días» se divide de forma natural en pasos:

WITH daily AS (
  SELECT meter_id, read_at::date AS day, sum(kwh) AS kwh
  FROM readings
  WHERE read_at >= current_date - 30
  GROUP BY meter_id, read_at::date
),
typical AS (
  SELECT meter_id, avg(kwh) AS avg_kwh
  FROM daily
  GROUP BY meter_id
)
SELECT d.meter_id, d.day, d.kwh, t.avg_kwh
FROM daily AS d
JOIN typical AS t USING (meter_id)
WHERE d.kwh > 3 * t.avg_kwh
ORDER BY d.day, d.meter_id;

El primer paso suma las lecturas diarias de cada contador; el segundo calcula la media de esos totales por contador; y el último compara cada día con la media de su propio contador. La misma respuesta puede escribirse con subconsultas anidadas, pero los totales diarios aparecerían dos veces en el texto, una dentro de la otra, y el lector tendría que trabajar de dentro hacia fuera. Con WITH, la lógica se lee de arriba abajo y cada paso puede ejecutarse por separado mientras se comprueba.

Añadir RECURSIVE permite que una CTE lea su propia salida, algo que SQL sin esa construcción no tiene otra forma de expresar. Una CTE recursiva tiene una consulta inicial, después UNION o UNION ALL y después una consulta que vuelve a referirse a la CTE. PostgreSQL ejecuta la consulta inicial, guarda sus filas en una tabla de trabajo y sigue ejecutando la segunda sobre las filas más recientes hasta que una ronda no añade ninguna. Así recorre una sentencia un árbol de profundidad desconocida, como las categorías que dependen de un encabezado:

WITH RECURSIVE subtree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE id = 42
  UNION ALL
  SELECT c.id, c.parent_id, c.name, s.depth + 1
  FROM categories AS c
  JOIN subtree AS s ON c.parent_id = s.id
)
SELECT id, name, depth FROM subtree ORDER BY depth, name;

Por qué importa

La primera razón es la legibilidad. Una consulta analítica de cien líneas con cuatro niveles de anidamiento es difícil de revisar y más difícil aún de cambiar. La misma lógica como cadena de pasos con nombre da a quien revisa una frase por paso, da al autor un lugar para poner un comentario y permite ejecutar un paso aislado para ver si sus filas parecen correctas antes de confiar en el siguiente.

La segunda razón es cómo evalúa la base de datos los pasos, y aquí los detalles han cambiado con el tiempo. PostgreSQL evalúa normalmente una consulta WITH una vez por ejecución de la sentencia, aunque esta la mencione varias veces, por lo que un cálculo costoso necesario en dos lugares se ejecuta una sola vez. El coste de esa garantía es que el planificador no puede trasladar un filtro de la consulta externa a una CTE referenciada varias veces, porque el filtro debería aplicarse a uno de sus usos y no a los demás. La CTE se calcula completa y las filas se descartan después.

Antes de PostgreSQL 12, eso ocurría con todas las CTE: cada una se evaluaba antes que el resto de la consulta y nunca se integraba en ella. La versión 12 cambió el comportamiento predeterminado. Una CTE no recursiva, sin efectos secundarios y referenciada una sola vez se integra ahora en la consulta externa, de modo que el planificador optimiza juntos los dos niveles y puede llevar el filtro externo hasta un índice de la tabla base. MATERIALIZED fuerza el comportamiento antiguo y NOT MATERIALIZED fuerza la integración incluso para una CTE utilizada varias veces, con el riesgo de ejecutar su trabajo dos veces. SQLite acepta las mismas dos palabras clave como indicaciones no vinculantes y dice que tomó la idea de PostgreSQL. Otros motores deciden por su cuenta, y el plan es donde hay que comprobarlo.

La tercera razón es que una CTE puede modificar datos. Un DELETE con RETURNING en una cláusula WITH puede pasar su salida a un INSERT de la sentencia principal, lo que mueve filas de una tabla a otra en una sola sentencia que se ejecuta por completo o no se ejecuta. Las CTE que modifican datos se ejecutan exactamente una vez y siempre hasta terminar, se lea o no su salida, lo que las convierte en una herramienta para agrupar varias escrituras, no en una forma de describir un resultado.

Cómo se aplica en la práctica

El trabajo de acelerar 10 veces el rendimiento de PostgreSQL se realizó sobre una base de datos de cuentas de usuario, organizaciones cliente, ubicaciones y lecturas diarias de electricidad que se ralentizaba al crecer los datos. La solución tuvo tres partes: índices específicos y GiST, particionamiento temporal de la tabla de lecturas y reescritura de consultas. Las reescrituras utilizaban CTE y vistas materializadas para los datos consultados con frecuencia, a fin de evitar recorridos completos de tablas, con EXPLAIN ANALYZE señalando las uniones y subconsultas que convenía reestructurar. La mejora de diez veces en los tiempos de respuesta y la caída del 40 % en el uso de recursos procedieron de las tres partes juntas, no solo de las CTE. Conviene señalar la combinación porque cumplen funciones distintas: una CTE da nombre a un resultado intermedio durante una sentencia, mientras que una vista materializada almacena un resultado que muchas sentencias pueden leer hasta que se actualiza.

La otra vertiente de las CTE, la legibilidad, sustentó los 500 informes de análisis de datos eléctricos y GIS. Todos los informes se escribieron en SQL puro para PostgreSQL y BigQuery sobre datos de contadores inteligentes con coordenadas, identificadores de contador y consumos con marca de tiempo, y los scripts utilizaban CTE junto con funciones de ventana, subconsultas y uniones geoespaciales. Cada informe se entregó con SQL anotado para que otros miembros del equipo pudieran reproducir y auditar el análisis, además de notas sobre problemas comunes de calidad de los datos, como coordenadas GPS ausentes y valores de contadores corruptos. Un SQL que se lee paso a paso se comparte bien: los informes se convirtieron en una referencia entre departamentos y ayudaron a mejorar un 10–15 % la eficiencia de la planificación operativa. Los pasos con nombre son lo que permite anotar un SQL de ese tipo: una nota encima de cada paso puede indicar qué elimina o qué da por supuesto.

Dónde surgen los problemas

Un filtro aplicado cuando todo el trabajo ya está hecho. Una CTE referenciada dos veces o marcada MATERIALIZED se calcula completa antes de que la consulta externa la filtre, y la copia que produce no tiene índices. Una consulta que debería tocar unas pocas filas lee una tabla entera. Lea el plan: si la CTE aparece como un paso independiente, con recuentos de filas muy superiores a lo que devuelve la consulta, pruebe NOT MATERIALIZED o reformule la consulta para referenciar la CTE una sola vez. Mantenga la forma materializada cuando la CTE llame a una función costosa, para que se ejecute una vez por fila y no una vez por referencia.

Una consulta recursiva que no termina. Unos datos con un ciclo, como dos categorías que figuren cada una como padre de la otra, hacen que una CTE recursiva con UNION ALL dé vueltas mientras el servidor lo permita. UNION solo ayuda cuando se repiten filas completas, algo que rara vez ocurre al añadir una columna de profundidad. Registre en un array el recorrido de claves visitadas y deténgase si reaparece una, o utilice la cláusula CYCLE añadida en PostgreSQL 14. Un LIMIT en la consulta externa sirve como truco de prueba, y el manual desaconseja depender de él en producción. SQL Server limita la recursión de forma predeterminada a 100 niveles y cancela la sentencia al superarlos, así que el mismo error se detiene en lugar de continuar.

Cambios que el resto de la sentencia no ve. Una CTE que modifica datos y la consulta principal se ejecutan de forma concurrente sobre la misma instantánea, así que una consulta principal que lea directamente la tabla de destino ve las filas tal como estaban antes del cambio. El único canal entre ambas es RETURNING. Si dos partes de una sentencia modifican la misma fila, es peor: solo surte efecto una modificación y es difícil prever cuál. Lea las filas cambiadas de la salida RETURNING de la CTE y asegúrese de que cada fila solo la modifique una parte de la sentencia.

Un plan que cambió con una actualización. El código escrito para PostgreSQL 11 o anterior a veces dependía de que una CTE se evaluara primero, como barrera deliberada contra un plan que el autor no quería. Tras actualizar a 12 o posterior, la misma CTE se integra en la consulta externa y el planificador elige algo nuevo, normalmente más rápido y en ocasiones mucho más lento. Compare la salida de EXPLAIN de las sentencias lentas antes y después de la actualización y añada MATERIALIZED donde la barrera fuera intencionada.

Lecturas adicionales

Contacto

¿Aún está definiendo lo que necesita?

Cuéntenos qué está fallando o qué intenta construir. Le diremos qué haría falta, cuánto costaría aproximadamente y si somos las personas adecuadas.

Desde 1.400 DKK por hora, sin IVA; y un precio por hora menor en una contratación más larga.

welcome@engineer.company +45 50 10 61 84 Copenhague, Dinamarca