Um Guia Sistemático para Depurar Consultas Lentas no PostgreSQL

Consultas lentas no PostgreSQL são mais fáceis de corrigir quando você para de tratá-las como mistérios. O banco de dados geralmente pode mostrar o caminho que escolheu, quantas linhas esperava, quantas linhas realmente tocou, se leu do cache ou do disco e se esperou por outra sessão.

O erro que vejo com mais frequência é pular direto de "este endpoint está lento" para "criar um índice". Às vezes funciona. Às vezes, o índice é ignorado porque o predicado está escrito de uma forma que não pode usá-lo. Às vezes, a consulta está boa, mas bloqueada por uma transação que está aberta há vinte minutos. Uma abordagem sistemática economiza tempo porque separa a forma da consulta, as estimativas do planejador, E/S, memória e bloqueios.

Entendendo os Gargalos de Desempenho de Consultas

Antes de mergulhar nas ferramentas, é essencial reconhecer razões comuns pelas quais uma consulta PostgreSQL pode ter desempenho ruim. Esses problemas geralmente se enquadram em algumas categorias principais:

• Índices Ausentes ou Ineficientes: O banco de dados é forçado a realizar varreduras sequenciais em tabelas grandes quando um índice poderia ter fornecido acesso rápido.
• Estrutura de Consulta Subótima: Junções complexas, subconsultas desnecessárias ou uso inadequado de funções podem confundir o planejador.
• Estatísticas Desatualizadas: O PostgreSQL depende de estatísticas para construir planos de execução eficientes. Se as estatísticas estiverem desatualizadas, o planejador pode escolher um caminho ineficiente.
• Contenção de Recursos: Problemas como altos tempos de espera de E/S, bloqueios excessivos ou memória insuficiente alocada para o PostgreSQL.

Passo 1: Identificando a Consulta Lenta

Antes de corrigir uma consulta lenta, você deve identificá-la com precisão. Confiar em reclamações de usuários é ineficiente; você precisa de dados empíricos do próprio banco de dados.

Usando pg_stat_statements

O método mais eficaz para rastrear consultas que consomem muitos recursos em um ambiente de produção é usar a extensão pg_stat_statements. Este módulo rastreia estatísticas de execução para todas as consultas executadas no banco de dados.

Habilitando a Extensão (requer privilégios de superusuário e recarga de configuração):

-- 1. Certifique-se de que está listado no postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

-- 2. Conecte-se ao banco de dados e crie a extensão
CREATE EXTENSION pg_stat_statements;

Consultando os Principais Ofensores:

Para encontrar as consultas que consomem mais tempo total, use a seguinte consulta:

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

Em versões mais antigas do PostgreSQL, essas colunas podem ser nomeadas total_time e mean_time. Use os nomes que seu servidor expõe.

Tempo total e tempo médio respondem a perguntas diferentes. Uma consulta que tem média de 20 milissegundos, mas é executada um milhão de vezes, pode ser o maior custo do seu banco de dados. Uma consulta executada uma vez por hora por 40 segundos pode ser dolorosa para um usuário, mas menos importante para todo o sistema. Observe ambos.

Se você precisar da consulta atualmente lenta, não da historicamente cara, verifique as sessões ativas:

SELECT pid, now() - query_start AS age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

Passo 2: Analisando o Plano de Execução com EXPLAIN ANALYZE

Uma vez que uma consulta lenta é isolada, o próximo passo crítico é entender como o PostgreSQL a está executando. O comando EXPLAIN mostra o plano pretendido, mas EXPLAIN ANALYZE realmente executa a consulta e relata o tempo real gasto em cada etapa.

Sintaxe e Uso

Sempre envolva sua consulta lenta com EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) para a saída mais detalhada. A opção BUFFERS é crucial, pois mostra a atividade de E/S de disco.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM large_table lt 
JOIN other_table ot ON lt.id = ot.lt_id
WHERE lt.status = 'active' AND lt.created_at > NOW() - INTERVAL '1 day';

Interpretando a Saída

A saída é lida de baixo para cima e da direita para a esquerda, pois os nós mais internos são executados primeiro. As principais métricas a serem focadas incluem:

