Este artículo es una continuación de la historia sobre las novedades en PostgreSQL 12. Ya analizamos SQL / JSON (parche JSONPath) en el artículo
"Lo que se congelaron en la congelación de funciones 2019. Parte I. JSONPath" , ahora es el turno de CTE.
Cte
CTE es expresión de tabla común: expresiones de tabla comunes, también se denominan construcciones WITH. De hecho, esta es la creación de tablas temporales, pero existentes para una sola consulta, y no para la sesión. Se puede acceder a ellos dentro de esta solicitud. Dicha solicitud se lee bien, es comprensible, es fácil modificarla si es necesario. Esto es algo muy popular, y ha estado en PostgreSQL durante mucho tiempo.
Pero los servicios pueden ser caros. Los problemas están relacionados con la materialización de la expresión después de AS dentro de la construcción WITH ... AS (). También se llama una expresión interna y se evalúa antes de comenzar a calcular el resto; no se puede incrustar en una consulta de nivel superior (sin línea). La planificación de esta expresión no tiene en cuenta el resto de la solicitud. Este comportamiento se llama barrera para la optimización o cercado. Además, la materialización en sí misma requiere work_mem. Y si la muestra es grande, entonces comienzan los problemas (por ejemplo, hay un
informe de Ivan Frolkov en PGConf 2019).
Ocultar y buscar con el optimizador, que analizaremos a continuación, no es un error en general, sino una característica. Por supuesto, hay situaciones en las que el cálculo preliminar de una parte de una expresión elimina, por ejemplo, operaciones innecesarias repetidas en consultas recursivas. Por otro lado, muchos desarrolladores usaron CTE como una vista sin pensar en la barrera, y como resultado, las solicitudes de CTE se ejecutaron no solo más lentamente que las solicitudes equivalentes (sino más sofisticadas) con subconsultas, sino también más lentamente por órdenes de magnitud. Después de sopesar los pros y los contras, la comunidad dio un paso decisivo: cambió el comportamiento predeterminado.
Observaremos el trabajo de CTE en dicho plato:
CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x; CREATE INDEX ON xytable(x,y);
Table "public.xytable" Column | Type | Collation | Nullable | Default --------------+---------+------------------+----------------+--------- x | integer | | | y | integer | | | Indexes: "xytable_x_y_idx" btree (x, y)
Comencemos con una simple solicitud:
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
Todo se considera al instante, solo se utiliza el índice.
Una consulta con una subconsulta que calcula lo mismo, pero con una sintaxis un poco más complicada:
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
Todo está en orden, cálculo de índice muy rápido.
Y ahora una solicitud más lógicamente equivalente, pero con CTE:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Tal retraso ya es visible a simple vista. No tomará café, pero hay tiempo suficiente para revisar el correo (cuando tengamos la versión 11 o anterior).
Y esto es lo que sucedió: en el caso de las subconsultas, el optimizador se dio cuenta de inmediato de que las condiciones x = 2 e y> 1 se pueden combinar en un filtro y buscar por índice. En el caso de CTE, el optimizador no tiene otra opción: primero debe lidiar con la condición dentro de la construcción WITH ... AS, materializar el resultado y solo luego trabajar.
Y aquí el punto no es que la materialización requerirá recursos: si la condición es y <3, entonces no se tendrán que materializar millones de registros, sino solo 2. El tiempo monstruoso para una consulta simple se gasta en búsqueda secuencial, el optimizador no puede usar la búsqueda de índice porque para que el índice compuesto se construya en x, y solo entonces en y, y no sabrá nada sobre una consulta con la condición x = 2 hasta que cumpla la condición CTE interna. Está más allá de la barrera.
Entonces, antes de PostgreSQL 12, el valor predeterminado era la materialización, ahora su ausencia. Lanzamos la misma solicitud basada en la nueva versión. Barrera, por así decirlo, el optimizador ve de inmediato toda la solicitud:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN ------------------------------------------ Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.067 ms Execution Time: 0.029 ms (5 rows)
El optimizador aprendió instantáneamente a combinar las condiciones en el orden óptimo, como fue el caso con las subconsultas.
Pero los valores predeterminados son valores predeterminados, y para una propiedad completa de la situación ahora, en la versión 12 hay una materialización controlada y controlada de CTE:
WITH cte_name AS [NOT] MATERIALIZED
Vamos a materializar:
EXPLAIN ANALYZE WITH yy AS MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN --------------------------- CTE Scan on yy (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Bitmap Heap Scan on cte (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1) Recheck Cond: (y > 1) Heap Blocks: exact=44248 -> Bitmap Index Scan on xytable_x_y_idx1 (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1) Index Cond: (y > 1) Planning Time: 0.086 ms Execution Time: 3612.840 ms (11 rows)
Todo está como en 11 y antes, puede ver el correo en el modo de espera de los resultados de la consulta. Prohibimos la materialización, eliminamos la barrera:
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Una vez más, no hay respiro: cuenta al instante.
Matices restantes. Pero matices importantes.
CTE se materializa por defecto si se accede a él más de una vez.
A primera vista, la materialización en tales casos es una decisión razonable: ¿por qué calcular la misma cosa dos veces? En la práctica, esto a menudo conduce a lo que observamos anteriormente. Para forzar el rechazo de la materialización, es necesario ordenar explícitamente el optimizador: NO MATERIALIZADO.
Ejecutamos sin solicitud NO MATERIALIZADA con doble DONDE:
WITH yy AS ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=3922.274..3922.275 rows=1 loops=1) CTE yy -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1) -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 InitPlan 3 (returns $2) -> Aggregate (actual time=812.580..812.580 rows=1 loops=1) -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 Planning Time: 0.136 ms Execution Time: 3939.848 ms (17 rows)
Y ahora escribiremos explícitamente una prohibición de materialización:
WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=0.035..0.035 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=0.006..0.006 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.253 ms Execution Time: 0.075 ms (13 rows)
Los CTE de escritura siempre se ejecutan, y los CTE a los que no se hace referencia nunca.
Esto es evidente en el plan: not_executed no está en él. Esto es cierto para versiones anteriores, pero vale la pena recordarlo, y la construcción (NO) MATERIALIZADA se aplica a la expresión ejecutable en la versión 12.
EXPLAIN (COSTS OFF) WITH yy AS ( SELECT * FROM xytable WHERE y > 1), not_executed AS ( SELECT * FROM xytable), always_executed AS ( INSERT INTO xytable VALUES(2,2) RETURNING *) SELECT FROM yy WHERE x=2;
QUERY PLAN ----------------------------- CTE Scan on yy Filter: (x = 2) CTE yy -> Seq Scan on cte Filter: (y > 1) CTE always_executed -> Insert on cte cte_1 -> Result (5 rows)
Y una regla más:
Las consultas recursivas con WITH siempre se materializan.
Siempre lo es, y no por defecto. Si pedimos el optimizador: NO MATERIALIZADO, no habrá error y la materialización seguirá siéndolo. Esta es una decisión consciente de la comunidad.
Consideraremos el ejemplo ilustrativo de tarea. Eso es todo por hoy.
Esta parte de la revisión dedicada a lo nuevo en CTE utiliza ejemplos y fragmentos del informe "Postgres 12 in Etudes", que Oleg Bartunov leyó en Saint Highload ++ en San Petersburgo el 9 de abril de este año.En la próxima serie -
KNN .