Analisador de Consultas Lentas do EXPLAIN do Postgres

--- name: postgres-explain-slow-query-analyzer description: Lê planos EXPLAIN e EXPLAIN ANALYZE do Postgres (texto ou JSON) e descobre por que uma consulta está lenta - varreduras sequenciais em relações grandes, nested loops com estimativas de linhas enormes, erros de estimativa (estimado vs real), leituras altas de buffers, Sort/Hash que derramam para disco, falta de pushdown de LIMIT e nós caros por tempo real. Classifica correções (índice, reescrita, estatísticas). Analisador Python offline com stdlib. --- # Analisador de Consultas Lentas com EXPLAIN do Postgres Você ajuda desenvolvedores e DBAs a ler planos de consulta do Postgres. Uma consulta lenta raramente é corrigida apenas olhando para o SQL: o plano mostra se o Postgres escolheu uma varredura sequencial, um nested loop que explodiu, uma estimativa de linhas ruim, um Sort que derramou para disco, ou um Hash Join que leu gigabytes de buffers. Você recebe um plano `EXPLAIN` ou `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT|JSON)`, nomeia os nós caros, explica por que eles prejudicam e classifica correções concretas. Esta habilidade é sobre ler planos, não reescrever SQL do zero sem evidências. Você não executa declarações destrutivas. Prefira `EXPLAIN (ANALYZE, BUFFERS)` em uma réplica ou em uma transação que você possa reverter; avise que ANALYZE realmente executa a consulta. ## Arquivos nesta habilidade - `scripts/explain_analyze.py` - analisa saída EXPLAIN / EXPLAIN ANALYZE em texto ou JSON; pontua nós por tempo real ou custo; relata EX01-EX12 (varreduras sequenciais, explosões de nested loop, erros de estimativa, sorts/hashes em disco, leituras compartilhadas altas, nós nunca executados, desequilíbrio paralelo); classifica correções sugeridas (apenas biblioteca padrão Python 3.8+) - `references/plan-nodes-and-buffers.md` - como ler tipos de nó, custos, linhas, loops, buffers e arquivos temporários - `references/checks-and-codes.md` - cada código de descoberta, gravidade e o que fazer, além de dicas de entrada - `templates/slow-query-review.md` - a nota de revisão que você devolve com o trecho do plano e correções classificadas - `examples/orders_by_email.explain.txt` - um EXPLAIN ANALYZE em texto de uma consulta lenta (varredura sequencial + nested loop) - `examples/review-report.md` - a saída real do analisador no exemplo ## Fluxo de trabalho ### 1. Coletar o plano Peça a versão do Postgres, o SQL e um `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` (ou JSON). Se ANALYZE for inseguro em produção, use `EXPLAIN (BUFFERS)` sem ANALYZE e diga que os tempos são apenas estimativas. Capture tamanhos de tabela (`pg_stat_user_tables`, `pg_relation_size`) quando uma Seq Scan parecer suspeita. ### 2. Executar o analisador ``` 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 ``` Saída 0 = nenhuma descoberta alta, 1 = descobertas altas, 2 = entrada ruim. Sem um shell, percorra a árvore do plano de baixo para cima usando `references/plan-nodes-and-buffers.md` e aplique os códigos em `references/checks-and-codes.md`. ### 3. Ler o resultado - Alto primeiro: EX01 Seq Scan em uma relação grande com filtro seletivo, EX03 Nested Loop com linhas reais x loops enormes, EX05 erro de estimativa acima de 100x em um nó quente, EX07 Sort/Hash que derramou para arquivos temporários. - Médio: EX02 chaves de junção sem índice, EX04 Filter removendo a maioria das linhas após uma varredura, EX06 buffers de leitura compartilhada altos, EX08 ramos nunca executados que ainda parecem caros no papel. - Baixo/informação: EX09 workers paralelos desiguais, EX10 tempo de planejamento alto vs execução, EX11 LIMIT não empurrado para baixo, EX12 CTE MATERIALIZED forçando computação completa. ### 4. Classificar correções Prefira mudanças baseadas em evidências: adicione ou ajuste um índice que corresponda ao Filter/Join Filter, atualize estatísticas (`ANALYZE`), reescreva uma subconsulta correlacionada que virou um Nested Loop, aumente `work_mem` apenas quando um Sort/Hash derramou e o conjunto cabe em memória, ou mude uma CTE para `NOT MATERIALIZED` quando o Postgres 12+ a materializou desnecessariamente. Não invente índices sem nomear colunas do plano. ### 5. Entregar Retorne o veredito, os nós mais caros com tempo real e buffers, a lista de descobertas e uma lista curta de correções classificadas usando `templates/slow-query-review.md`. ## Exemplo de resultado No plano de exemplo, o Nested Loop dirigiu 98% do tempo: uma Seq Scan externa em `customers` filtrada por `lower(email)` (sem índice de expressão) alimentou uma Index Scan interna em `orders_customer_id_idx` que executou 48.210 loops. As linhas estimadas na varredura externa eram 42 vs 48.210 reais (erro de estimativa). Buffers de leitura compartilhada eram altos em `orders`. Correções classificadas: índice de expressão em `lower(email)`, depois confirmar seletividade da junção; `ANALYZE customers`. ## Regras - Baseie cada afirmação em um nó do plano (tipo, custo/real, linhas, loops, buffers). - Distinga EXPLAIN apenas com estimativas de ANALYZE; nunca trate custos como milissegundos. - Não recomende desabilitar varreduras sequenciais globalmente ou definir random_page_cost para valores fantasiosos sem medir. - Avise que EXPLAIN ANALYZE executa a consulta (triggers, escritas, locks). - Isto é um auxílio para leitura de planos, não um substituto para monitoramento com `pg_stat_statements` ou uma revisão completa de esquema. 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 # Análise EXPLAIN do Postgres Fonte: `examples/orders_by_email.explain.txt` Tempo de Execução: 912.61 ms Tempo de Planejamento: 0.412 ms **Veredito: PRECISA DE TRABALHO** - 3 descoberta(s) alta(s). ## Principais nós por tempo (ou custo) | Nó | Relação | ms reais (x loops) | Custo | Linhas est -> real | Loops | Leitura compartilhada | |------|----------|---------------------|------|-----------------|-------|-------------| | Nested Loop | - | 912.4 | 184220 | 12 -> 96 | 1 | 88440 | | Index Scan | public.orders | 771.4 | 4 | 8 -> 0 | 48210 | 86610 | | Seq Scan | public.customers | 88.2 | 1850 | 42 -> 48210 | 1 | 1830 | ## Descobertas | Sev | Código | Detalhe | |-----|------|--------| | alta | EX01 | Seq Scan em public.customers com Filter seletivo ((lower((email)::text) = 'ada@example.com'::text)); removeu 151790 linhas | | alta | EX03 | Index Scan filho do Nested Loop executou loops=48210 (linhas reais/loop~0) consumindo ~771.4 ms | | alta | EX05 | Erro de estimativa na Seq Scan public.customers: linhas est=42 real=48210 (razão~1148x) | | média | EX02 | Nested Loop tem Seq Scan interna/externa em public.customers | | média | EX06 | Nested Loop ? leitura compartilhada=88440 blocos | | média | EX06 | Index Scan public.orders leitura compartilhada=86610 blocos | ## Correções classificadas (sugeridas) 1. Adicione um índice btree/expressão que corresponda ao Filter da Seq Scan (verifique wrappers de função como lower()). 2. Reduza as linhas externas do Nested Loop ou indexe a chave de junção interna; re-verifique a ordem da junção após ANALYZE. 3. Execute ANALYZE nas tabelas tocadas; considere estatísticas estendidas para colunas correlacionadas. 4. Reduza leituras compartilhadas com um índice melhor ou filtrando antes para que o Nested Loop não releia heaps. FILE:references/checks-and-codes.md # Verificações e códigos | Código | Gravidade | Significado | O que fazer | |------|----------|---------|------------| | EX01 | alta | Seq Scan em uma relação com Filter seletivo e muitas linhas reais ou tempo real alto | Índice de expressão ou btree correspondendo ao filtro; evite envolver a coluna em uma função sem um índice de expressão correspondente | | EX02 | média | Condição de junção parece sem índice (Seq Scan interna do Nested Loop ou loops altos com Filter na chave de junção) | Indexe a chave de junção interna; confirme que os tipos de dados correspondem (sem cast que mate o índice) | | EX03 | alta | Nested Loop com loops muito altos * linhas reais (explosão de linhas) | Reduza as linhas externas primeiro; considere Hash/Merge Join; corrija erros de estimativa que levam à escolha do NL | | EX04 | média | Filter remove a maioria das linhas após uma varredura (linhas entrando >> linhas saindo) | Empurre o predicado para uma condição de índice; reescreva para que o planejador possa usar um índice | | EX05 | alta | Erro de estimativa: real/estimado ou estimado/real >= 100 em um nó que importa | ANALYZE; estatísticas estendidas para colunas correlacionadas; evite funções não imutáveis em predicados | | EX06 | média | Buffers de leitura compartilhada altos em um nó quente | Cache/índice para reduzir buscas de heap; verifique se o Nested Loop rele as mesmas páginas | | EX07 | alta | Sort ou Hash derramou para arquivos temporários (merge externo / Batches > 1 com temporário escrito) | Reduza linhas antes do sort/hash; aumente work_mem com cuidado para esta sessão de consulta; adicione índice de suporte para evitar sort | | EX08 | baixa | Nó nunca executado (loops reais=0) mas domina o custo estimado | Ignore para tempo de execução; útil ao comparar planos sem ANALYZE | | EX09 | baixa | Consulta paralela com contribuição desigual dos workers | Verifique configurações parallel_* e se o gather vale a pena | | EX10 | informação | Tempo de planejamento grande vs tempo de execução | Declarações preparadas; simplifique planejamento único; problemas de poda de partição | | EX11 | média | LIMIT acima de um Sort completo ou agregação completa de um conjunto grande | Reescreva para que order/filtro permita uma varredura de índice com parada antecipada | | EX12 | média | CTE Scan com MATERIALIZED forçando avaliação completa | WITH ... AS NOT MATERIALIZED (PG12+) ou inline a CTE | ## Dicas de entrada - Planos em texto: cole a árvore completa; não remova a indentação. - Planos JSON: array `FORMAT JSON` do Postgres com um objeto de plano; o script aceita um array bruto ou `{Plan: ...}`. - Linhas de cabeçalho opcionais como `QUERY PLAN` ou regras tracejadas são ignoradas. FILE:references/plan-nodes-and-buffers.md # Nós de plano e buffers ## Capturando um plano útil ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) <query>; -- ou EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>; ``` Em produção, prefira um standby quente ou envolva escritas em uma transação que você reverta. `ANALYZE` executa a declaração. ## Lendo uma linha de nó em texto Exemplo: `Seq Scan on public.customers (cost=0.00..1550.00 rows=42 width=64) (actual time=0.020..85.110 rows=48210 loops=1)` - Tipo de nó e relação - `cost=startup..total` (unidades do planejador, não ms) - `rows` = linhas estimadas; sob ANALYZE também `actual time` e `rows` e `loops` - Tempo total para aquele nó ~= (tempo total real) * loops para o padrão de contribuição da subárvore; o tempo da folha é mais claro que o do pai quando os pais incluem filhos ## Tipos comuns de nó - **Seq Scan**: lê o heap inteiro. Bom para tabelas pequenas ou quando a maioria das linhas corresponde; doloroso com um Filter seletivo em uma tabela grande. - **Index Scan / Index Only Scan / Bitmap Heap Scan**: bom quando seletivo. Bitmap Heap com muitas re-checagens ainda pode doer. - **Nested Loop**: para cada linha externa, execute o interno. Ótimo para poucas linhas externas + interno indexado; catastrófico quando o externo é grande. - **Hash Join / Merge Join**: melhor para conjuntos maiores; cuidado com Hash que derrama (`Batches` > 1, temporário escrito). - **Sort**: `Sort Method: external merge` significa derramamento para disco; considere `work_mem` ou menos linhas antes. - **Aggregate / GroupAggregate / HashAggregate**: verifique se as colunas de agrupamento são indexadas ou pré-filtradas. - **CTE Scan**: Postgres 12+ pode MATERIALIZAR CTEs; pode bloquear pushdown de filtro. - **Limit**: deve ficar acima de um caminho barato; se Limit está acima de um sort completo de tudo, empurre os critérios para baixo. ## Buffers (opção BUFFERS) - `shared hit` = encontrado em buffers compartilhados (barato) - `shared read` = lido do SO/disco para buffers (caro sob falta de cache) - `temp read/written` = arquivos de derramamento de work_mem Um nó com `shared read` enorme no lado interno repetido de um Nested Loop geralmente precisa de um filtro externo ou índice melhor. ## Erros de estimativa de linhas Compare `rows` (estimado) com `rows` real. Razões acima de 10x (especialmente 100x+) em nós caros significam estatísticas ruins, `ANALYZE` desatualizado, colunas correlacionadas, ou uma função envolvendo uma coluna indexada (`lower(email)`). ## O que esta habilidade não faz Não substitui `pg_stat_statements`, auto_explain, ou revisões de design de índice que começam apenas do esquema. Começa de um plano e explica esse plano. FILE:scripts/explain_analyze.py #!/usr/bin/env python3 """Analisador de plano EXPLAIN / EXPLAIN ANALYZE do Postgres (apenas stdlib, Python 3.8+). Analisa planos em texto ou JSON, pontua nós caros e relata descobertas EX01-EX12 focadas em ler o plano (varreduras sequenciais, explosões de nested loop, erros de estimativa, pressão de buffer, derramamentos de disco). Uso: explain_analyze.py PLAN.txt|.json [--min-ms N] [--json-out] explain_analyze.py --self-test Saída: 0 = nenhuma descoberta alta, 1 = descobertas altas, 2 = entrada ruim. """ import argparse, json, re, sys SEV = {"alta": 0, "média": 1, "baixa": 2, "informação": 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("nenhum nó de plano encontrado (precisa de texto EXPLAIN com cost=... rows=...)") return nodes, extras def parse_json_plan(obj): if isinstance(obj, list): if not obj: raise InputError("array de plano JSON vazio") obj = obj[0] if not isinstance(obj, dict): raise InputError("plano JSON deve ser um objeto ou array de objetos") plan = obj.get("Plan") or obj.get("plan") if not plan: raise InputError("JSON sem objeto 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", "Desconhecido"), "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("alta", "EX01", "Seq Scan em %s com Filter seletivo (%s); removeu %s linhas" % (rel, n["filter"], n.get("removed")), n) if t.startswith("Nested Loop"): for c in n["children"]: if c["type"].startswith("Seq Scan"): add("média", "EX02", "Nested Loop tem Seq Scan interna/externa em %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("alta", "EX03", "Filho %s do Nested Loop executou loops=%d (linhas reais/loop~%s) consumindo ~%.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("média", "EX04", "Filter em %s %s remove %.0f%% das linhas (%d removidas, %d mantidas)" % ( 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("alta", "EX05", "Erro de estimativa em %s %s: linhas est=%d real=%d (razão~%.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("média", "EX06", "%s %s leitura compartilhada=%d blocos" % (t, rel, sread), n) sm = (n.get("sort_method") or "").lower() if "external" in sm: add("alta", "EX07", "Sort derramou para disco (%s) em %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("alta", "EX07", "Hash derramou: Batches=%d temporário escrito=%s" % (n["hash_batches"], n["buffers"].get("twrite")), n) if loops == 0 and n["cost_total"] >= 1000: add("baixa", "EX08", "Nó %s nunca executado mas custo=%.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("média", "EX11", "LIMIT acima de Sort de conjunto grande (%s linhas)" % (c.get("act_rows") or c.get("est_rows")), n) if "CTE" in t and (ms or n["cost_total"] > 1000): add("média", "EX12", "CTE Scan %s pode estar materializada; considere NOT MATERIALIZED se filtros devem ser empurrados para baixo" % (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("informação", "EX10", "Tempo de Planejamento %.1f ms > Tempo de Execução %.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("# Análise EXPLAIN do Postgres") lines.append("") lines.append("Fonte: `%s`" % src) if extras.get("execution_ms") is not None: lines.append("Tempo de Execução: %s ms" % extras["execution_ms"]) if extras.get("planning_ms") is not None: lines.append("Tempo de Planejamento: %s ms" % extras["planning_ms"]) high = sum(1 for f in findings if f[0] == "alta") lines.append("") if high: lines.append("**Veredito: PRECISA DE TRABALHO** - %d descoberta(s) alta(s)." % high) elif findings: lines.append("**Veredito: OK COM NOTAS** - %d descoberta(s), nenhuma alta." % len(findings)) else: lines.append("**Veredito: OK** - nenhuma descoberta acima dos limites.") lines.append("") lines.append("## Principais nós por tempo (ou custo)") 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ó | Relação | ms reais (x loops) | Custo | Linhas est -> real | Loops | Leitura compartilhada |") 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("## Descobertas") lines.append("") if not findings: lines.append("Nenhuma.") else: lines.append("| Sev | Código | Detalhe |") lines.append("|-----|------|--------|") for sev, code, msg, _ in findings: lines.append("| %s | %s | %s |" % (sev, code, msg.replace("|", "/"))) lines.append("") lines.append("## Correções classificadas (sugeridas)") lines.append("") fixes = [] codes = {f[1] for f in findings} if "EX01" in codes: fixes.append("Adicione um índice btree/expressão que corresponda ao Filter da Seq Scan (verifique wrappers de função como lower()).") if "EX03" in codes or "EX02" in codes: fixes.append("Reduza as linhas externas do Nested Loop ou indexe a chave de junção interna; re-verifique a ordem da junção após ANALYZE.") if "EX05" in codes: fixes.append("Execute ANALYZE nas tabelas tocadas; considere estatísticas estendidas para colunas correlacionadas.") if "EX07" in codes: fixes.append("Corte linhas antes de Sort/Hash, ou aumente work_mem para esta sessão apenas após medir.") if "EX06" in codes: fixes.append("Reduza leituras compartilhadas com um índice melhor ou filtrando antes para que o Nested Loop não releia heaps.") if "EX11" in codes: fixes.append("Reescreva para que LIMIT possa parar em uma varredura de índice ordenada em vez de ordenar o conjunto completo.") if "EX12" in codes: fixes.append("Tente WITH ... AS NOT MATERIALIZED ou inline a CTE para que filtros sejam empurrados para baixo.") if not fixes: fixes.append("Nenhuma correção estrutural de alta prioridade a partir dos limites; mantenha o plano sob observação do 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 inválido: %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("nested loop raiz analisado", nodes[0]["type"] == "Nested Loop") t("dois filhos", len(nodes[0]["children"]) == 2) t("tempo de execução", extras["execution_ms"] == 912.610) findings = analyze(nodes, extras) codes = {f[1] for f in findings} t("EX01 seq scan seletiva", "EX01" in codes) t("EX03 explosão nested loop", "EX03" in codes) t("EX05 erro de estimativa", "EX05" in codes) t("EX06 leituras compartilhadas", "EX06" in codes) r = report(nodes, extras, findings, "t") t("veredito do relatório precisa de trabalho", "PRECISA DE TRABALHO" 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("tabela pequena sem alta", not any(x[0] == "alta" 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 sort externo", 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+sort -> EX11", any(x[1] == "EX11" for x in fj)) try: parse_text("não é um plano") ok = False except InputError: ok = True t("entrada ruim rejeitada", ok) for name, ok in tests: print(("PASS " if ok else "FAIL ") + name) passed = sum(ok for ok in tests) print("%d/%d passaram" % (passed, len(tests))) return 0 if passed == len(tests) else 1 def main(argv=None): ap = argparse.ArgumentParser(description="Analisa planos EXPLAIN / EXPLAIN ANALYZE do 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("erro: %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] == "alta" for f in findings) else 0 if __name__ == "__main__": sys.exit(main()) FILE:templates/slow-query-review.md # Revisão de plano de consulta lenta - Data: - Banco / versão do Postgres: - Ambiente (réplica de produção / staging): - Nome da consulta ou ticket: ## SQL (curto) ```sql ``` ## Veredito - Geral: - Sumidouros de tempo dominantes: ## Principais nós | Nó | Tempo real / custo | Linhas est -> real | Loops | Buffers | Notas | |------|--------------------|-----------------|-------|---------|-------| | | | | | | | ## Descobertas | Código | Gravidade | Detalhe | |------|----------|--------| | | | | ## Correções classificadas 1. 2. 3. ## Acompanhamentos - [ ] Capture `EXPLAIN (ANALYZE, BUFFERS)` após a mudança - [ ] Verifique `pg_stat_user_indexes` / índices não utilizados antes de adicionar mais - [ ] Anote se ANALYZE nas tabelas base estava desatualizado