Respuesta

La instrucción WITH (o CTE — Common Table Expression) crea temporalmente un conjunto de resultados que se puede utilizar en el SELECT principal o incluso de manera recursiva. Los CTE hacen que las consultas sean más fáciles de entender, mejoran la legibilidad y permiten reutilizar resultados intermedios.

Ventajas sobre las subconsultas normales:

Mejoran la legibilidad de consultas complejas (más fácil de depurar y mantener).
Se pueden usar consultas recursivas.
Se puede referir a un CTE varias veces por nombre, en lugar de duplicar la misma subconsulta.

Ejemplo:

WITH high_salary AS (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Pregunta capciosa

“¿Es importante especificar MATERIALIZED/NOT MATERIALIZED al declarar un CTE? ¿Cómo puede afectar esto al rendimiento?”

Respuesta: En PostgreSQL, se puede especificar explícitamente MATERIALIZED (siempre calcular el CTE una vez y almacenar, incluso si se usa varias veces) o NOT MATERIALIZED (el CTE se integrará en la consulta principal). Normalmente, el optimizador decide si materializar el CTE, pero especificar forzosamente a veces afecta significativamente la velocidad.

Ejemplo:

WITH high_salary AS MATERIALIZED (
  SELECT id, name FROM employees WHERE salary > 100000
)
SELECT * FROM high_salary WHERE name LIKE 'A%';

Historia

En un gran proyecto, un analista reemplazó todas las subconsultas por CTE, sin saber que en PostgreSQL hasta la versión 12 los CTE siempre se materializan. Como resultado, las consultas se ralentizaron de 2 a 3 veces. Después de volver a las subconsultas o pasar a versiones nuevas, el problema se resolvió.

Historia

Un informe con varios niveles de subconsultas anidadas resultó ilegible para un colega. Se reescribió en CTE con nombres significativos, lo que facilitó considerablemente la discusión y el mantenimiento de las consultas.

Historia

Dos CTE con los mismos nombres causaron confusión en un gran script: el CTE se utilizó incluso antes de su declaración. Como resultado, se produjo un error de compilación y una gran pérdida de tiempo buscando la causa.

¿Qué hace la instrucción WITH (Common Table Expressions, CTE)? ¿Cuáles son las ventajas de usar CTE en comparación con las subconsultas? Proporcione un ejemplo.

Respuesta

Pregunta capciosa