1. cost=: O custo estimado do planejador, não o tempo de relógio de parede. Use-o para comparar escolhas de plano, não como milissegundos.
2. rows=: O número estimado de linhas processadas por aquele nó.
3. actual time=: O tempo real gasto em milissegundos nesta operação específica.
4. rows= (Real): O número real de linhas retornadas por este nó.
5. loops=: Quantas vezes este nó foi executado (muitas vezes alto em loops aninhados).

Detectando Ineficiências:

• Varreduras Sequenciais em Tabelas Grandes: Se o acesso a uma tabela grande usa Seq Scan em vez de um Index Scan ou Bitmap Index Scan, você provavelmente precisa de um índice melhor.
• Grande Discrepância entre Linhas Estimadas e Reais: Se o planejador estimou 10 linhas, mas o nó realmente processou 1.000.000 de linhas, as estatísticas estão desatualizadas ou o planejador fez uma escolha ruim.
• Alto actual time em Junções/Ordenações: Tempo excessivo gasto em operações Hash Join, Merge Join ou Sort geralmente indica memória insuficiente (work_mem) ou incapacidade de usar índices de forma eficaz.

Observe também as linhas Buffers. shared hit significa que o PostgreSQL encontrou páginas no cache. shared read significa que ele teve que ler páginas do armazenamento. Uma consulta pode ser lenta porque o plano é ruim, ou porque o plano é razoável, mas lê uma grande quantidade de dados frios do disco.

Dica: Para planos complexos, use ferramentas online como explain.depesz.com ou o visualizador de planos de explicação do pgAdmin para interpretar os resultados graficamente.

Passo 3: Lidando com Gargalos Comuns

Com base nas descobertas do seu EXPLAIN ANALYZE, aplique correções direcionadas.

Otimização de Índices

Se Seq Scan domina uma tabela grande e a consulta é seletiva, considere índices nas colunas usadas nas cláusulas WHERE, JOIN e ORDER BY. Uma varredura sequencial não é automaticamente ruim; o PostgreSQL pode escolhê-la corretamente quando a maioria das linhas é necessária.

Exemplo: Se a consulta filtra por status e depois junta em user_id:

-- Crie um índice composto para buscas e junções mais rápidas
CREATE INDEX idx_large_table_status_user_id ON large_table (status, user_id);

Para sistemas de produção, use CREATE INDEX CONCURRENTLY quando precisar evitar bloquear escritas, e lembre-se de que ele não pode ser executado dentro de um bloco de transação normal:

CREATE INDEX CONCURRENTLY idx_large_table_status_user_id
ON large_table (status, user_id);

Atualizando Estatísticas (VACUUM ANALYZE)

Se o planejador está fazendo estimativas extremamente imprecisas (incompatibilidade entre linhas estimadas e reais), force uma atualização das estatísticas da tabela.

ANALYZE VERBOSE table_name;
-- Para tabelas altamente ativas, considere limites de autovacuum/analyze por tabela mais baixos.

Ajuste de Memória

Se operações de ordenação ou hash estão sendo derramadas no disco (muitas vezes indicado por alta E/S na saída BUFFERS ou ordenação lenta), aumente a memória de trabalho disponível do PostgreSQL.

-- Aumente work_mem no nível da sessão para o teste de consulta específico
SET work_mem = '128MB'; 
-- Ou globalmente no postgresql.conf para melhorias de desempenho sustentadas

Aviso: Aumentar work_mem globalmente muito alto pode esgotar a memória do sistema se muitas consultas complexas forem executadas simultaneamente. Ajuste isso cuidadosamente com base na capacidade do servidor.

Procure por detalhes de derramamento de ordenação ou hash no plano. A saída mais recente do PostgreSQL pode mostrar métodos de ordenação como external merge Disk: ..., que é um sinal claro de que a operação excedeu a memória disponível e escreveu arquivos temporários.

Reescrevendo Consultas

Às vezes, a própria estrutura é o problema. Evite predicados não SARGable (condições que impedem o uso de índice), como aplicar funções a colunas indexadas na cláusula WHERE:

Ineficiente (impede o uso de índice):

WHERE DATE(created_at) = '2023-10-01'

Eficiente (permite o uso de índice):

WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00'

Outro padrão comum é selecionar muito mais colunas do que a aplicação precisa. SELECT * torna os planos mais difíceis de otimizar, aumenta o uso de memória e pode forçar leituras extras de heap quando uma varredura apenas de índice poderia funcionar. Para caminhos críticos, liste as colunas deliberadamente.

