PostgreSQL Query Profiler : comment associer un plan et une requête

Beaucoup de ceux qui utilisent déjà explain.tensor.ru — notre service de visualisation des plans PostgreSQL ne sont peut-être pas au courant d'une de ses super capacités : transformer un extrait de journal de serveur difficile à lire…

… en une requête joliment formatée avec des suggestions contextuelles pour les nœuds correspondants du plan :

Dans cette analyse de la deuxième partie de sa présentation au PGConf.Russia 2020 je vais expliquer comment nous avons réussi à le faire.

Le transcription de la première partie, consacrée aux problèmes courants de performance des requêtes et à leurs solutions, est disponible dans l'article « Recettes pour les requêtes SQL en détresse ».

Commençons par la coloration — et nous allons colorer non pas le plan, car nous l'avons déjà coloré, il est déjà beau et clair, mais la requête.

Il nous a semblé que ce « rouleau » non formaté extrait du journal paraissait très peu attrayant et donc — peu pratique.


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

Dessiner cela d'une manière plus esthétique.


Et si nous pouvons le dessiner joliment, c'est-à-dire analyser et reconstruire le corps de la requête, alors nous pourrons également « attacher » une suggestion à chaque objet de cette requête — ce qui se passait à chaque point correspondant du plan.

Arbre syntaxique de la requête

Pour ce faire, il faut d'abord analyser la requête.


Puisque notre noyau système fonctionne sur NodeJS, nous avons créé un module, vous pouvez le trouver sur GitHub. En fait, cela représente des « liaisons » avancées avec l'intérieur du parseur PostgreSQL lui-même. C'est-à-dire que notre grammaire est simplement compilée de manière binaire, et des liaisons sont faites du côté NodeJS. Nous avons utilisé des modules existants comme base — il n'y a pas de grand secret ici.

Nous alimentons le corps de la requête dans notre fonction — en sortie, nous obtenons un arbre syntaxique analysé sous forme d'objet JSON.


Nous pouvons maintenant parcourir cet arbre à l'envers et reconstruire la requête avec les indentations, couleurs, formatage que nous souhaitons. Non, ce n'est pas configurable, mais nous avons pensé que c'était ce qui serait le plus pratique.

Correspondance des nœuds des requêtes et des plans

Voyons maintenant comment nous pouvons combiner le plan que nous avons analysé à la première étape et la requête que nous avons analysée à la deuxième.

Prenons un exemple simple : nous avons une requête qui génère un CTE et le lit deux fois. Cela génère un tel plan.

CTE

Si l'on y regarde de plus près, jusqu'à la version 12 (ou en commençant par celle-ci avec le mot-clé MATERIALIZED) la formation du CTE est un obstacle indéniable pour le planificateur..


Et donc, si nous voyons quelque part dans la requête une génération de CTE et quelque part dans le plan un nœud CTE, ces nœuds se « battent » entre eux, nous pouvons immédiatement les combiner.

La tâche « avec une étoile »: les CTE peuvent être imbriqués.

Ils peuvent être très mal imbriqués, et même avoir le même nom. Par exemple, vous pouvez à l'intérieur de CTE A faire CTE X, et au même niveau à l'intérieur de CTE B refaire CTE X:

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

Lors de la correspondance, vous devez comprendre cela. Comprendre cela « avec les yeux » — même en voyant le plan, même en voyant le corps de la requête — c'est très difficile. Si votre génération de CTE est complexe, imbriquée, et que les requêtes sont longues — alors c'est encore plus inconscient.

UNION

Si notre requête contient le mot-clé UNION [ALL] (opérateur de jointure de deux sélections), alors dans le plan il correspond soit à un nœud Ajouter, soit à quelque chose comme Union Récursive.


