Analyseur de requêtes lentes EXPLAIN Postgres

--- name: postgres-explain-slow-query-analyzer description: Lit les plans EXPLAIN et EXPLAIN ANALYZE de Postgres (texte ou JSON) et identifie pourquoi une requête est lente - séquences séquentielles sur de grandes relations, boucles imbriquées avec des estimations de lignes énormes, mauvaises estimations (estimé vs réel), lectures de tampons élevées, tris/hachages qui débordent sur disque, absence de pushdown de LIMIT et nœuds coûteux en temps réel. Classe les correctifs (index, réécriture, statistiques). Analyseur Python hors ligne avec bibliothèque standard. --- # Analyseur de requêtes lentes EXPLAIN Postgres Vous aidez les développeurs et les administrateurs de bases de données à lire les plans de requêtes Postgres. Une requête lente est rarement corrigée en regardant uniquement le SQL : le plan montre si Postgres a choisi une séquence séquentielle, une boucle imbriquée qui a explosé, une mauvaise estimation de lignes, un tri qui a débordé sur disque, ou une jointure de hachage qui a lu des gigaoctets de tampons. Vous prenez un plan `EXPLAIN` ou `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT|JSON)`, nommez les nœuds coûteux, expliquez pourquoi ils nuisent, et classez des correctifs concrets. Cette compétence consiste à lire des plans, pas à réécrire du SQL de zéro sans preuve. Vous n'exécutez pas de déclarations destructives. Préférez `EXPLAIN (ANALYZE, BUFFERS)` sur une réplique ou dans une transaction que vous pouvez annuler ; avertissez que ANALYZE exécute réellement la requête. ## Fichiers dans cette compétence - `scripts/explain_analyze.py` - analyse la sortie EXPLAIN / EXPLAIN ANALYZE texte ou JSON ; évalue les nœuds par temps réel ou coût ; rapporte EX01-EX12 (séquences séquentielles, explosions de boucles imbriquées, mauvaises estimations, tris/hachages sur disque, lectures partagées élevées, branches jamais exécutées, déséquilibre parallèle) ; classe les correctifs suggérés (Python 3.8+ bibliothèque standard uniquement) - `references/plan-nodes-and-buffers.md` - comment lire les types de nœuds, coûts, lignes, boucles, tampons et fichiers temporaires - `references/checks-and-codes.md` - chaque code de constatation, gravité et quoi faire, plus des conseils d'entrée - `templates/slow-query-review.md` - la note de revue que vous remettez avec l'extrait de plan et les correctifs classés - `examples/orders_by_email.explain.txt` - un EXPLAIN ANALYZE texte d'une recherche lente (séquence séquentielle + boucle imbriquée) - `examples/review-report.md` - la sortie réelle de l'analyseur sur l'exemple ## Flux de travail ### 1. Collecter le plan Demandez la version de Postgres, le SQL, et un `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` (ou JSON). Si ANALYZE est dangereux en production, prenez `EXPLAIN (BUFFERS)` sans ANALYZE et dites que les temps ne sont que des estimations. Capturez les tailles de tables (`pg_stat_user_tables`, `pg_relation_size`) lorsqu'une séquence séquentielle semble suspecte. ### 2. Exécuter l'analyseur ``` python3 scripts/explain_analyze.py examples/orders_by_email.explain.txt python3 scripts/explain_analyze.py plan.json --json-out python3 scripts/explain_analyze.py plan.txt --min-ms 5 python3 scripts/explain_analyze.py --self-test ``` Sortie 0 = aucun constat élevé, 1 = constats élevés, 2 = mauvaise entrée. Sans shell, parcourez l'arbre du plan de bas en haut en utilisant `references/plan-nodes-and-buffers.md` et appliquez les codes dans `references/checks-and-codes.md`. ### 3. Lire le résultat - Élevé d'abord : EX01 Séquence séquentielle sur une grande relation avec un filtre sélectif, EX03 Boucle imbriquée avec des lignes réelles x boucles énormes, EX05 ratio de mauvaise estimation supérieur à 100x sur un nœud chaud, EX07 Tri/Hachage qui a débordé sur des fichiers temporaires. - Moyen : EX02 clés de jointure sans index, EX04 Filtre supprimant la plupart des lignes après une analyse, EX06 tampons de lecture partagés élevés, EX08 branches jamais exécutées qui semblent encore coûteuses sur papier. - Faible/info : EX09 workers parallèles inégaux, EX10 temps de planification élevé vs exécution, EX11 LIMIT non poussée vers le bas, EX12 CTE MATERIALIZED forçant un calcul complet. ### 4. Classer les correctifs Préférez les changements basés sur des preuves : ajoutez ou ajustez un index qui correspond au Filtre/Filtre de jointure, mettez à jour les statistiques (`ANALYZE`), réécrivez une sous-requête corrélée devenue une boucle imbriquée, augmentez `work_mem` uniquement lorsqu'un tri/hachage a débordé et que l'ensemble tient en mémoire, ou changez un CTE en `NOT MATERIALIZED` lorsque Postgres 12+ l'a matérialisé inutilement. N'inventez pas d'index sans nommer les colonnes du plan. ### 5. Livrer Retournez le verdict, les nœuds coûteux principaux avec temps réel et tampons, la liste des constatations, et une courte liste de correctifs classés en utilisant `templates/slow-query-review.md`. ## Exemple de résultat Sur le plan d'exemple, la boucle imbriquée a entraîné 98% du temps : une séquence séquentielle externe sur `customers` filtrée par `lower(email)` (pas d'index d'expression) a alimenté une analyse d'index interne sur `orders_customer_id_idx` qui a exécuté 48 210 boucles. Les lignes estimées sur l'analyse externe étaient de 42 contre 48 210 réelles (mauvaise estimation). Les tampons de lecture partagés étaient élevés sur `orders`. Correctifs classés : index d'expression sur `lower(email)`, puis confirmer la sélectivité de la jointure ; `ANALYZE customers`. ## Règles - Basez chaque affirmation sur un nœud du plan (type, coût/réel, lignes, boucles, tampons). - Distinguez EXPLAIN estimé uniquement de ANALYZE ; ne traitez jamais les coûts comme des millisecondes. - Ne recommandez pas de désactiver les séquences séquentielles globalement ou de définir random_page_cost à des valeurs fantaisistes sans mesurer. - Avertissez que EXPLAIN ANALYZE exécute la requête (déclencheurs, écritures, verrous). - C'est une aide à la lecture de plans, pas un substitut à la surveillance `pg_stat_statements` ou à une revue complète du schéma. FILE:examples/orders_by_email.explain.txt QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1) Buffers: shared hit=1204 read=88440 -> Seq Scan on public.customers c (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1) Filter: (lower((email)::text) = 'ada@example.com'::text) Rows Removed by Filter: 151790 Buffers: shared hit=20 read=1830 -> Index Scan using orders_customer_id_idx on public.orders o (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210) Index Cond: (customer_id = c.id) Filter: (created_at >= (now() - '90 days'::interval)) Rows Removed by Filter: 3 Buffers: shared hit=1184 read=86610 Planning Time: 0.412 ms Execution Time: 912.610 ms FILE:examples/review-report.md # Analyse EXPLAIN Postgres Source : `examples/orders_by_email.explain.txt` Temps d'exécution : 912.61 ms Temps de planification : 0.412 ms **Verdict : À CORRIGER** - 3 constatation(s) élevée(s). ## Nœuds principaux par temps (ou coût) | Nœud | Relation | ms réels (x boucles) | Coût | Lignes estimées -> réelles | Boucles | Lecture partagée | |------|----------|---------------------|------|-----------------|-------|-------------| | Boucle imbriquée | - | 912.4 | 184220 | 12 -> 96 | 1 | 88440 | | Analyse d'index | public.orders | 771.4 | 4 | 8 -> 0 | 48210 | 86610 | | Séquence séquentielle | public.customers | 88.2 | 1850 | 42 -> 48210 | 1 | 1830 | ## Constatations | Gravité | Code | Détail | |-----|------|--------| | élevée | EX01 | Séquence séquentielle sur public.customers avec Filtre sélectif ((lower((email)::text) = 'ada@example.com'::text)) ; a supprimé 151790 lignes | | élevée | EX03 | Analyse d'index enfant de la boucle imbriquée a exécuté loops=48210 (lignes réelles/boucle~0) consommant ~771.4 ms | | élevée | EX05 | Mauvaise estimation sur Séquence séquentielle public.customers : lignes estimées=42 réelles=48210 (ratio~1148x) | | moyenne | EX02 | Boucle imbriquée a une séquence séquentielle interne/externe sur public.customers | | moyenne | EX06 | Boucle imbriquée ? lecture partagée=88440 blocs | | moyenne | EX06 | Analyse d'index public.orders lecture partagée=86610 blocs | ## Correctifs classés (suggérés) 1. Ajoutez un index btree/expression qui correspond au Filtre de la séquence séquentielle (vérifiez les wrappers de fonctions comme lower()). 2. Réduisez les lignes externes de la boucle imbriquée ou indexez la clé de jointure interne ; revérifiez l'ordre de jointure après ANALYZE. 3. Exécutez ANALYZE sur les tables concernées ; envisagez des statistiques étendues pour les colonnes corrélées. 4. Réduisez les lectures partagées via un meilleur index ou en filtrant plus tôt pour que la boucle imbriquée ne relise pas les tas. FILE:references/checks-and-codes.md # Vérifications et codes | Code | Gravité | Signification | Quoi faire | |------|----------|---------|------------| | EX01 | élevée | Séquence séquentielle sur une relation avec un Filtre sélectif et de grandes lignes réelles ou un temps réel élevé | Index expression ou btree correspondant au filtre ; évitez d'envelopper la colonne dans une fonction sans index d'expression correspondant | | EX02 | moyenne | La condition de jointure semble non indexée (Séquence séquentielle interne de boucle imbriquée ou boucles élevées avec Filtre sur la clé de jointure) | Indexez la clé de jointure interne ; confirmez que les types de données correspondent (pas de cast qui tue l'index) | | EX03 | élevée | Boucle imbriquée avec boucles * lignes réelles très élevées (explosion de lignes) | Réduisez d'abord les lignes externes ; envisagez une jointure de hachage/fusion ; corrigez les mauvaises estimations qui poussent au choix de la boucle imbriquée | | EX04 | moyenne | Le Filtre supprime la plupart des lignes après une analyse (lignes entrantes >> lignes sortantes) | Poussez le prédicat dans une condition d'index ; réécrivez pour que le planificateur puisse utiliser un index | | EX05 | élevée | Mauvaise estimation : réel/estimé ou estimé/réel >= 100 sur un nœud qui compte | ANALYZE ; statistiques étendues pour les colonnes corrélées ; évitez les fonctions non immuables dans les prédicats | | EX06 | moyenne | Tampons de lecture partagés élevés sur un nœud chaud | Mise en cache/index pour réduire les lectures de tas ; vérifiez si la boucle imbriquée relit les mêmes pages | | EX07 | élevée | Tri ou hachage débordé sur fichiers temporaires (fusion externe / Batches > 1 avec écriture temporaire) | Réduisez les lignes avant tri/hachage ; augmentez work_mem prudemment pour cette session de requête ; ajoutez un index de support pour éviter le tri | | EX08 | faible | Nœud jamais exécuté (boucles réelles=0) mais domine le coût estimé | Ignorez pour le temps d'exécution ; utile lors de la comparaison de plans sans ANALYZE | | EX09 | faible | Requête parallèle avec contribution des workers inégale | Vérifiez les paramètres parallel_* et si le gather en vaut la peine | | EX10 | info | Temps de planification élevé vs temps d'exécution | Déclarations préparées ; simplifiez la planification ponctuelle ; problèmes d'élagage de partition | | EX11 | moyenne | LIMIT au-dessus d'un tri complet ou d'une agrégation complète d'un grand ensemble | Réécrivez pour que l'ordre/filtre permette une analyse d'index avec arrêt précoce | | EX12 | moyenne | Analyse CTE avec MATERIALIZED forçant une évaluation complète | WITH ... AS NOT MATERIALIZED (PG12+) ou intégrez le CTE | ## Conseils d'entrée - Plans texte : collez l'arbre complet ; ne retirez pas l'indentation. - Plans JSON : tableau `FORMAT JSON` de Postgres avec un objet plan ; le script accepte un tableau brut ou `{Plan: ...}`. - Les lignes d'en-tête optionnelles comme `QUERY PLAN` ou les règles en pointillés sont ignorées. FILE:references/plan-nodes-and-buffers.md # Nœuds de plan et tampons ## Capturer un plan utile ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) <requête>; -- ou EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <requête>; ``` En production, préférez un standby chaud ou enveloppez les écritures dans une transaction que vous annulez. `ANALYZE` exécute la déclaration. ## Lire une ligne de nœud texte Exemple : `Seq Scan on public.customers (cost=0.00..1550.00 rows=42 width=64) (actual time=0.020..85.110 rows=48210 loops=1)` - Type de nœud et relation - `cost=startup..total` (unités du planificateur, pas ms) - `rows` = lignes estimées ; sous ANALYZE aussi `actual time` et `rows` et `loops` - Temps total pour ce nœud ~= (temps réel total) * boucles pour le modèle de contribution de sous-arbre ; le temps de feuille est plus clair que le parent lorsque les parents incluent les enfants ## Types de nœuds courants - **Seq Scan** : lit tout le tas. Bien pour les petites tables ou lorsque la plupart des lignes correspondent ; pénible avec un Filtre sélectif sur une grande table. - **Index Scan / Index Only Scan / Bitmap Heap Scan** : bien lorsque sélectif. Bitmap Heap avec de nombreuses revérifications peut encore nuire. - **Nested Loop** : pour chaque ligne externe, exécute l'interne. Excellent pour peu de lignes externes + interne indexé ; catastrophique lorsque l'externe est grand. - **Hash Join / Merge Join** : mieux pour les ensembles plus grands ; surveillez le hachage qui déborde (`Batches` > 1, écriture temporaire). - **Sort** : `Sort Method: external merge` signifie débordement sur disque ; envisagez `work_mem` ou moins de lignes plus tôt. - **Aggregate / GroupAggregate / HashAggregate** : vérifiez si les colonnes de regroupement sont indexées ou pré-filtrées. - **CTE Scan** : Postgres 12+ peut MATÉRIALISER les CTE ; peut bloquer le pushdown de filtre. - **Limit** : doit se situer au-dessus d'un chemin bon marché ; si Limit est au-dessus d'un tri complet de tout, poussez les critères vers le bas. ## Tampons (option BUFFERS) - `shared hit` = trouvé dans les tampons partagés (bon marché) - `shared read` = lu depuis le système d'exploitation/disque dans les tampons (coûteux en cas de manque de cache) - `temp read/written` = fichiers de débordement work_mem Un nœud avec un `shared read` énorme sur le côté interne répété d'une boucle imbriquée nécessite souvent un meilleur filtre externe ou index. ## Mauvaises estimations de lignes Comparez `rows` (estimation) aux lignes réelles. Des ratios supérieurs à 10x (surtout 100x+) sur des nœuds coûteux signifient de mauvaises statistiques, un `ANALYZE` obsolète, des colonnes corrélées, ou une fonction enveloppant une colonne indexée (`lower(email)`). ## Ce que cette compétence ne fait pas Elle ne remplace pas `pg_stat_statements`, auto_explain, ou les revues de conception d'index qui partent du schéma seul. Elle part d'un plan et explique ce plan. FILE:scripts/explain_analyze.py #!/usr/bin/env python3 """Analyseur de plans EXPLAIN / EXPLAIN ANALYZE Postgres (bibliothèque standard uniquement, Python 3.8+). Analyse les plans texte ou JSON, évalue les nœuds coûteux, et rapporte les constatations EX01-EX12 axées sur la lecture du plan (séquences séquentielles, explosions de boucles imbriquées, mauvaises estimations, pression de tampons, débordements disque). Utilisation : explain_analyze.py PLAN.txt|.json [--min-ms N] [--json-out] explain_analyze.py --self-test Sortie : 0 = aucun constat élevé, 1 = constats élevés, 2 = mauvaise entrée. """ import argparse, json, re, sys SEV = {"high": 0, "medium": 1, "low": 2, "info": 3} NODE_RE = re.compile( r"^(?P<indent>\s*)(?:->\s*)?(?P<type>.+?)" r"(?=\s+using\s+|\s+on\s+|\s*\(cost=)" r"(?:\s+using\s+(?P<idx>[\w.]+))?" r"(?:\s+on\s+(?P<rel>[\w.]+))?" r"(?:\s+(?P<alias>\w+))?" r"\s*\(cost=(?P<cstart>[\d.]+)\.\.(?P<ctotal>[\d.]+)\s+rows=(?P<erows>[\d]+)\s+width=(?P<width>[\d]+)\)" r"(?:\s*\(actual time=(?P<astart>[\d.]+)\.\.(?P<atotal>[\d.]+)\s+rows=(?P<arows>[\d]+)\s+loops=(?P<loops>[\d]+)\))?" ) BUF_RE = re.compile( r"Buffers:\s*(?:shared hit=(?P<shit>\d+))?" r"(?:\s*read=(?P<sread>\d+))?" r"(?:.*?temp read=(?P<tread>\d+))?" r"(?:.*?written=(?P<twrite>\d+))?", re.I, ) FILTER_RE = re.compile(r"^\s+Filter:\s*(.+)$") REMOVED_RE = re.compile(r"Rows Removed by Filter:\s*(\d+)") SORT_RE = re.compile(r"Sort Method:\s*(.+)$", re.I) HASH_BATCH_RE = re.compile(r"Batches:\s*(\d+)", re.I) PLAN_TIME_RE = re.compile(r"Planning Time:\s*([\d.]+)\s*ms", re.I) EXEC_TIME_RE = re.compile(r"Execution Time:\s*([\d.]+)\s*ms", re.I) class InputError(Exception): pass def parse_text(text): lines = text.splitlines() nodes = [] extras = {"planning_ms": None, "execution_ms": None} stack = [] i = 0 while i < len(lines): line = lines[i].rstrip() if not line or set(line) <= set("-=") or line.strip() == "QUERY PLAN": i += 1 continue m = PLAN_TIME_RE.search(line) if m: extras["planning_ms"] = float(m.group(1)); i += 1; continue m = EXEC_TIME_RE.search(line) if m: extras["execution_ms"] = float(m.group(1)); i += 1; continue m = NODE_RE.match(line) if m: raw_indent = re.match(r"^(\s*)", line).group(1) ind = len(raw_indent) node = { "type": m.group("type").strip(), "rel": m.group("rel"), "idx": m.group("idx"), "cost_start": float(m.group("cstart")), "cost_total": float(m.group("ctotal")), "est_rows": int(m.group("erows")), "width": int(m.group("width")), "act_start": float(m.group("astart")) if m.group("astart") else None, "act_total": float(m.group("atotal")) if m.group("atotal") else None, "act_rows": int(m.group("arows")) if m.group("arows") else None, "loops": int(m.group("loops")) if m.group("loops") else None, "filter": None, "removed": None, "buffers": {}, "sort_method": None, "hash_batches": None, "indent": ind, "children": [], } while stack and stack[-1][0] >= ind: stack.pop() if stack: stack[-1][1]["children"].append(node) else: nodes.append(node) stack.append((ind, node)) j = i + 1 while j < len(lines): pl = lines[j] if not pl.strip(): break if NODE_RE.match(pl) or PLAN_TIME_RE.search(pl) or EXEC_TIME_RE.search(pl): break if not pl.startswith(" ") and not pl.startswith("\t"): break fm = FILTER_RE.match(pl) if fm: node["filter"] = fm.group(1).strip() rm = REMOVED_RE.search(pl) if rm: node["removed"] = int(rm.group(1)) bm = BUF_RE.search(pl) if bm: node["buffers"] = {k: int(bm.group(k)) for k in ("shit", "sread", "tread", "twrite") if bm.group(k)} sm = SORT_RE.search(pl) if sm: node["sort_method"] = sm.group(1).strip() hb = HASH_BATCH_RE.search(pl) if hb: node["hash_batches"] = int(hb.group(1)) j += 1 i = j continue i += 1 if not nodes: raise InputError("aucun nœud de plan trouvé (besoin de texte EXPLAIN avec cost=... rows=...)") return nodes, extras def parse_json_plan(obj): if isinstance(obj, list): if not obj: raise InputError("tableau de plan JSON vide") obj = obj[0] if not isinstance(obj, dict): raise InputError("le plan JSON doit être un objet ou un tableau d'objets") plan = obj.get("Plan") or obj.get("plan") if not plan: raise InputError("JSON manquant l'objet Plan") extras = { "planning_ms": obj.get("Planning Time"), "execution_ms": obj.get("Execution Time"), } def conv(p, indent=0): node = { "type": p.get("Node Type", "Inconnu"), "rel": p.get("Relation Name") or p.get("Relation"), "idx": p.get("Index Name"), "cost_start": float(p.get("Startup Cost", 0)), "cost_total": float(p.get("Total Cost", 0)), "est_rows": int(p.get("Plan Rows", 0)), "width": int(p.get("Plan Width", 0)), "act_start": p.get("Actual Startup Time"), "act_total": p.get("Actual Total Time"), "act_rows": p.get("Actual Rows"), "loops": p.get("Actual Loops"), "filter": p.get("Filter"), "removed": p.get("Rows Removed by Filter"), "buffers": {}, "sort_method": p.get("Sort Method"), "hash_batches": p.get("Hash Batches") or p.get("Batches"), "indent": indent, "children": [], } if "Shared Hit Blocks" in p or "Shared Read Blocks" in p: node["buffers"] = { k: int(p[src]) for k, src in (("shit", "Shared Hit Blocks"), ("sread", "Shared Read Blocks"), ("tread", "Temp Read Blocks"), ("twrite", "Temp Written Blocks")) if src in p } for ch in p.get("Plans") or []: node["children"].append(conv(ch, indent + 2)) return node return [conv(plan)], extras def walk(nodes): out = [] def w(n): out.append(n) for c in n["children"]: w(c) for n in nodes: w(n) return out def time_ms(n): if n["act_total"] is None or n["loops"] is None: return None return float(n["act_total"]) * float(n["loops"]) def analyze(nodes, extras, min_ms=1.0): findings = [] flat = walk(nodes) def add(sev, code, msg, node=None): findings.append((sev, code, msg, node)) for n in flat: t = n["type"] ms = time_ms(n) est, act = n["est_rows"], n["act_rows"] loops = n["loops"] or 1 rel = n["rel"] or "?" if t.startswith("Seq Scan") and n.get("filter") and (n.get("removed") or 0) > 1000: if (act or 0) > 1000 or (ms or 0) >= min_ms * 10: add("high", "EX01", "Séquence séquentielle sur %s avec Filtre sélectif (%s) ; a supprimé %s lignes" % (rel, n["filter"], n.get("removed")), n) if t.startswith("Nested Loop"): for c in n["children"]: if c["type"].startswith("Seq Scan"): add("medium", "EX02", "Boucle imbriquée a une séquence séquentielle interne/externe sur %s" % (c.get("rel") or "?"), c) if t.startswith("Nested Loop"): for c in n["children"]: cl = c.get("loops") or 0 if cl >= 10000 and (time_ms(c) or 0) >= min_ms * 20: add("high", "EX03", "Enfant de boucle imbriquée %s a exécuté loops=%d (lignes réelles/boucle~%s) consommant ~%.1f ms" % ( c["type"], cl, c.get("act_rows"), time_ms(c) or 0), c) if n.get("removed") and act is not None: total_in = n["removed"] + act if total_in > 1000 and n["removed"] / total_in >= 0.9: add("medium", "EX04", "Filtre sur %s %s supprime %.0f%% des lignes (%d supprimées, %d conservées)" % ( t, rel, 100.0 * n["removed"] / total_in, n["removed"], act), n) if act is not None and est is not None and (ms or 0) >= min_ms: if est == 0: ratio = float("inf") if act > 0 else 1.0 else: ratio = max(act / max(est, 1), est / max(act, 1)) if ratio >= 100: add("high", "EX05", "Mauvaise estimation sur %s %s : lignes estimées=%d réelles=%d (ratio~%.0fx)" % (t, rel, est, act, ratio if ratio != float("inf") else 9999), n) sread = n.get("buffers", {}).get("sread") or 0 if sread >= 5000 and (ms or 0) >= min_ms: add("medium", "EX06", "%s %s lecture partagée=%d blocs" % (t, rel, sread), n) sm = (n.get("sort_method") or "").lower() if "external" in sm: add("high", "EX07", "Tri débordé sur disque (%s) sur %s" % (n["sort_method"], t), n) if n.get("hash_batches") and n["hash_batches"] > 1 and (n.get("buffers", {}).get("twrite") or 0) > 0: add("high", "EX07", "Hachage débordé : Batches=%d écriture temporaire=%s" % (n["hash_batches"], n["buffers"].get("twrite")), n) if loops == 0 and n["cost_total"] >= 1000: add("low", "EX08", "Nœud %s jamais exécuté mais coût=%.0f" % (t, n["cost_total"]), n) if t.startswith("Limit"): for c in n["children"]: if c["type"].startswith("Sort") and (c.get("act_rows") or c.get("est_rows") or 0) > 1000: add("medium", "EX11", "LIMIT au-dessus d'un tri de grand ensemble (%s lignes)" % (c.get("act_rows") or c.get("est_rows")), n) if "CTE" in t and (ms or n["cost_total"] > 1000): add("medium", "EX12", "Analyse CTE %s peut être matérialisée ; envisagez NOT MATERIALIZED si les filtres doivent être poussés vers le bas" % (n.get("rel") or ""), n) if extras.get("planning_ms") and extras.get("execution_ms"): if extras["planning_ms"] > extras["execution_ms"] and extras["planning_ms"] > 20: add("info", "EX10", "Temps de planification %.1f ms > Temps d'exécution %.1f ms" % (extras["planning_ms"], extras["execution_ms"]), None) findings.sort(key=lambda x: (SEV.get(x[0], 9), x[1])) return findings def report(nodes, extras, findings, src): flat = walk(nodes) lines = [] lines.append("# Analyse EXPLAIN Postgres") lines.append("") lines.append("Source : `%s`" % src) if extras.get("execution_ms") is not None: lines.append("Temps d'exécution : %s ms" % extras["execution_ms"]) if extras.get("planning_ms") is not None: lines.append("Temps de planification : %s ms" % extras["planning_ms"]) high = sum(1 for f in findings if f[0] == "high") lines.append("") if high: lines.append("**Verdict : À CORRIGER** - %d constatation(s) élevée(s)." % high) elif findings: lines.append("**Verdict : OK avec notes** - %d constatation(s), aucune élevée." % len(findings)) else: lines.append("**Verdict : OK** - aucune constatation au-dessus des seuils.") lines.append("") lines.append("## Nœuds principaux par temps (ou coût)") lines.append("") scored = [] for n in flat: ms = time_ms(n) scored.append((ms if ms is not None else -1, n["cost_total"], n)) scored.sort(key=lambda x: (x[0] if x[0] >= 0 else -1, x[1]), reverse=True) lines.append("| Nœud | Relation | ms réels (x boucles) | Coût | Lignes estimées -> réelles | Boucles | Lecture partagée |") lines.append("|------|----------|---------------------|------|-----------------|-------|-------------|") for ms, cost, n in scored[:12]: am = ("%.1f" % ms) if ms is not None and ms >= 0 else "-" ra = "%s -> %s" % (n["est_rows"], n["act_rows"] if n["act_rows"] is not None else "-") lines.append("| %s | %s | %s | %.0f | %s | %s | %s |" % ( n["type"], n["rel"] or "-", am, cost, ra, n["loops"] if n["loops"] is not None else "-", n.get("buffers", {}).get("sread", "-"))) lines.append("") lines.append("## Constatations") lines.append("") if not findings: lines.append("Aucune.") else: lines.append("| Gravité | Code | Détail |") lines.append("|-----|------|--------|") for sev, code, msg, _ in findings: lines.append("| %s | %s | %s |" % (sev, code, msg.replace("|", "/"))) lines.append("") lines.append("## Correctifs classés (suggérés)") lines.append("") fixes = [] codes = {f[1] for f in findings} if "EX01" in codes: fixes.append("Ajoutez un index btree/expression qui correspond au Filtre de la séquence séquentielle (vérifiez les wrappers de fonctions comme lower()).") if "EX03" in codes or "EX02" in codes: fixes.append("Réduisez les lignes externes de la boucle imbriquée ou indexez la clé de jointure interne ; revérifiez l'ordre de jointure après ANALYZE.") if "EX05" in codes: fixes.append("Exécutez ANALYZE sur les tables concernées ; envisagez des statistiques étendues pour les colonnes corrélées.") if "EX07" in codes: fixes.append("Coupez les lignes avant Tri/Hachage, ou augmentez work_mem pour cette session uniquement après mesure.") if "EX06" in codes: fixes.append("Réduisez les lectures partagées via un meilleur index ou en filtrant plus tôt pour que la boucle imbriquée ne relise pas les tas.") if "EX11" in codes: fixes.append("Réécrivez pour que LIMIT puisse s'arrêter sur une analyse d'index ordonnée au lieu de trier l'ensemble complet.") if "EX12" in codes: fixes.append("Essayez WITH ... AS NOT MATERIALIZED ou intégrez le CTE pour que les filtres soient poussés vers le bas.") if not fixes: fixes.append("Aucun correctif structurel prioritaire selon les seuils ; gardez le plan sous surveillance pg_stat_statements.") for i, f in enumerate(fixes, 1): lines.append("%d. %s" % (i, f)) lines.append("") return "\n".join(lines) + "\n" def load_plan(path): raw = open(path, encoding="utf-8").read() if path.endswith(".json") or raw.lstrip().startswith(("[", "{")): try: obj = json.loads(raw) except json.JSONDecodeError as e: raise InputError("JSON invalide : %s" % e) return parse_json_plan(obj) return parse_text(raw) def self_test(): tests = [] def t(name, ok): tests.append((name, bool(ok))) sample = """Nested Loop (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1) Buffers: shared hit=1204 read=88440 -> Seq Scan on public.customers c (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1) Filter: (lower((email)::text) = 'ada@example.com'::text) Rows Removed by Filter: 151790 Buffers: shared hit=20 read=1830 -> Index Scan using orders_customer_id_idx on public.orders o (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210) Index Cond: (customer_id = c.id) Buffers: shared hit=1184 read=86610 Planning Time: 0.412 ms Execution Time: 912.610 ms """ nodes, extras = parse_text(sample) t("racine analysée boucle imbriquée", nodes[0]["type"] == "Nested Loop") t("deux enfants", len(nodes[0]["children"]) == 2) t("temps d'exécution", extras["execution_ms"] == 912.610) findings = analyze(nodes, extras) codes = {f[1] for f in findings} t("EX01 séquence séquentielle sélective", "EX01" in codes) t("EX03 explosion de boucle imbriquée", "EX03" in codes) t("EX05 mauvaise estimation", "EX05" in codes) t("EX06 lectures partagées", "EX06" in codes) r = report(nodes, extras, findings, "t") t("verdict du rapport à corriger", "À CORRIGER" in r) tiny = """Seq Scan on public.t (cost=0.00..1.05 rows=5 width=4) (actual time=0.002..0.003 rows=5 loops=1) Execution Time: 0.020 ms """ nodes2, ex2 = parse_text(tiny) f2 = analyze(nodes2, ex2) t("petite table sans élevé", not any(x[0] == "high" for x in f2)) spill = """Sort (cost=1000..1100 rows=10000 width=8) (actual time=50..60 rows=10000 loops=1) Sort Method: external merge Disk: 4096kB """ ns, es = parse_text(spill) fs = analyze(ns, es) t("EX07 tri externe", any(x[1] == "EX07" for x in fs)) j = [{"Plan": {"Node Type": "Limit", "Startup Cost": 0, "Total Cost": 10, "Plan Rows": 10, "Plan Width": 4, "Actual Startup Time": 0.1, "Actual Total Time": 5.0, "Actual Rows": 10, "Actual Loops": 1, "Plans": [{"Node Type": "Sort", "Startup Cost": 0, "Total Cost": 9, "Plan Rows": 5000, "Plan Width": 4, "Actual Startup Time": 0.1, "Actual Total Time": 4.9, "Actual Rows": 5000, "Actual Loops": 1, "Sort Method": "quicksort"}]}, "Planning Time": 1.0, "Execution Time": 5.1}] nj, ej = parse_json_plan(j) fj = analyze(nj, ej) t("JSON limit+tri -> EX11", any(x[1] == "EX11" for x in fj)) try: parse_text("pas un plan") ok = False except InputError: ok = True t("mauvaise entrée rejetée", ok) for name, ok in tests: print(("PASS " if ok else "FAIL ") + name) passed = sum(ok for ok in tests) print("%d/%d réussis" % (passed, len(tests))) return 0 if passed == len(tests) else 1 def main(argv=None): ap = argparse.ArgumentParser(description="Analyse les plans EXPLAIN / EXPLAIN ANALYZE Postgres.") ap.add_argument("plan", nargs="?") ap.add_argument("--min-ms", type=float, default=1.0) ap.add_argument("--json-out", action="store_true") ap.add_argument("--self-test", action="store_true") a = ap.parse_args(argv) if a.self_test: return self_test() if not a.plan: ap.print_usage(sys.stderr) return 2 try: nodes, extras = load_plan(a.plan) findings = analyze(nodes, extras, min_ms=a.min_ms) except (OSError, InputError) as e: print("erreur : %s" % e, file=sys.stderr) return 2 if a.json_out: print(json.dumps({ "execution_ms": extras.get("execution_ms"), "planning_ms": extras.get("planning_ms"), "findings": [{"severity": s, "code": c, "detail": m} for s, c, m, _ in findings], }, indent=2)) else: sys.stdout.write(report(nodes, extras, findings, a.plan)) return 1 if any(f[0] == "high" for f in findings) else 0 if __name__ == "__main__": sys.exit(main()) FILE:templates/slow-query-review.md # Revue de plan de requête lente - Date : - Base de données / version Postgres : - Environnement (réplique prod / staging) : - Nom de la requête ou ticket : ## SQL (court) ```sql ``` ## Verdict - Global : - Sources de temps dominantes : ## Nœuds principaux | Nœud | Temps réel / coût | Lignes estimées -> réelles | Boucles | Tampons | Notes | |------|--------------------|-----------------|-------|---------|-------| | | | | | | | ## Constatations | Code | Gravité | Détail | |------|----------|--------| | | | | ## Correctifs classés 1. 2. 3. ## Suivis - [ ] Capturez `EXPLAIN (ANALYZE, BUFFERS)` après le changement - [ ] Vérifiez `pg_stat_user_indexes` / index inutilisés avant d'en ajouter d'autres - [ ] Notez si ANALYZE sur les tables de base était obsolète