Verificações de Bloqueio

Se EXPLAIN ANALYZE é rápido na sua sessão, mas a aplicação está lenta, a consulta pode estar esperando antes mesmo de obter tempo de execução útil. Verifique esperas de bloqueio:

SELECT pid, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Em seguida, encontre o bloqueador:

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.query AS blocked_query,
       blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN pg_locks blocker_locks
  ON blocker_locks.locktype = blocked_locks.locktype
 AND blocker_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocker_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocker
  ON blocker.pid = blocker_locks.pid;

A correção pode ser no nível da aplicação: transações mais curtas, mover chamadas lentas de API externa para fora da transação, evitar SELECT ... FOR UPDATE desnecessário ou alterar a ordem em que as tabelas são atualizadas para que transações concorrentes não entrem em deadlock.

Um Pequeno Exemplo: A Consulta Lenta do Dashboard

Suponha que um dashboard execute esta consulta a cada poucos segundos:

SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

A tabela tem milhões de linhas. EXPLAIN ANALYZE mostra uma varredura sequencial, um grande número de linhas removidas pelo filtro e uma ordenação. O primeiro instinto pode ser indexar created_at, mas o predicado envolve a coluna em DATE(created_at), então um índice normal em created_at é menos útil.

Reescreva o filtro de data como um intervalo:

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Em seguida, considere um índice que corresponda ao filtro e à ordenação:

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

Esta não é uma receita de índice universal. Funciona quando paid é um filtro comum do dashboard e quando os pedidos pagos mais recentes são o que a aplicação geralmente solicita. Se a aplicação também filtra fortemente por conta, o melhor índice pode começar com account_id. O ponto é projetar o índice em torno do padrão de acesso real, não em torno de uma única coluna mencionada na consulta.

Após a alteração, o plano deve mostrar menos linhas varridas e, idealmente, evitar uma ordenação explícita. Se o plano ainda escolher uma varredura sequencial, verifique se o intervalo de datas é muito amplo, se as estatísticas estão desatualizadas ou se os parâmetros da consulta em produção diferem do seu caso de teste.

Passo 4: Verificação e Monitoramento

Após implementar uma alteração, execute novamente EXPLAIN ANALYZE na mesma consulta com parâmetros comparáveis. O objetivo nem sempre é ver uma varredura de índice. O objetivo é ver menos trabalho: menos linhas removidas por filtros, menos buffers lidos, sem derramamento de disco, melhores estimativas de linhas ou menos tempo gasto no nó caro.

Continue monitorando pg_stat_statements para confirmar que a consulta modificada não está mais aparecendo na lista dos principais ofensores, garantindo que a correção tenha um impacto global positivo.

Observe também o custo de escrita após adicionar índices. Cada novo índice deve ser mantido durante inserções, atualizações e exclusões. Um índice de leitura perfeito para um dashboard pode ser uma má troca se ele desacelerar um caminho de ingestão de alto volume. Para tabelas importantes, verifique ambos os lados: a consulta lenta melhorou e a latência de escrita ou o inchaço da tabela pioraram depois?

Mais um hábito ajuda durante incidentes reais: teste com valores de parâmetros realistas. O PostgreSQL pode escolher planos diferentes para um cliente com dez linhas e um cliente com dez milhões de linhas. Se a aplicação usa instruções preparadas, planos genéricos também podem se comportar de forma diferente da consulta avulsa que você cola no psql. Quando o problema de produção afeta um locatário, uma conta ou um intervalo de datas, reproduza essa forma o mais próximo possível em um ambiente seguro.

Se a consulta for destrutiva ou muito cara para executar com EXPLAIN ANALYZE, comece com EXPLAIN simples, execute em staging ou envolva o teste em uma transação que você desfaz. Para UPDATE e DELETE, você ainda pode aprender muito com a parte de varredura e junção do plano antes de fazer qualquer alteração em produção.

Mantenha uma pequena nota com o plano antes e depois, o tempo e o motivo da alteração. Esse hábito previne regressões acidentais mais tarde e dá à próxima pessoa uma explicação real em vez de um nome de índice misterioso no esquema.