Siempre pensé que los CTE deberían considerarse como una macro de vista en línea. Entonces, mi pensamiento es: si no se hace referencia/usa el CTE, no se ejecuta. Es solo una definición, nada más.
Pero, tome la siguiente consulta:
create table t ( id int primary key ); with a as ( insert into t(id) values(1) ) select false; select * from t; Parece que después de la consulta basada en CTE, select * from t devuelve la tupla como se insertó en el CTE. ¿Por qué se inserta esta tupla, a pesar de que no se utiliza el CTE?
¿Es esto por diseño o especificación? ¿Es seguro confiar en este comportamiento? Esto permite ejecutar múltiples consultas que están totalmente descorrelacionadas en una sola consulta.
Esto parece contradecir la siguiente información: https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/#comment-19121
Sintácticamente , una CTE se comporta como cualquier otra expresión de tabla .
semánticamente es diferente. [en Postgres] siempre se ejecutará una vez , incluso si se hace referencia a él más de una vez.
[en Postgres] un CTE actuará como una barrera de optimización; los términos de la consulta no se pueden mover entre (dentro o fuera) del CTE y la consulta principal.
El segundo y tercer punto pueden tener serias implicaciones. Debido a la barrera&exactamente una vez, un CTE-scan difícilmente puede hacer uso del orden implícito o la presencia de índices dentro del CTE. Un escaneo CTE se comporta más o menos como un escaneo secuencial en una tabla desordenada o vista materializada. Para CTE pequeños esto no será un problema, ya que se puede usar un hash-join. Los CTE grandes necesitarán materialización + clasificación para unir el CTE a la consulta principal.
En postgres, un cte no debe considerarse una vista en línea, aunque es útil pensar en él como una vista materializada que vive dentro del alcance de una declaración. Se materializará un CTE si se referencia en otra parte de la consulta, o si se alteran datos (INSERTAR/ACTUALIZAR/BORRAR).
Entonces, dado que su ejemplo altera los datos, se evalúa el CTE, mientras que el enlace al que se refiere tiene un CTE que no altera los datos.
Mientras que en otras bases de datos, el optimizador bajará los predicados de la consulta externa al CTE, en postgresql, el CTE se materializará por completo.
p.ej
WITH cte AS (SELECT * FROM foo WHERE foo.bar = True) SELECT * FROM cte WHERE cte.id > 10 AND cte.id < 20es más lento que en postgresql
SELECT * FROM (SELECT * FROM foo WHERE bar = TRUE) cte WHERE cte.id > 10 AND cte.id < 20Esta es una consideración donde uno tiene predicados opcionales o dinámicos en la consulta externa. La versión CTE ligeramente más rápida sería
WITH cte AS (SELECT * FROM foo WHERE foo.bar = True AND foo.id > 10 AND foo.id < 20) SELECT * FROM cteEsto es así por diseño y puede confiar en este comportamiento para crear una barrera de optimización.
Se permiten CTE siempre que se permita una cláusula SELECT. Por lo tanto, es posible usar CTE dentro de las declaraciones INSERT, UPDATE o DELETE. No estoy seguro de si esto es parte del estándar SQL .
Por ejemplo, hasta la versión 9.5 y la introducción de la sintaxis INSERT ... ON CONFLICT , podíamos usar un CTE para realizar un UPSERT. Aquí hay un hilo SO que ilustra con un ejemplo
Hay un segundo tipo de CTE más interesante, CTE RECURSIVO, donde un CTE se compone de la unión de una parte iterativa y una parte aplicativa que puede trabajar sobre los valores generados en la parte iterativa. No creo que este tipo de consulta pueda insertarse de todos modos.