Ce qui est « en haut » de UNION est le premier enfant de notre nœud, ce qui est « en bas » est le deuxième. Si nous avons « collé » plusieurs blocs en même temps à travers UNION , le nœud sera toujours un seul, mais aura beaucoup d'enfants — dans l'ordre où ils viennent, respectivement : Ajouter(...) -- #1 UNION ALL (...) -- #2 UNION ALL (...) -- #3

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

: à l'intérieur de la génération d'une sélection récursive (

La tâche « avec une étoile ») il peut également y avoir plus d'unWITH RECURSIVE. Mais seul le dernier bloc après le dernier est toujours récursif. UNIONTout ce qui est au-dessus — c'est un, mais différent. UNIONWITH RECURSIVE T AS( (...) -- #1 UNION ALL (...) -- #2, ici se termine la génération de l'état de départ de la récursion. UNION ALL (...) -- #3, seul ce bloc est récursif et peut contenir une référence à T ) ... UNION:

De tels exemples doivent également être « collés ». Dans cet exemple, nous voyons que

-les segments dans notre requête étaient au nombre de 3. Par conséquent, un nœud correspond à l'un, et l'autre à UNIONLa lecture et l'écriture des données. UNION correspond à AjouterVoilà, nous avons tout décomposé, maintenant nous savons quel morceau de la requête correspond à quel morceau du plan. Et dans ces morceaux, nous pouvons facilement et librement trouver les objets qui sont « lus ». Union Récursive.

Du point de vue de la 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.

Tout est clair maintenant, nous savons quel segment de la requête correspond à quel segment du plan. Et dans ces segments, nous pouvons facilement et naturellement identifier les objets qui sont "lisibles".

Du point de vue de la requête, nous ne savons pas s'il s'agit d'une table ou d'une CTE, mais ils sont tous désignés par le même nœud. PlageVar. Dans le plan, « se lit » également comme un ensemble d'éléments assez limité :

• Scan séquentiel sur [tbl]
• Analyse de tas au format Bitmap sur [tbl]
• Index [Only] Scan [Backward] utilisant [idx] sur [tbl]
• Scan CTE sur [cte]
• Insérer/Mise à jour/Supprimer sur [tbl]

Nous connaissons la structure du plan et de la requête, nous savons quelles sont les correspondances des blocs, nous savons les noms des objets — nous faisons une correspondance sans ambiguïté.


Encore une fois, la tâche « avec étoile ». Nous prenons la requête, nous l'exécutons, nous n'avons pas d'alias — nous avons simplement lu deux fois un même CTE.


Nous examinons le plan — quel est le problème ? Pourquoi avons-nous un alias qui apparaît ? Nous ne l'avons pas demandé. D'où vient-il, ce « numéroté » ?

PostgreSQL l'ajoute lui-même. Il faut simplement comprendre que cet alias précis n'a aucune pertinence pour nous dans le but de la correspondance avec le plan, il a simplement été ajouté ici. Ne prêtons pas attention à lui.

Deuxième la tâche « avec étoile »: si nous lisons depuis une table partitionnée, nous allons obtenir un nœud Ajouter ou Fusion Append, qui sera composé d'un grand nombre de « enfants », chacun d'eux étant un Scande type ‘ de la table-section : Scan séquentiel, Analyse de tas au format Bitmap ou Index Scan. Mais, dans tous les cas, ces « enfants » ne seront pas des requêtes complexes — ainsi, ces nœuds peuvent être distingués des Ajouter lorsque UNION.


De tels nœuds, nous les comprenons également, nous les rassemblons « en un tout » et nous disons : "tout ce que tu as lu de la megatable — c'est ici et vers le bas de l'arbre.".

Les nœuds « simples » de récupération de données

Analyse des valeurs correspondent dans le plan VALEURS dans la requête.

Résultat — c'est une requête sans DE comme SELECT 1. Ou lorsque vous avez une expression forcément fausse dans le OÙ-bloc (alors un attribut apparaît One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- ou 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

Analyse de Fonction « s'alignent » sur des SRF homonymes.

Mais avec les sous-requêtes, tout devient plus difficile — malheureusement, elles ne se transforment pas toujours en InitPlan/PlanSub. Parfois, elles se transforment en ... Join ou ... Anti Join, surtout lorsque vous écrivez quelque chose comme WHERE NOT EXISTS .... Et là, les combiner n'est pas toujours possible — dans le texte du plan, les nœuds correspondants aux opérateurs dans le plan ne sont pas présents.

Encore une fois, la tâche « avec étoile »: plusieurs VALEURS dans la requête. Dans ce cas, dans le plan, vous obtiendrez plusieurs nœuds. Analyse des valeurs.


Les « suffixes numérotés » aideront à les distinguer les uns des autres — ils sont ajoutés exactement dans l'ordre de la découverte des blocs correspondants de haut en bas. VALEURSIl semble que nous avons tout analysé dans notre requête — il ne reste que

Traitement des données

Mais ici, c'est simple — des nœuds comme Limite.


« s'alignent » parfaitement sur les opérateurs correspondants dans la requête, s'ils y sont présents. Ici, il n'y a pas de « étoiles » ni de complications. Limite, Trier, Agrégat, WindowAgg, Unique Les difficultés apparaissent lorsque nous voulons combiner

JOIN

entre eux. Ce n'est pas toujours possible, mais c'est faisable. JOIN entre eux. Ce n'est pas toujours possible, mais c'est faisable.


Du point de vue du parseur de requêtes, nous avons un nœud ExprJoin, qui a exactement deux enfants - gauche et droit. C'est, respectivement, ce qui est «au-dessus» de votre JOIN et ce qui est «en dessous» dans la requête.

Et du point de vue du plan, c'est deux enfants d'un * Boucle/* Rejoignez-nœud. Boucle imbriquée, Jointure anti-hachage,… - quelque chose comme ça.

Utilisons une logique simple : si nous avons des tables A et B qui «s'unissent» entre elles dans le plan, alors dans la requête, elles peuvent être disposées soit A-JOIN-B, ou bien B-JOIN-A. Essayons de les assembler ainsi, essayons de les assembler à l'envers, et ainsi de suite jusqu'à ce que de telles paires soient épuisées.

Prenons notre arbre syntaxique, prenons notre plan, regardons-les... cela ne ressemble pas!


Redessinons-le sous forme de graphes - oh, cela commence déjà à ressembler à quelque chose!


Faisons attention, nous avons des nœuds qui ont en même temps des enfants B et C - peu importe dans quel ordre. Assemblons-les et retournons l'image du nœud.


Regardons encore une fois. Maintenant, nous avons des nœuds avec des enfants A et la paire (B + C) - assemblons-les également.


Super! Il s'avère que nous avons réussi à assembler ces deux JOIN de la requête avec les nœuds du plan.

Malheureusement, cette tâche n'est pas toujours résolue.


Par exemple, si dans la requête A JOIN B JOIN C, et dans le plan, en premier, les nœuds «extrêmes» A et C se sont joints. Et il n'y a pas un tel opérateur dans la requête, nous n'avons rien à mettre en surbrillance, rien auquel l'indice puisse être attaché. La même chose avec la «virgule», quand vous écrivez A, B.

Mais, dans la plupart des cas, presque tous les nœuds peuvent être «déliés» et obtenir ce type de profilage à gauche selon le temps - littéralement, comme dans Google Chrome, lorsque vous analysez le code JavaScript. Vous voyez combien de temps chaque ligne et chaque opérateur ont «été exécutés».


Et pour que vous puissiez utiliser tout cela plus facilement, nous avons créé un stockage d'une archive, où vous pouvez sauvegarder et ensuite retrouver vos plans avec les requêtes associées ou partager le lien avec quelqu'un.

Si vous devez simplement formater une requête illisible de manière adéquate, utilisez notre «normalizeur».

Source : habr.com