Cet article est une continuation de l'histoire des nouveautés de PostgreSQL 12. Nous avons déjà analysé SQL / JSON (patch JSONPath) dans l'article
«Ce qu'ils ont gelé lors du gel des fonctionnalités 2019. Partie I. JSONPath» , c'est maintenant au tour de CTE.
Cte
CTE est Common Table Expression - expressions de table communes, elles sont aussi appelées avec des constructions WITH. En fait, il s'agit de la création de tables temporaires, mais n'existant que pour une seule requête, et non pour la session. Ils sont accessibles dans cette demande. Une telle demande est bien lue, elle est compréhensible, il est facile de la modifier si besoin. C'est une chose très populaire, et elle existe depuis longtemps dans PostgreSQL.
Mais les équipements peuvent être chers. Les problèmes sont liés à la matérialisation de l'expression après AS dans la construction WITH ... AS (). Il est également appelé une expression interne et est calculé avant de commencer à calculer le reste; il ne peut pas être incorporé dans une requête de niveau supérieur (pas d'inline). La planification de cette expression ne prend pas en compte le reste de la demande. Ce comportement est appelé une barrière à l'optimisation ou à l'escrime. De plus, la matérialisation elle-même nécessite work_mem. Et si l'échantillon est grand, alors les problèmes commencent (par exemple, il y a un
rapport d' Ivan Frolkov à PGConf 2019).
Le cache-cache avec l'optimiseur, que nous analyserons ci-dessous, n'est pas un bug en général, mais une fonctionnalité. Bien sûr, il existe des situations où le calcul préliminaire d'une partie d'une expression élimine, disons, les opérations répétées inutiles dans les requêtes récursives. D'un autre côté, de nombreux développeurs ont utilisé CTE comme une vue sans penser à la barrière, et en conséquence, les demandes de CTE ont été exécutées non seulement plus lentement que les demandes équivalentes (mais plus sophistiquées) avec des sous-requêtes, mais plus lentement par ordre de grandeur. Après avoir pesé le pour et le contre, la communauté a franchi une étape décisive: elle a changé le comportement par défaut.
Nous observerons le travail de CTE sur une telle plaque:
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)
Commençons par une simple demande:
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
Tout est considéré instantanément, seul l'index est utilisé.
Une requête avec une sous-requête qui calcule la même chose, mais avec une syntaxe un peu plus compliquée:
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
Tout est en ordre, calcul d'indice très rapide.
Et maintenant une autre requête logiquement équivalente, mais avec CTE:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Un tel retard est déjà visible à l'œil nu. Vous ne boirez pas de café, mais vous avez suffisamment de temps pour consulter le courrier (lorsque nous aurons la 11e version ou plus tôt).
Et voici ce qui s'est passé: dans le cas des sous-requêtes, l'optimiseur s'est immédiatement rendu compte que les conditions x = 2 et y> 1 peuvent être combinées en un seul filtre et recherchées par index. Dans le cas de CTE, l'optimiseur n'a pas le choix: il doit d'abord traiter la condition à l'intérieur de la construction WITH ... AS, matérialiser le résultat et ensuite seulement travailler.
Et ici, le fait n'est pas que la matérialisation nécessitera des ressources: si la condition est y <3, alors pas des millions d'enregistrements devront être matérialisés, mais seulement 2. Le temps monstrueux pour une requête simple est consacré à la recherche séquentielle, l'optimiseur ne peut pas utiliser la recherche d'index car de sorte que l'index composite est construit sur x, puis seulement sur y, et il ne saura rien sur une requête avec la condition x = 2 jusqu'à ce qu'il remplisse la condition CTE interne. C'est au-delà de la barrière.
Donc, avant PostgreSQL 12, la valeur par défaut était la matérialisation, maintenant son absence. Nous lançons la même demande basée sur la nouvelle version. Barrière, pour ainsi dire, l'optimiseur voit immédiatement toute la demande:
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)
L'optimiseur a instantanément appris à combiner les conditions dans l'ordre optimal - comme ce fut le cas avec les sous-requêtes.
Mais les valeurs par défaut sont des valeurs par défaut, et pour une appropriation complète de la situation maintenant, dans la version 12, il y a une matérialisation contrôlée et contrôlée de CTE:
WITH cte_name AS [NOT] MATERIALIZED
Réalisons:
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)
Tout est comme dans 11 et avant, vous pouvez regarder le courrier en mode veille des résultats de la requête. Nous interdisons la matérialisation, supprimons la barrière:
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
Encore une fois, pas de répit: ça compte instantanément.
Nuances restées. Mais des nuances importantes.
CTE se matérialise par défaut si on y accède plusieurs fois.
À première vue, la matérialisation dans de tels cas est une décision raisonnable: pourquoi calculer deux fois la même chose. En pratique, cela conduit souvent à ce que nous avons observé ci-dessus. Pour forcer le refus de matérialisation, il est nécessaire de commander explicitement l'optimiseur: NON MATÉRIALISÉ.
Nous exécutons sans demande NON MATÉRIELLE avec double O double:
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)
Et maintenant, nous allons explicitement écrire une interdiction de matérialisation:
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)
L'écriture de CTE est toujours exécutée et les CTE qui ne sont pas référencés ne le sont jamais.
Cela ressort clairement du plan: not_executed n'y figure pas. Cela est vrai pour les versions précédentes, mais il convient de le rappeler, et la construction (NOT) MATERIALIZED s'applique à l'expression exécutable dans la version 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)
Et encore une règle:
les requêtes récursives avec WITH se matérialisent toujours.
C'est toujours le cas, et non par défaut. Si nous commandons l'optimiseur: NON MATÉRIALISÉ, il n'y aura pas d'erreur, et la matérialisation sera toujours. Il s'agit d'une décision consciente de la communauté.
Nous considérerons l'exemple de devoirs illustratifs. C'est tout pour aujourd'hui.
Cette partie de la revue consacrée aux nouveautés en CTE utilise des exemples et fragments du rapport «Postgres 12 in Etudes», qu'Oleg Bartunov a lu à Saint Highload ++ à Saint-Pétersbourg le 9 avril dernier.Dans la prochaine série -
KNN .