Y bueno, ha llegado el momento de hablar del lado oscuro de los CTE — los errores típicos. Incluso la consulta más pro puede romperse si usas mal estas herramientas tan potentes. Pero tranqui, aquí tienes una guía para diagnosticarlos y prevenirlos.
1. Error: materialización de CTE y sus consecuencias
Una de las cosas clave de PostgreSQL al trabajar con CTE es su materialización por defecto. O sea, el resultado del CTE se procesa y se guarda temporalmente en memoria (o en disco si hay demasiados datos). Si tienes muchas consultas o un volumen grande de datos, esto puede ralentizar mucho la ejecución.
Ejemplo:
WITH heavy_data AS (
SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;
A primera vista parece que el CTE solo filtra los datos. Pero en realidad, heavy_data primero se carga y materializa entero, y solo después se aplica el filtro. Esto puede tardar un montón.
¿Cómo evitarlo?
Desde PostgreSQL versión 12 puedes usar CTE como expresión inline (como un subquery), lo que soluciona el problema de la materialización. Solo tienes que definir CTE que se usen una sola vez y no necesiten guardar resultados intermedios.
Ejemplo de enfoque optimizado:
WITH inline_data AS MATERIALIZED (
SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;
Consejo: si quieres que sí se materialice, pon MATERIALIZED. Si no, usa NOT MATERIALIZED.
2. Error: CTE recursivos que se quedan en bucle
Los CTE recursivos son potentes, pero si los usas sin límite de profundidad pueden quedarse en bucle. No solo ralentiza todo, sino que se comen todos los recursos.
Ejemplo:
WITH RECURSIVE endless_loop AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM endless_loop
)
SELECT * FROM endless_loop;Esto va a generar infinitas filas porque no hay ninguna condición que pare la recursividad.
¿Cómo evitarlo?
Pon una condición de parada clara usando WHERE. Por ejemplo:
WITH RECURSIVE limited_loop AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM limited_loop
WHERE value < 10
)
SELECT * FROM limited_loop;Consejo: si usas CTE recursivos para jerarquías grandes, limita la profundidad con la opción max_recursion_depth de PostgreSQL.
3. Error: mal uso de UNION y UNION ALL
Cuando unes la consulta base y la recursiva en un CTE, elegir mal entre UNION y UNION ALL puede dar resultados raros. Por ejemplo, UNION elimina filas duplicadas, lo que cuesta recursos extra.
Ejemplo:
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id
FROM employees
WHERE manager_id IS NULL
UNION -- Aquí mejor usar UNION ALL
SELECT e.employee_id, e.manager_id
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;En este ejemplo, UNION puede borrar filas importantes de la jerarquía si se repiten por casualidad. ¡Y encima ralentiza la consulta!
¿Cómo arreglarlo?
Usa UNION ALL si no necesitas eliminar duplicados:
UNION ALL
4. Error: demasiados CTE en una sola consulta
Intentando hacer la consulta más estructurada, hay quien mete decenas de CTE. Esto no solo lía el código, sino que sobrecarga el planificador de consultas de PostgreSQL.
Ejemplo:
WITH cte1 AS (...),
cte2 AS (...),
cte3 AS (...),
...
cte20 AS (...)
SELECT ...
FROM cte20;
Parece una pesadilla para cualquier developer.
¿Cómo arreglarlo?
— Divide la consulta en varias más simples. En vez de un megaquery con decenas de CTE, haz varias consultas independientes.
— Otra opción: para resultados intermedios que necesitas varias veces, usa tablas temporales.
5. Error: CTE complejos sin índices
Si el CTE maneja muchos datos pero olvidas poner índices en las tablas, las consultas irán lentísimas. Los índices son como doping para tu base de datos.
Ejemplo:
WITH filtered_data AS (
SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;
¿Cómo arreglarlo?
Antes de usar CTE, asegúrate de que tus tablas están optimizadas:
CREATE INDEX idx_large_table ON large_table(unindexed_column);
6. Error: intentar usar CTE para llamadas múltiples de datos
El CTE se crea, se ejecuta y luego se "congela". Si necesitas su resultado en varios sitios, los datos no se recalculan — y a veces eso da errores.
Ejemplo:
WITH data AS (
SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- Si necesitas recalcular data otra vez, no va a pasar.
¿Cómo arreglarlo?
Si necesitas dinamismo o recalcular, igual el CTE no es la mejor opción. Usa subqueries.
7. Error: falta de comentarios
Los CTE pueden ser una pasada, pero ¿quién quiere una consulta SQL compleja que nadie, ni tú, pueda leer dentro de dos semanas?
Ejemplo:
WITH data_filtered AS (
SELECT *
FROM large_table
WHERE some_column > 100
)
SELECT * FROM data_filtered;
¡En un mes nadie recordará por qué se filtraban esos datos!
Así que comenta tus consultas, sobre todo si usas CTE complejos o recursivos:
WITH data_filtered AS (
-- Filtro de datos por el valor de la columna some_column > 100
SELECT *
FROM large_table
WHERE some_column > 100
)
SELECT * FROM data_filtered;
8. Error: abusar de CTE en vez de tablas temporales
A veces las tablas temporales son mucho mejor opción. Por ejemplo, si necesitas el resultado varias veces en diferentes consultas o trabajas con un set de datos enorme.
Ejemplo:
WITH temp_data AS (
SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;
¡Esta consulta con CTE se ejecuta dos veces aunque los datos no cambien!
¿Cómo arreglarlo?
Crea una tabla temporal si vas a usar los datos muchas veces:
CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;
SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;Consejo final
Como con cualquier función potente, al trabajar con CTE es clave entender que no siempre son la mejor herramienta para todo. Piensa bien para qué y cómo los usas. El rollo de "cuantos más CTE mejor" puede empeorar mucho el rendimiento y la legibilidad del código. Y por supuesto, no olvides hacer tests de rendimiento y optimizar tus consultas.
GO TO FULL VERSION