Como o EXPLAIN Funciona
EXPLAIN é a janela do PostgreSQL para a tomada de decisão do planejador de consultas, mostrando o plano que ele escolheu sem necessariamente executá-lo.
Toda instrução SQL que você envia ao PostgreSQL passa por um planejador que considera várias maneiras de executá-la e escolhe a que ele acredita ser a mais barata, e EXPLAIN é como você inspeciona essa escolha em vez de adivinhar.
Entender como EXPLAIN funciona conceitualmente, não apenas quais sinalizadores passar, é o que permite que você leia um plano e saiba imediatamente se o planejador raciocinou corretamente sobre seus dados.
Resumo
EXPLAINimprime a árvore de planos que o planejador de consultas selecionou, juntamente com custos estimados ou, comANALYZE, comportamento medido real.- Por que Importa: Problemas de desempenho de consulta são quase sempre problemas de plano, e o plano é invisível até que você o solicite.
- Conceitos Chave: árvore de planos, estimativa de custo, nó de plano, estimativa de linhas, tempo real, planejador vs. executor.
- Quando Usar: Diagnosticar uma consulta lenta, validar se um índice está sendo realmente usado, comparar duas formas de consulta ou investigar uma regressão após uma mudança nos dados ou estatísticas.
- Limitações / Compromissos:
EXPLAINmostra um único plano escolhido, não todos os planos que o otimizador considerou, eANALYZEaltera as características de tempo adicionando sobrecarga de instrumentação. - Tópicos Relacionados: estatísticas do planejador de consultas, seleção de varredura de índice, algoritmos de junção, cache de planos.
Fundamentos
Em sua essência, EXPLAIN pede ao planejador para fazer tudo o que normalmente faz, exceto pular a etapa final de execução.
O planejador analisa sua consulta, considera os caminhos de acesso disponíveis (varreduras sequenciais, varreduras de índice, várias ordens de junção), atribui um custo numérico a cada um e retorna a combinação mais barata como uma árvore de nós.
Essa árvore é o "plano", e cada nó nela representa uma operação, como varrer uma tabela, juntar dois conjuntos de linhas ou classificar um resultado.
Um nó na parte inferior da árvore alimenta linhas para o nó acima dele, muito parecido com uma pequena linha de montagem de fábrica, onde cada estação só conhece sua saída imediata.
Quando você executa EXPLAIN simples, o PostgreSQL imprime essa árvore juntamente com o custo estimado e a contagem de linhas estimada para cada nó, mas nunca executa sua instrução.
Essa distinção importa enormemente na prática, pois significa que EXPLAIN sozinho é sempre seguro para executar em produção, mesmo para um DELETE ou UPDATE.
EXPLAIN ANALYZE, por outro lado, executa a instrução, percorrendo o plano real e registrando o tempo real e as contagens de linhas ao lado das estimativas originais.
É por isso que EXPLAIN ANALYZE em uma instrução de escrita realmente executa a escrita, que é a maneira mais comum pela qual as pessoas acidentalmente alteram dados enquanto "apenas verificam um plano".
Os dois números estimados que você vê em cada nó, custo e linhas, vêm do modelo de custo do planejador, uma fórmula que combina custos de acesso a página, custos de CPU por linha e as estatísticas conhecidas da tabela.
Esses custos são números sem unidade calibrados contra seq_page_cost (a linha de base), não milissegundos, então um custo de 1000 não é "1000 de algo real", é apenas significativo em comparação com o custo de um plano concorrente.
Mecânica e Interações
O planejador não avalia todos os planos concebíveis para uma consulta; ele poda o espaço de busca usando heurísticas e programação dinâmica sobre a ordem de junção, especialmente à medida que o número de tabelas unidas aumenta.
Para cada caminho de acesso candidato, ele consulta as estatísticas da tabela, armazenadas em pg_stats, para estimar quantas linhas sobreviverão a uma determinada cláusula WHERE, e essa estimativa de linha então alimenta todos os cálculos de custo acima dela na árvore.
Esta é a conexão crítica que liga EXPLAIN à seção de estatísticas do planejador deste site: uma estimativa de linha incorreta na parte inferior da árvore se propaga para cima e pode produzir uma forma de plano catastroficamente incorreta várias junções depois.
Cada tipo de nó de plano tem sua própria fórmula de custo; uma varredura sequencial custa aproximadamente o número de páginas vezes seq_page_cost mais linhas vezes cpu_tuple_cost, enquanto uma varredura de índice adiciona uma penalidade de aleatoriedade por tupla via random_page_cost para as buscas de página não sequenciais.
Quando você adiciona ANALYZE, o executor instrumenta cada nó com um temporizador e um contador de linhas, de modo que o que você vê como actual time=0.012..4.531 é a janela de tempo real medida durante a qual aquele nó específico foi executado, incluindo o tempo gasto em seus filhos.
Essa contabilidade pai-inclui-filho é uma fonte frequente de leitura incorreta: o actual time de um nó parece alarmante até você perceber que a maior parte dele pertence a um nó filho abaixo dele, não ao trabalho desse nó em si.
BUFFERS adiciona um segundo canal de instrumentação independente que conta as páginas tocadas nos buffers compartilhados versus as páginas que tiveram que ser lidas do sistema operacional ou disco, o que é um sinal muito mais estável em execuções repetidas do que apenas o tempo.
A instrumentação de tempo em si não é gratuita; em consultas muito rápidas e de alta frequência, a sobrecarga de chamar o relógio do sistema milhões de vezes pode inflar visivelmente os números do ANALYZE em relação ao custo real não medido da consulta.
É aqui também que o cache de planos interage com o que você observa: um plano genérico construído para uma instrução preparada pode diferir do plano único construído para um valor literal, porque o planejador às vezes escolhe um plano personalizado por execução e só retorna a um plano genérico após repetições suficientes.
Nós de consulta paralela complicam ainda mais a contabilidade, pois Workers Launched e actual time por trabalhador são relatados separadamente, e o processo líder também pode fazer uma parte do trabalho.
-- O padrão de dois números que você lê em cada nó: estimativa vs. real
-- rows=<palpite do planejador> actual rows=<o que realmente saiu>
-- Uma grande lacuna aqui é o sinal mais confiável de um problema de estatísticas
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'shipped';
-- ^ estimativa alimenta cada junção acima deste nóConsiderações Avançadas e Aplicações
Em escala, o custo de executar EXPLAIN ANALYZE em si se torna uma consideração de design, pois instrumentar uma consulta que varre centenas de milhões de linhas pode adicionar sobrecarga significativa além do tempo de execução da própria consulta.
auto_explain aborda isso permitindo que o servidor registre planos automaticamente para consultas que excedem um limite de duração, sem que você precise capturar uma consulta lenta interativamente.
A compilação JIT, habilitada por padrão para planos suficientemente caros, adiciona seus próprios itens de linha à saída do EXPLAIN, e um plano que parece barato em tempo de execução bruto ainda pode mostrar sobrecarga de compilação JIT não trivial para consultas simples e executadas com frequência.
A estabilidade do plano entre as versões do PostgreSQL não é garantida, pois melhorias no planejador entre as principais versões podem alterar as escolhas de nós, constantes de custo ou até mesmo introduzir novos tipos de nós, portanto, um plano de linha de base capturado deve sempre ser fixado em uma versão específica ao ser usado para comparação de regressão.
O estado do cache é outra armadilha avançada: o primeiro EXPLAIN ANALYZE após uma reinicialização do servidor ou uma grande alteração nos dados lê principalmente do disco, enquanto uma segunda execução contra a mesma consulta se beneficia de um cache de buffers compartilhados aquecido, e comparar uma execução fria com uma execução aquecida produz conclusões enganosas sobre a latência do mundo real da consulta.
Pipelines de observabilidade de produção normalmente combinam três ferramentas complementares em vez de depender apenas do EXPLAIN interativo, pois cada uma responde a uma pergunta diferente sobre o comportamento do plano ao longo do tempo.
| Ferramenta | Força | Fraqueza | Melhor Ajuste |
|---|---|---|---|
EXPLAIN (sem ANALYZE) | Risco zero de execução, sempre seguro em produção | Sem tempo real ou verdade sobre linhas | Verificação rápida de sanidade de uma escolha de caminho de acesso |
EXPLAIN (ANALYZE, BUFFERS) | Tempo real, contagens de linhas e I/O por nó | Executa a consulta; adiciona sobrecarga de instrumentação | Diagnóstico profundo e único de uma consulta lenta específica |
auto_explain | Captura planos automaticamente acima de um limite de duração | Adiciona volume de log; requer acesso à configuração do servidor | Capturar consultas lentas intermitentes que você não consegue reproduzir sob demanda |
pg_stat_statements | Custo agregado e contagens de chamadas em todas as consultas | Sem árvore de planos ou detalhes por nó | Encontrar qual texto de consulta merece um EXPLAIN detalhado |
Conceitos Errôneos Comuns
- "Um número de custo mais alto significa que a consulta é lenta." - Custo é uma estimativa relativa e sem unidade do planejador, calibrada para comparação de planos, não uma previsão de milissegundos.
- "EXPLAIN sempre executa minha consulta." - O
EXPLAINsimples nunca executa nada; apenasEXPLAIN ANALYZEfaz isso, e essa distinção é crucial para a segurança de instruções de escrita. - "O plano que vejo hoje é o plano que sempre será executado." - Planos são reconstruídos por execução (ou por um limite de plano genérico para instruções preparadas) e podem mudar à medida que estatísticas, volume de dados ou configurações mudam.
- "O tempo real de um nó é todo o seu próprio trabalho." - O
actual timerelatado em qualquer nó inclui todo o tempo gasto dentro de seus nós filhos abaixo dele na árvore. - "Os números do EXPLAIN ANALYZE são o custo real e não medido da consulta." - A instrumentação de tempo e buffer adiciona sua própria sobrecarga, que geralmente é pequena, mas não zero, especialmente em consultas muito rápidas.
- "Um nó superior de baixo custo significa que toda a consulta é rápida." - O trabalho mais caro muitas vezes se esconde vários níveis abaixo dentro de um loop aninhado ou uma subconsulta repetida, não no topo da árvore.
FAQs
Qual é a diferença real entre EXPLAIN e EXPLAIN ANALYZE?
EXPLAIN sozinho mostra o plano escolhido pelo planejador e suas estimativas de custo/linhas sem executar a consulta.
EXPLAIN ANALYZE executa a instrução e adiciona tempo real medido e contagens de linhas a cada nó na mesma árvore de planos.
É sempre inseguro executar EXPLAIN simples em um banco de dados de produção?
Não, EXPLAIN simples nunca executa a instrução subjacente, portanto, não há risco de alterar dados ou manter bloqueios de longa duração pela execução.
O único custo real é o tempo de planejamento (geralmente trivial) em si.
Por que as contagens de linhas estimadas e reais em um nó às vezes diferem drasticamente?
A estimativa vem do modelo de custo baseado em estatísticas do planejador, que pode estar incorreto devido a estatísticas desatualizadas, colunas correlacionadas ou distribuições de dados distorcidas.
Uma grande lacuna entre linhas estimadas e reais é o sinal mais forte de que as suposições do planejador sobre seus dados não são mais válidas.
O que os números de custo como cost=0.29..8.31 realmente representam?
Eles são o custo de inicialização estimado pelo planejador e o custo total em termos abstratos e sem unidade, calibrados contra seq_page_cost.
Eles só são significativos ao comparar dois planos candidatos entre si, não como uma previsão de tempo de relógio.
Por que o tempo real de um nó parece maior do que eu esperava para uma operação tão simples?
O actual time relatado em qualquer nó inclui todo o tempo gasto dentro de seus nós filhos, não apenas o trabalho desse nó.
Leia a árvore de baixo para cima para encontrar onde o tempo está sendo genuinamente gasto.
Adicionar BUFFERS ao EXPLAIN torna a consulta ainda mais lenta?
A contabilidade de buffers adiciona uma quantidade pequena e geralmente negligenciável de sobrecarga em comparação com a instrumentação de tempo do ANALYZE.
Geralmente vale a pena incluir sempre que você usa ANALYZE, pois ele distingue acertos de cache de leituras reais de disco.
A mesma consulta pode produzir dois planos diferentes em duas execuções diferentes?
Sim, se ela usar uma instrução preparada, o planejador pode alternar entre um plano personalizado construído para valores de parâmetro específicos e um plano genérico construído sem eles.
Os planos também podem mudar legitimamente se as estatísticas, o tamanho da tabela ou as configurações de configuração mudarem entre as execuções.
Por que o primeiro EXPLAIN ANALYZE após uma reinicialização parece muito pior?
Um cache de buffers compartilhados frio força mais páginas a serem lidas do disco ou do cache do sistema operacional em vez de serem servidas da memória do PostgreSQL.
Execute a mesma consulta uma segunda vez antes de tirar conclusões sobre o desempenho em estado estacionário.
O EXPLAIN me informa sobre esperas de bloqueio ou contenção?
Não, a saída do EXPLAIN descreve a forma do plano e o tempo, não as esperas de concorrência de outras sessões.
Verifique pg_stat_activity e sua coluna wait_event_type para atrasos relacionados a bloqueios separadamente.
O que JIT está fazendo na minha saída EXPLAIN?
A compilação just-in-time gera código nativo para expressões e pode aparecer como sua própria linha de tempo quando o plano é caro o suficiente para acioná-la.
É uma parte real do tempo total de execução e vale a pena notar em consultas que são executadas com muita frequência, mas individualmente permanecem baratas.
Por que um plano que funcionou bem por meses de repente ficou lento?
A distribuição de dados subjacente provavelmente mudou o suficiente para que as estatísticas em cache do planejador não descrevam mais a realidade com precisão.
Uma nova execução de ANALYZE, ou em casos mais complexos estatísticas estendidas, geralmente restaura um plano sensato.
Um Seq Scan é sempre um sinal de um índice ausente?
Não necessariamente, pois uma varredura sequencial pode ser a opção genuinamente mais barata quando uma consulta toca uma grande fração de uma tabela pequena ou de tamanho moderado.
Se é um problema depende do tamanho da tabela e da seletividade da consulta, não apenas do nome do nó.
Como a saída EXPLAIN em formato JSON difere do formato TEXT?
Ambos descrevem a árvore de planos idêntica e os números idênticos, mas JSON é estruturado para ferramentas, enquanto TEXT é formatado para humanos lendo um terminal ou ticket.
Ferramentas de comparação de planos e visualização geralmente esperam JSON como entrada.
Relacionados
- Noções Básicas de EXPLAIN - a receita prática para executar EXPLAIN com sinalizadores comuns
- Seq Scan vs Index Scan - como o planejador escolhe entre esses dois caminhos de acesso
- Nested Loop, Hash Join, Merge Join - os tipos de nós de junção que você verá em uma árvore de planos
- Estudos de Caso de Planos Ruins - exemplos reais de discrepâncias entre estimativa e real
- Noções Básicas de Estatísticas - por que as estimativas de linhas que alimentam o EXPLAIN às vezes estão erradas
Versões da Pilha: Esta página foi escrita para PostgreSQL 18.4 (linha estável 18, linha de manutenção 17); a mecânica geral do EXPLAIN e do planejador descrita aqui são estáveis em versões principais recentes.