Este artigo é uma continuação da história sobre novidades no PostgreSQL 12. Já analisamos SQL / JSON (patch JSONPath) no artigo
“O que eles congelaram no congelamento de recursos 2019. Parte I. JSONPath” , agora é a vez da CTE.
Cte
CTE é expressão comum de tabela - expressões comuns de tabela, elas também são chamadas de construções WITH. De fato, essa é a criação de tabelas temporárias, mas existem apenas para uma consulta e não para a sessão. Eles podem ser acessados dentro desta solicitação. Essa solicitação é bem lida, é compreensível, é fácil modificá-la, se necessário. Isso é muito popular e está no PostgreSQL há muito tempo.
Mas as comodidades podem ser caras. Os problemas estão relacionados à materialização da expressão após AS dentro da construção WITH ... AS (). Também é chamada de expressão interna e é avaliada antes de começar a calcular o restante; não pode ser incorporada a uma consulta de nível superior (sem inlining). O planejamento para esta expressão não leva em consideração o restante da consulta. Esse comportamento é chamado de barreira à otimização ou vedação. Além disso, a própria materialização requer work_mem. E se a amostra for grande, os problemas começam (por exemplo, há um
relatório de Ivan Frolkov no PGConf 2019).
Esconde-esconde com o otimizador, que analisaremos abaixo, não é um bug em geral, mas um recurso. Obviamente, há situações em que o cálculo preliminar de uma parte de uma expressão elimina, digamos, operações repetidas desnecessárias em consultas recursivas. Por outro lado, muitos desenvolvedores usaram o CTE como uma visão, sem pensar nessa barreira e, como resultado, os pedidos do CTE foram executados não apenas mais lentamente que os pedidos equivalentes (mas mais sofisticados) com subconsultas, mas mais lentos por ordens de magnitude. Depois de ponderar os prós e os contras, a comunidade deu um passo decisivo: mudou o comportamento padrão.
Vamos observar o trabalho da CTE em tal prato:
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)
Vamos começar com uma solicitação simples:
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
Tudo é considerado instantaneamente, apenas o índice é usado.
Uma consulta com uma subconsulta que calcula o mesmo, mas com uma sintaxe um pouco mais complicada:
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
Está tudo em ordem, cálculo de índice muito rápido.
E agora mais uma solicitação logicamente equivalente, mas com o CTE:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Esse atraso já é visível a olho nu. Você não bebe café, mas há tempo suficiente para analisar o correio (quando temos a 11ª versão ou anterior).
E aqui está o que aconteceu: no caso de subconsultas, o otimizador percebeu imediatamente que as condições x = 2 e y> 1 podem ser combinadas em um filtro e pesquisadas por índice. No caso do CTE, o otimizador não tem escolha: ele deve primeiro lidar com a condição dentro da construção WITH ... AS, materializar o resultado e somente depois trabalhar.
E aqui não é importante que a materialização exija recursos: se a condição for y <3, não será necessário materializar milhões de registros, mas apenas 2. O tempo monstruoso de uma consulta simples é gasto na pesquisa seqüencial, o otimizador não pode usar a pesquisa de índice porque para que o índice composto seja construído em x, e somente então em y, e ele não saberá nada sobre a consulta com a condição x = 2 até que ele atenda à condição CTE interna. Está além da barreira.
Portanto, antes do PostgreSQL 12, o padrão era materialização, agora sua ausência. Lançamos a mesma solicitação com base na nova versão. Barreira, por assim dizer, o otimizador vê imediatamente toda a solicitação:
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)
O otimizador aprendeu instantaneamente a combinar as condições na ordem ideal - como foi o caso das subconsultas.
Mas os padrões são padrões e, para a propriedade completa da situação agora, na versão 12, há uma materialização controlada e controlada do CTE:
WITH cte_name AS [NOT] MATERIALIZED
Vamos 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)
Tudo é como em 11 e, antes disso, você pode ver as mensagens no modo de espera dos resultados da consulta. Proibimos a materialização, removemos a barreira:
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Novamente, não há trégua: conta instantaneamente.
Nuanças restantes. Mas nuances importantes.
O CTE se materializa por padrão se for acessado mais de uma vez.
À primeira vista, a materialização nesses casos é uma decisão razoável: por que calcular a mesma coisa duas vezes. Na prática, isso geralmente leva ao que observamos acima. Para forçar a recusa de materialização, é necessário solicitar explicitamente o otimizador: NÃO MATERIALIZADO.
Executamos sem solicitação NÃO MATERIALIZADA com duplo WHERE:
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)
E agora escreveremos explicitamente uma proibição de materialização:
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)
CTEs de gravação são sempre executados e CTEs que não são referenciados nunca.
Isso é evidente no plano: not_executed não está nele. Isso vale para versões anteriores, mas vale lembrar que, e a construção (NOT) MATERIALIZED se aplica à expressão executável na versão 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)
E mais uma regra:
consultas recursivas com WITH sempre se materializam.
É sempre, e não por padrão. Se solicitarmos o otimizador: NÃO MATERIALIZADO, não haverá erro e a materialização ainda ocorrerá. Esta é uma decisão consciente da comunidade.
Vamos considerar o exemplo ilustrativo de lição de casa. Isso é tudo por hoje.
Esta parte da revisão dedicada ao novo no CTE usa exemplos e fragmentos do relatório “Postgres 12 in Etudes”, que Oleg Bartunov leu no Saint Highload ++ em São Petersburgo, em 9 de abril deste ano.Na próxima série -
KNN .