Dominando EXPLAIN ANALYZE: Guia de Otimização de Planos de Consulta PostgreSQL

EXPLAIN ANALYZE é o que eu uso quando uma consulta PostgreSQL parece lenta e os palpites comuns não são mais suficientes. Talvez a consulta pareça inofensiva no código da aplicação. Talvez a tabela tenha um índice e todos assumam que o banco de dados deve estar usando-o. Talvez a consulta seja rápida em homologação, mas lenta em produção. O plano é onde essas suposições se confirmam ou desmoronam.

O hábito útil é ler o plano como uma história do trabalho realizado pelo PostgreSQL: quais linhas ele esperava tocar, quais linhas ele realmente tocou, onde ele fez junções, onde ordenou, se permaneceu na memória e se teve que ler do disco. Você não precisa memorizar todos os nós do plano antes que isso se torne útil. Você precisa desacelerar e comparar estimativas com a realidade.

Entendendo EXPLAIN vs. EXPLAIN ANALYZE

A diferença entre EXPLAIN e EXPLAIN ANALYZE é importante porque um é uma previsão e o outro é uma medição.

EXPLAIN

Quando você executa uma consulta prefixada com EXPLAIN, o PostgreSQL gera o plano de execução pretendido sem realmente executar a consulta. Isso é útil para:

• Visualizar o plano: Você pode ver o que o PostgreSQL espera ser a maneira mais barata de executar sua consulta.
• Estimar custos: Ele fornece estimativas de custo para cada nó no plano, dando uma ideia relativa do uso de recursos.

Exemplo:

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE vai um passo além. Ele não apenas mostra a execução planejada, mas também executa a consulta e depois relata as estatísticas reais de execução. Isso significa que você obtém:

• Tempos reais de execução: Quanto tempo cada etapa realmente levou.
• Contagens reais de linhas: Quantas linhas foram realmente processadas em cada nó.
• Confirmação das estimativas: Você pode comparar as contagens estimadas de linhas com as reais para ver se o planejador do PostgreSQL está fazendo previsões precisas.

Isso torna o EXPLAIN ANALYZE a melhor ferramenta para ajuste real, mas tem uma aresta afiada: ele executa a consulta. Um SELECT ainda pode ser caro porque pode escanear muitos dados, travar ou competir por cache. Um UPDATE, DELETE ou INSERT realmente modificará os dados, a menos que você os envolva em uma transação e os reverta:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

Esse padrão é útil em uma janela de manutenção ou em uma cópia de homologação. Não é um passe livre para executar instruções perigosas em um banco de dados de produção ocupado.

Exemplo:

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

Decodificando a Saída do EXPLAIN ANALYZE

A saída do EXPLAIN ANALYZE pode parecer densa no início, mas entender seus principais componentes é fundamental.

Componentes Principais:

• Tipo de Nó: Identifica a operação sendo executada (por exemplo, Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Aggregate).
• Custo: Apresentado como (custo_inicial .. custo_total).
  • custo_inicial: O custo para recuperar a primeira linha.
  • custo_total: O custo para recuperar todas as linhas.
  • Nota: Os custos são unidades arbitrárias usadas para comparação, não tempo ou memória diretamente.
• Linhas: O número estimado de linhas que o planejador espera retornar deste nó.
• Largura: A largura média estimada (em bytes) das linhas retornadas por este nó.
• Tempo Real: Apresentado como (tempo_inicial .. tempo_total). Este é o tempo real em milissegundos para executar este nó.
  • tempo_inicial: Tempo real para retornar a primeira linha.
  • tempo_total: Tempo real para retornar todas as linhas.
• Linhas Reais: O número real de linhas retornadas por este nó.
• Loops: O número de vezes que este nó foi executado. Para nós de nível superior, geralmente é 1. Para operações aninhadas, pode ser maior.

Exemplo de Interpretação de Saída:

Vamos considerar um exemplo simplificado de um Seq Scan (Varredura Sequencial) em uma tabela grande:

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

Interpretação:

• Seq Scan on users: O banco de dados está lendo cada linha na tabela users.
• cost=0.00..15000.00: O planejador estimou o custo total em cerca de 15000 unidades.
• rows=1000000: O planejador estimou que havia 1 milhão de linhas na tabela.
• actual time=0.020..150.500: Na verdade, levou 150,5 milissegundos para concluir a varredura e o filtro.
• rows=950000: Na verdade, retornou 950.000 linhas (após a filtragem).
• loops=1: Esta varredura foi executada uma vez.
• Filter: (registration_date > '2023-01-01'): Esta é a condição aplicada para filtrar linhas.
• Rows Removed by Filter: 50000: 50.000 linhas foram descartadas pelo filtro.

