🥇PostgreSQL Query Profiler : Comment comparer un plan et une requête

Beaucoup de ceux qui l'utilisent déjà expliquer.tensor.ru — notre service de visualisation de plan PostgreSQL n'est peut-être pas conscient de l'un de ses super pouvoirs : transformer un morceau difficile à lire du journal du serveur…

…dans une requête magnifiquement formatée avec des conseils contextuels sur les nœuds de plan pertinents :

Dans cette transcription de la deuxième partie de son rapport à PGConf.Russia 2020 Je vais vous dire comment nous avons réussi à faire cela.

Une transcription de la première partie, consacrée aux problèmes typiques de performances des requêtes et à leurs solutions, peut être trouvée dans l'article. Recettes pour les requêtes SQL défectueuses.

Tout d'abord, faisons la coloration - et nous ne colorierons pas le plan, nous l'avons déjà coloré, il est déjà beau et clair, mais la demande.

Nous avons estimé que la requête, extraite du journal de cette manière, sans la formater, était très moche et donc peu pratique.

Surtout lorsque les développeurs « collent » le corps de la requête (c'est, bien sûr, un contre-modèle, mais ça arrive) en une seule ligne de code. Horrible !

Dessinons cela d'une manière plus jolie.

Et si nous pouvons dessiner cela magnifiquement, c'est-à-dire démonter et remonter le corps de la demande, alors nous pouvons alors « attacher » un indice à chaque objet de cette demande - ce qui s'est passé au point correspondant du plan.

Arbre de syntaxe de requête

Pour ce faire, la requête doit d’abord être analysée.

Parce que nous avons le noyau du système fonctionne sur NodeJS, puis nous avons créé un module pour cela, vous pouvez Vous pouvez le trouver sur GitHubEn fait, il s'agit de « liaisons » étendues aux composants internes de l'analyseur PostgreSQL. Autrement dit, la grammaire est simplement compilée en binaire et liée à celui-ci par NodeJS. Nous avons utilisé d'autres modules comme base ; il n'y a pas de secret.

Nous alimentons le corps de la requête en entrée de notre fonction - en sortie, nous obtenons un arbre de syntaxe analysé sous la forme d'un objet JSON.

Nous pouvons maintenant parcourir cet arbre en arrière et assembler une requête avec l'indentation, la couleur et le formatage souhaités. Non, ce n'est pas personnalisable, mais nous avons pensé que ce serait pratique.

Mappage des nœuds de requête aux nœuds de planification

Voyons maintenant comment nous pouvons combiner le plan dont nous avons discuté dans la première étape et la requête dont nous avons discuté dans la deuxième.

Prenons un exemple simple : une requête crée un CTE et le lit deux fois. Elle génère le plan suivant.

CTE

Si vous le regardez attentivement, il s'agit de la version 12 (ou à partir de celle-ci avec le mot-clé MATERIALIZED) formation Le CTE est un obstacle absolu pour le planificateur.

A, donc si on voit quelque part dans la requête la génération d'un CTE et quelque part dans le plan un nœud CTE, alors ces nœuds sont clairement « en conflit » les uns avec les autres, nous pouvons immédiatement les combiner.

Problème avec un astérisque:Les CTE peuvent être imbriqués.

Il y en a de très mal imbriqués, et même des portant le même nom. Par exemple, vous pouvez à l'intérieur CTE A faire CTE X, et au même niveau à l'intérieur CTE B refaites-le CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

Lors du mappage, il est essentiel de comprendre cela. Comprendre cela visuellement, même en observant le plan et le corps de la requête, est très difficile. Si votre génération d'EFT est complexe, imbriquée et que vos requêtes sont volumineuses, cela est totalement inconscient.

UNION

Si nous avons un mot-clé dans notre requête UNION [ALL] (l'opérateur de jonction de deux échantillons), alors dans le plan il correspond soit à un nœud Append, ou quelque chose Recursive Union.

Ce qui est « au-dessus » au-dessus UNION — c'est le premier descendant de notre nœud, qui est « en dessous » — le deuxième. Si à travers UNION Nous avons plusieurs blocs collés à la fois, puis Append- il n'y aura toujours qu'un seul nœud, mais il n'aura pas deux enfants, mais plusieurs - dans l'ordre dans lequel ils vont, respectivement :

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

Problème avec un astérisque: à l'intérieur de la génération d'un échantillon récursif (WITH RECURSIVE) il peut aussi y en avoir plusieurs UNION. Mais seul le tout dernier bloc après le dernier est toujours récursif. UNIONTout ce qui précède est un, mais différent UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

Il faut aussi savoir décoincer de tels exemples. Dans cet exemple, on voit que UNION- Notre requête comportait trois segments. Par conséquent, un UNION match Append-nœud, et à l'autre - Recursive Union.

Lecture et écriture de données

Bon, nous avons décomposé le problème. Nous savons maintenant quel fragment de requête correspond à quel fragment de plan. Et au sein de ces fragments, nous pouvons facilement et sans effort trouver les objets « lisibles ».

Du point de vue d'une requête, nous ne savons pas s'il s'agit d'une table ou d'un CTE, mais ils sont désignés par le même nœud. RangeVarEt en termes de « lisible », il s’agit également d’un ensemble de nœuds assez limité :

Seq Scan on [tbl]
Bitmap Heap Scan on [tbl]
Index [Only] Scan [Backward] using [idx] on [tbl]
CTE Scan on [cte]
Insert/Update/Delete on [tbl]

Nous connaissons la structure du plan et de la requête, nous connaissons la correspondance des blocs, nous connaissons les noms des objets - nous faisons une comparaison sans ambiguïté.

Encore problème avec un astérisqueNous prenons la requête, l'exécutons, nous n'avons aucun alias : nous lisons simplement deux fois le même CTE.

On regarde le plan : quel est le problème ? Pourquoi avons-nous obtenu cet alias ? Nous ne l'avons pas commandé. D'où vient ce « numéro » ?

PostgreSQL l'ajoute lui-même. Il faut juste comprendre que c'est exactement l'alias Pour nos besoins de comparaison avec le plan, cela n'a aucun sens ; il est simplement ajouté ici. Nous l'ignorerons.

La seconde problème avec un astérisque: si nous lisons à partir d'une table partitionnée, nous obtiendrons un nœud Append ou Merge Append, qui sera composé d'un grand nombre d'« enfants », et chacun d'entre eux sera d'une certaine sorte Scan'om de la table de section : Seq Scan, Bitmap Heap Scan ou Index ScanMais, dans tous les cas, ces « enfants » ne seront pas des requêtes complexes - c'est ainsi que ces nœuds peuvent être distingués des Append à UNION.

Nous comprenons également de tels nœuds, nous les collectons « dans une pile » et disons : «Tout ce que vous lisez sur megatable est ici et en bas de l'arborescence.".

Nœuds de réception de données « simples »

Values Scan conformément au plan VALUES dans la demande.

Result - c'est une demande sans FROM comme SELECT 1. Ou lorsque vous avez une expression volontairement fausse dans WHERE-block (alors l'attribut apparaît One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan « mappé » sur les SRF du même nom.

Mais avec les requêtes imbriquées, les choses sont plus compliquées - malheureusement, elles ne se transforment pas toujours en InitPlan/SubPlanParfois, ils se transforment en ... Join ou ... Anti Join, surtout quand vous écrivez quelque chose comme WHERE NOT EXISTS ...Et il n’est pas toujours possible de les combiner ici : le texte du plan ne contient pas les opérateurs correspondant aux nœuds du plan.

Encore problème avec un astérisque: quelques VALUES dans la requête. Dans ce cas, vous obtiendrez également plusieurs nœuds dans le plan. Values Scan.

Les suffixes « numéro » aideront à les distinguer les uns des autres ; ils sont ajoutés dans l'ordre dans lequel les suffixes correspondants sont trouvés. VALUES-blocs dans l'ordre de la demande de haut en bas.

Traitement des données

Il semble que nous ayons couvert tout ce qui se trouve dans notre demande - il n'en reste qu'une Limit.

Mais ici tout est simple - des nœuds tels que Limit, Sort, Aggregate, WindowAgg, Unique Ils sont associés un à un aux opérateurs correspondants dans la requête, le cas échéant. Il n'y a ici ni astérisques ni complications.

INSCRIPTION

Des difficultés surgissent lorsque nous voulons combiner JOIN entre eux. Ce n'est pas toujours possible, mais c'est possible.

Du point de vue de l'analyseur de requêtes, nous avons un nœud JoinExpr, qui a exactement deux descendants : gauche et droite. Il s'agit respectivement de ce qui se trouve « au-dessus » de votre jointure et de ce qui se trouve « en dessous » de celle-ci dans la requête.

Et du point de vue du plan, ce sont deux descendants de quelqu'un * Loop/* Join-nœud. Nested Loop, Hash Anti Join,… — quelque chose comme ça.

Utilisons une logique simple : si nous avons des tables A et B qui sont « jointes » l’une à l’autre dans le plan, alors dans la requête elles pourraient être situées soit A-JOIN-BOu B-JOIN-AEssayons de les combiner de cette façon, essayons de les combiner dans l'autre sens, et ainsi de suite jusqu'à ce que nous n'ayons plus de telles paires.

Prenons notre arbre syntaxique, prenons notre plan, regardons-les... ils ne se ressemblent pas !

Redessinons-le sous forme de graphiques - oh, ça commence déjà à ressembler à quelque chose !

Notons que nous avons des nœuds qui ont simultanément les enfants B et C ; l'ordre importe peu. Combinons-les et inversons l'image du nœud.

Revoyons cela. Nous avons maintenant des nœuds avec l'enfant A et des paires (B + C) ; apparions-les également.

Excellent ! Il s'avère que nous sommes ces deux-là JOIN à partir de la requête avec les nœuds du plan, ils ont été combinés avec succès.

Malheureusement, ce problème n’est pas toujours résolu.

Par exemple, si dans la requête A JOIN B JOIN C, et dans le plan, les nœuds « extrêmes » A et C ont été connectés en premier. Mais comme cet opérateur n'est pas présent dans la requête, nous n'avons rien à mettre en évidence, ni à associer l'indice. Il en va de même pour la virgule lorsque vous écrivez. A, B.

Mais dans la plupart des cas, il est possible de démêler presque tous les nœuds et d'obtenir une vue de profilage temporelle comme celle de gauche, comme dans Google Chrome pour analyser du code JavaScript. Vous pouvez voir le temps d'exécution de chaque ligne et de chaque instruction.

Et pour vous faciliter l'utilisation de tout cela, nous avons créé un espace de stockage l'archive, où vous pouvez enregistrer et retrouver ultérieurement vos plans ainsi que les requêtes associées ou partager un lien avec quelqu'un.