Identificando e corrigindo consultas lentas

Este é o enunciado final da entrevista: o entrevistador entrega uma consulta lenta e um plano EXPLAIN ANALYZE e pede que você faça o diagnóstico. Ele está avaliando um método, não truques decorados.

Uma resposta forte segue uma lista de verificação em voz alta: medir, ler o plano, encontrar o custo dominante, formular uma hipótese, propor uma correção e verificar. Esta lição desenvolve essa lista passo a passo.

Seja sistemático e narre seu raciocínio; é isso que garante a avaliação de nível sênior.

Nunca adivinhe apenas com base na consulta. Obtenha o plano real com EXPLAIN (ANALYZE, BUFFERS).

ANALYZE fornece os tempos e as quantidades de linhas reais; BUFFERS mostra se você está obtendo dados da memória ou lendo do disco. Juntos, eles informam se a consulta é limitada pela CPU, pelas operações de entrada e saída ou se simplesmente está fazendo trabalho demais.

Execute-a algumas vezes; a primeira execução pode sofrer uma penalidade por não haver dados na memória, distorcendo o tempo medido.

Não leia de cima para baixo procurando aleatoriamente. Encontre o nó em que o maior tempo é realmente gasto.

Calcule o tempo próprio de cada nó: seu actual time total menos o tempo de seus filhos, multiplicado por loops. O nó com a maior parcela é o seu alvo; todo o restante é ruído.

Nas entrevistas, diga: 80 por cento do tempo de execução está nesta única varredura sequencial, então é nela que vou me concentrar. Otimizar qualquer outra coisa seria esforço desperdiçado.

No nó dominante, compare a quantidade estimada de linhas com a quantidade real. Uma grande diferença significa que o planejador está operando às cegas e provavelmente escolheu um plano ruim (algoritmo de junção incorreto, método de acesso incorreto).

O exemplo mostra uma subestimativa de 1000 vezes. Antes de reprojetar qualquer coisa, atualize as estatísticas; esse único comando frequentemente corrige o plano sem custo.

ANALYZE recalcula as estatísticas das colunas; VACUUM ANALYZE também limpa as tuplas obsoletas e atualiza o mapa de visibilidade.

O erro corrigível mais frequente: uma função ou conversão envolve a coluna em WHERE, impedindo o uso do índice e fazendo o mecanismo executar uma varredura sequencial.

O exemplo força uma varredura completa porque DATE() é aplicada a cada linha. Reescreva-a como um predicado de intervalo sobre a coluna sem função (forma sargável), e o índice em created_at passará a ser usado.

A mesma ideia vale para WHERE lower(email)=...: armazene os dados normalizados, consulte a coluna sem função ou crie um índice de expressão.

Se o nó dominante for uma varredura sequencial com um filtro altamente seletivo, ou um laço aninhado com loops enorme sobre uma chave interna sem índice, a correção geralmente é criar um índice.

Adicione um índice à coluna filtrada ou unida. O exemplo cria um em customer_id para que a junção possa trocar as varreduras sequenciais por varreduras usando índice, e o planejador talvez escolha um plano muito mais barato.

Verifique executando novamente EXPLAIN ANALYZE; não presuma que o índice ajudou.

SELECT * carrega todas as colunas do disco e pela rede, além de impedir varreduras somente pelo índice, pois o índice raramente contém todas as colunas.

Selecione apenas as colunas de que precisa. Isso reduz a largura das linhas, diminui a entrada e saída e pode permitir uma varredura somente por um índice de cobertura.

Um entrevistador que inclui SELECT * quer que você perceba isso. Reduzir a lista de colunas costuma gerar um ganho rápido e real em tabelas largas.

Se um nó Sort ou Hash informar uso de disco (Sort Method: external merge Disk: 25000kB ou Batches: > 1), a operação excedeu work_mem e transbordou para o disco.

Opções: aumente work_mem para a sessão, reduza a quantidade de linhas que chega à ordenação ou à dispersão filtrando mais cedo, ou adicione um índice que forneça a ordem desejada para que nenhuma ordenação seja necessária.

Esse é um diagnóstico preciso, de nível sênior, que os entrevistadores valorizam.

Observe Rows Removed by Filter: 9500000. A consulta leu dez milhões de linhas e descartou quase todas; é um desperdício clássico de trabalho.

Correções: adicione um índice para que o filtro seja aplicado durante o acesso, e não depois; torne o predicado mais seletivo; ou antecipe a filtragem na consulta para que menos linhas subam pela árvore.

O princípio é: faça o mínimo de trabalho, filtrando o mais cedo e da forma mais barata possível.

Recite isto na entrevista e você não perderá o rumo:

• Meça com EXPLAIN (ANALYZE, BUFFERS).
• Localize o nó que consome mais tempo.
• Compare as linhas estimadas e reais; corrija primeiro as estatísticas desatualizadas.
• Verifique a sargabilidade; remova as funções das colunas filtradas.
• Crie índices para filtros seletivos e chaves de junção.
• Reduza as colunas; evite SELECT *.
• Observe os transbordamentos para o disco e a busca de linhas em excesso.
• Verifique executando novamente o plano.

Explique em voz alta um exemplo completo. O plano mostra uma varredura sequencial em uma tabela orders com 50 milhões de linhas, filtro customer_id = 42, Rows Removed by Filter próximo de 50 milhões e uma estimativa aproximadamente compatível com o resultado real.

Diagnóstico: filtro seletivo, nenhum índice; o custo dominante é a varredura. Correção: CREATE INDEX ON orders(customer_id). Execute novamente: o plano muda para uma varredura por índice, e o tempo cai de segundos para menos de um milissegundo.

Esse ciclo de medir, diagnosticar, corrigir e verificar é o modelo de resposta para qualquer pergunta sobre uma consulta lenta.

Uma consulta filtra com WHERE YEAR(order_date) = 2026, e o plano mostra uma varredura sequencial completa, apesar de existir um índice B-tree em order_date. Qual é a melhor primeira correção?

Agora você tem um método repetível para responder a perguntas sobre consultas lentas:

• Sempre meça usando EXPLAIN (ANALYZE, BUFFERS) e concentre-se no nó dominante.
• Corrija primeiro as estatísticas desatualizadas quando as estimativas e os resultados reais divergirem.
• Torne os predicados sargáveis, adicione índices para filtros seletivos e chaves de junção, e elimine o uso de SELECT *.
• Resolva os derramamentos em disco e a busca excessiva de dados; depois, verifique o novo plano.

Descreva a lista de verificação, proponha uma mudança concreta e execute novamente o plano para comprová-la: essa é a resposta de nível sênior.