Identificação de gargalos: Não procure apenas pelo maior actual time. Procure também por um nó que é executado muitas vezes. Uma varredura interna de loop aninhado que leva 0,2 ms pode parecer inofensiva até que loops=50000. Nesse caso, o custo real é aproximadamente o tempo por loop multiplicado pela contagem de loops.

Leia de Dentro para Fora

Os planos do PostgreSQL são árvores. O nó superior retorna o resultado final para o cliente, mas o trabalho geralmente começa mais profundamente no plano. Quando uma consulta junta orders, customers e order_items, a linha superior pode ser um Aggregate, mas a dor real pode ser uma varredura ou junção abaixo dele.

Eu geralmente leio um plano nesta ordem:

1. Comece pelos nós de varredura mais profundos e pergunte: o PostgreSQL leu muito mais linhas do que a consulta retorna?
2. Compare as rows estimadas com as rows reais.
3. Verifique se os nós caros têm loops altos.
4. Procure por nós Sort, Hash ou Materialize que transbordam para o disco.
5. Use BUFFERS para decidir se a consulta é principalmente trabalho de CPU/cache ou I/O de disco.

Aqui está um exemplo comum:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Se você vir uma varredura sequencial sobre milhões de linhas orders, depois uma ordenação, depois um limite, o banco de dados está fazendo muito trabalho antes de poder retornar as 20 linhas que você pediu. Um índice prático poderia ser:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

Depois disso, um bom plano pode usar o índice para ir diretamente aos pedidos mais recentes para aquele cliente e parar após 20 linhas. O plano exato depende do tamanho da tabela, estatísticas, versão do PostgreSQL e distribuição de dados, mas o princípio é estável: combine o índice com o padrão de filtro e ordenação que você realmente usa.

Nós Comuns de Planos de Consulta e Estratégias de Otimização

Entender os diferentes tipos de nós e como otimizá-los é fundamental para dominar o desempenho de consultas.

1. Varredura Sequencial (Seq Scan)

• O que é: Lê cada linha da tabela. Isso geralmente é ineficiente para tabelas grandes, especialmente ao filtrar em condições específicas.
• Quando é aceitável: Para tabelas pequenas, ou quando você precisa recuperar uma grande porcentagem das linhas da tabela. Uma varredura sequencial não é automaticamente ruim.
• Otimização: Crie um índice nas colunas de filtro seletivas, mas verifique com o plano. Se um predicado retorna a maior parte da tabela, o PostgreSQL pode corretamente continuar usando uma varredura sequencial.

2. Varredura de Índice (Index Scan)

• O que é: Usa um índice para encontrar as linhas que correspondem à cláusula WHERE. O PostgreSQL percorre o índice e depois busca as linhas correspondentes da tabela.
• Otimização: Garanta que o índice corresponda à forma da consulta. Para um índice composto, a ordem das colunas é importante. Um índice em (tenant_id, created_at) ajuda uma consulta que filtra por tenant_id e ordena por created_at; pode não ajudar muito para uma consulta que filtra apenas por created_at.

3. Varredura Apenas de Índice (Index Only Scan)

• O que é: Um Index Scan otimizado onde todos os dados exigidos pela consulta estão disponíveis diretamente dentro do índice. O PostgreSQL não precisa visitar o heap da tabela.
• Quando é eficiente: Quando todas as colunas selecionadas estão disponíveis no índice e o mapa de visibilidade permite que o PostgreSQL evite muitas verificações de heap.
• Otimização: Considere um índice de cobertura com INCLUDE para caminhos com muita leitura, mas não adicione todas as colunas "por precaução". Índices maiores custam mais para manter em gravações.

4. Operações de Junção (Nested Loop, Hash Join, Merge Join)

• Nested Loop: Para cada linha na relação externa, o PostgreSQL varre a relação interna. Eficiente para relações externas pequenas ou quando a relação interna pode ser acessada rapidamente através de um índice.
• Hash Join: Constrói uma tabela hash de uma relação (o lado de construção) e a testa com linhas da outra relação (o lado de teste). Eficiente para tabelas grandes onde os índices não são benéficos para a condição de junção.
• Merge Join: Requer que ambas as relações sejam ordenadas nas chaves de junção. Mescla as listas ordenadas. Eficiente para entradas grandes e já ordenadas.
• Otimização:
  • Garanta que existam índices nas colunas de junção.
  • Revise se estimativas ruins de linhas causaram uma escolha de junção pobre. O PostgreSQL não suporta dicas nativas de otimizador no mesmo estilo que alguns bancos de dados, então as correções usuais são melhores estatísticas, melhores índices ou uma reescrita de consulta.
  • Verifique EXPLAIN ANALYZE para contagens altas de loops ou actual time alto em nós de junção.

5. Ordenação (Sort)

• O que é: Ordena as linhas. Pode ser computacionalmente caro, especialmente em grandes conjuntos de dados.
• Otimização:
  • Adicione um índice cuja ordem de colunas corresponda ao padrão ORDER BY quando a consulta for seletiva o suficiente.
  • Reduza o número de linhas sendo ordenadas adicionando cláusulas WHERE mais restritivas.
  • Garanta que work_mem suficiente esteja configurado para permitir que a ordenação ocorra na memória em vez de no disco.

6. Agregações (Aggregate)

• O que é: Executa operações como COUNT(), SUM(), AVG(), GROUP BY.
• Otimização:
  • Garanta que as cláusulas WHERE sejam eficientes, reduzindo o número de linhas antes da agregação.
  • Considere usar visões materializadas para dados pré-agregados se a agregação for uma operação frequente e lenta.
  • Indexe colunas usadas em cláusulas GROUP BY.

Usando EXPLAIN ANALYZE com Opções

EXPLAIN ANALYZE tem várias opções úteis que podem fornecer informações ainda mais detalhadas.

VERBOSE

• O que faz: Exibe informações adicionais sobre o plano de consulta, como os nomes de tabela qualificados pelo esquema e nomes de colunas de saída.

EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

• O que faz: Inclui os custos estimados na saída. Este é o comportamento padrão, mas você pode desativá-lo explicitamente.

EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

• O que faz: Relata informações sobre o uso de buffers (compartilhados, temporários e locais). Isso ajuda a identificar gargalos de I/O.
  • shared hit: Blocos encontrados no cache de buffer compartilhado do PostgreSQL.
  • shared read: Blocos lidos do disco para os buffers compartilhados.
  • temp read/written: Blocos lidos/escritos em arquivos temporários (frequentemente para ordenações ou hashes que excedem work_mem).

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

• O que faz: Inclui o tempo real de inicialização e o tempo total para cada nó. Este é o comportamento padrão para ANALYZE.

EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

Combinando Opções

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

Dicas Práticas e Melhores Práticas

• Comece com EXPLAIN ANALYZE: Sempre use EXPLAIN ANALYZE para análise de desempenho no mundo real. EXPLAIN sozinho é insuficiente.
• Foque no actual time: Priorize a otimização de nós com o maior actual time.
• Compare rows (estimadas vs. reais): Grandes discrepâncias indicam que o planejador de consultas do PostgreSQL pode estar fazendo suposições imprecisas. Isso pode ser corrigido atualizando as estatísticas da tabela usando ANALYZE <table_name>; ou criando índices apropriados.
• Use BUFFERS: Analise o uso de buffers para entender se sua consulta é limitada por I/O.
• Teste com dados realistas: Execute EXPLAIN ANALYZE em um banco de dados que tenha uma quantidade representativa de dados e uma distribuição de dados semelhante ao seu ambiente de produção.
• Otimize em estágios: Não tente otimizar tudo de uma vez. Aborde primeiro o maior gargalo.
• Considere work_mem: Se você vir leituras significativas de disco para ordenação ou hash (temp read/written em BUFFERS), aumentar work_mem (por sessão ou globalmente) pode ajudar, mas esteja ciente do uso de memória.
• Indexe com sabedoria: Crie apenas índices que sejam realmente usados e benéficos. Muitos índices podem desacelerar as gravações e consumir espaço em disco.
• Verifique a versão do PostgreSQL: Versões mais recentes geralmente têm planejadores de consulta aprimorados e novos recursos que podem afetar o desempenho.

Uma Passagem Prática de Ajuste

Pegue esta consulta:

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Se o plano mostrar uma varredura sequencial, um índice apenas em email pode não ajudar porque a consulta aplica lower(email). O PostgreSQL nem sempre pode usar um índice simples quando a expressão na consulta difere do valor indexado. Uma opção melhor pode ser um índice de expressão:

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

Então execute novamente:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Você está procurando por menos linhas varridas, menos buffers lidos e menor tempo de execução. Se o plano ainda não usar o índice, verifique se a tabela é minúscula, se as estatísticas estão desatualizadas ou se a consulta não está escrita como você pensa que a aplicação a envia.

Outro caso comum é uma junção que parece boa em SQL, mas explode no plano:

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

Índices úteis podem incluir orders(created_at), order_items(order_id) e a chave primária em products(id). Mas se os últimos sete dias incluírem a maior parte da tabela orders, orders(created_at) pode não ser a principal correção. O plano informa se o problema real é o filtro de data, a expansão da junção ou um índice ausente na tabela filha.

Um bom ajuste de consulta PostgreSQL não é "adicione um índice até o plano mudar". É um loop: meça o plano real, faça uma alteração defensável, meça novamente e mantenha a alteração apenas se ela melhorar a carga de trabalho com a qual você realmente se importa.