Otimização de Consultas PostgreSQL

Verificação de afirmações — CHECKS.md. Tudo abaixo se refere ao PostgreSQL comum. Primeiro descubra a versão (SHOW server_version): dela dependem BUFFERS padrão (PG18), skip scan (PG18) e o padrão hash_mem_multiplier (2.0 a partir do PG15).

Regra Principal

Não trate no escuro. A ordem é:

Onde realmente o tempo é gasto;
Qual consulta é a culpada e quantas vezes é chamada;
Em qual nó do plano a estimativa de linhas errou;
Correção;
Verificação pelo número de páginas em dados de volume de produção.

Toque em índice, work_mem e random_page_cost apenas se o diagnóstico apontar para eles. Por quê: sem perfil, corrigem uma consulta "assustadora" com participação de 1/60 e não ganham nada, enquanto um índice adicionado aleatoriamente prejudica consultas longas.

Onde o tempo é gasto

Reclamação "lento": primeiro compare o tempo no BD com o que o usuário vê. Se as consultas somam ~100 ms e a reclamação é de um minuto, o banco não tem culpa. Não mexa nas consultas, procure no aplicativo, rede, frontend.
Operação com muitas consultas: faça um perfil via pg_stat_statements.
1. Defina pg_stat_statements.track = all. O padrão é top, e consultas dentro de funções não são visíveis.
2. Execute pg_stat_statements_reset(). Requer superusuário ou permissão concedida na função. Resetar apaga as estatísticas de todos, portanto faça apenas em ambiente de teste ou em horário tranquilo.
3. Execute a operação uma vez e ordene por total_exec_time.
4. Veja calls, shared_blks_hit + shared_blks_read e toplevel. O tempo de linhas com toplevel = true já inclui consultas aninhadas. Se somar tudo indiscriminadamente, o tempo é contado duas vezes.
Corrija apenas o que consome a maior parte. O ganho não é maior que essa parcela: uma subtarefa de 1 s em 60 no máximo dará 1/60.
Consultas em pg_stat_statements são normalizadas: constantes são substituídas por $1, não é possível separar por sessão, armazenam agregados (média, min/max, stddev). Se parece que o plano depende do valor (cliente grande vs. pequeno), pegue valores específicos e analise EXPLAIN separadamente. Com pg_stat_activity e EXPLAIN VERBOSE, as linhas são vinculadas pelo queryid, para isso é necessário compute_query_id (a partir do PG14; auto o ativa quando o pg_stat_statements está carregado).
Via log: em log_line_prefix são necessários %p ou %c e o tempo %m. Consultas aninhadas são visíveis apenas através do auto_explain. Defina log_min_duration (padrão -1, ou seja, desligado), ative log_analyze e log_nested_statements (padrão off; alterar requer superusuário ou GRANT SET, a partir do PG15). Analise o log com pgBadger. Para uma única sessão, basta SET log_min_duration_statement (superusuário ou GRANT SET ON PARAMETER) e LOAD 'auto_explain' (apenas superusuário).

Em quais dados medir

Não tire conclusões em BD de desenvolvimento com dados fictícios. O plano depende do tamanho das tabelas e das estatísticas, e um banco pequeno cabe inteiro no cache. Se a consulta é rápida em um banco pequeno, escreva que isso não prova nada.
Volume e distribuição — como na produção. Se na produção há dados pessoais, não copie para o ambiente de teste. Gere dados sintéticos com a mesma forma: quantas linhas nas tabelas principais e como estão distribuídas por clientes. Acesse a produção apenas para estatísticas (pg_stat_statements, pg_class).
Não insira planos com constantes da produção em visualizadores externos: constantes podem conter dados pessoais. Use ferramentas locais.

Muitas chamadas rápidas (N+1)

Se a consulta já é rápida, mas é chamada dezenas ou centenas de vezes em uma única operação, não a acelere. Reescreva a tarefa em uma única consulta para todo o lote:
- em vez de um loop de consultas com uma lista de ids no código — WHERE id = ANY($1) ou batch;
- cortes por categorias — count(*) FILTER (WHERE …);
- para não perder linhas sem dados — LEFT JOIN.
Por quê: uma consulta de 1 ms já não pode ser acelerada, mas mil dessas chamadas tornam o sistema lento. Por exemplo, 27 chamadas consolidadas em uma consulta leem ≈60 mil páginas em vez de ≈900 mil.
Se não for possível com uma única consulta, revise o acesso aos dados no serviço, e não as configurações do banco.

Como comparar variantes

Métrica principal — páginas. Execute EXPLAIN (ANALYZE, BUFFERS) e veja shared hit + read no nó superior: lá já está a soma dos filhos. Some hit e read, pois sua proporção depende do cache. No PG18, BUFFERS é ativado junto com ANALYZE automaticamente.
Tempo — apenas referência. É afetado por cache, carga, hardware e pela própria medição do ANALYZE. A segunda execução é sempre mais rápida e não prova nada. Escreva "ficou mais rápido" apenas após várias execuções.
Páginas não veem carga de CPU: ordenação em memória, funções, JIT, planos paralelos. Se a consulta processa mais do que lê, verifique o tempo separadamente.
Se precisar apenas de linhas e buffers, adicione TIMING FALSE: assim não haverá overhead de medição.

Análise do plano

Classe da consulta.
- Curta: retorna poucas linhas, a resposta é esperada por uma pessoa. Exemplo: ficha de objeto, página de lista, busca por chave. Espere acesso por índice e nested loop. Para análise, defina SET max_parallel_workers_per_gather = 0.
- Longa: lê tabelas quase inteiras, agregação no topo. Exemplo: relatórios, análises, exportações. Espere seq scan e hash join, paralelismo é normal aqui.
Isso é expectativa, não regra. Se o plano não correspondeu à expectativa, procure a causa nas estimativas, não adicione um índice.
Estimativa vs. fato. Compare rows com actual rows por nó. Quando loops > 1, o valor actual rows é dado por uma execução; multiplique por loops. Corrija o nó mais interno onde a divergência apareceu pela primeira vez. O erro cresce para cima no plano e leva a método de acesso e join incorretos.
Páginas vs. tamanho das tabelas (para consultas longas). Execute ANALYZE. Depois compare hit + read do nó superior com a soma de relpages das tabelas da consulta em pg_class. Índices e TOAST adicione separadamente se a consulta os lê.
- Comparável ou menor: os dados são lidos uma vez, o plano é razoável.
- Várias vezes maior: os dados são lidos repetidamente, por exemplo Index Scan dentro de nested loop.
Para consultas curtas por índice, esse critério não se aplica.
Memória. Se em BUFFERS houver temp read/written, Sort está indo para disco ou Hash tem Batches > 1, a memória é insuficiente. O que fazer — na seção "O que corrigir".

O que corrigir

Primeiro as estimativas e a própria consulta, depois a memória. Altere custos por último.

Função sobre coluna na condição (starts_at::date = …, date_trunc('day', starts_at) = …, round(col) …). Reescreva em intervalo semiaberto starts_at >= $1 AND starts_at < $2. Calcule os limites para timestamptz no fuso horário do usuário, não do servidor. Por quê: para uma expressão sem estatísticas, o planejador usa seletividade fixa (para = é 0,5% das linhas) e erra muito. O intervalo sobre a própria coluna é estimado pelas estatísticas e usa índice. Crie índice ou estatística por expressão apenas se não puder reescrever.
Verifique qual índice foi escolhido. Se o índice é composto e a coluna filtrada não é a primeira, até o PG18 ele é lido inteiro. No PG18, o skip scan ajuda (no plano — Index Searches: N), mas é vantajoso apenas quando a primeira coluna tem poucos valores distintos. Caso contrário, é necessário um índice onde essa coluna seja a primeira.
Declare FKs compostas (account_id, user_id), mesmo que a aplicação verifique tudo sozinha, e escreva o join por todas as colunas da FK com igualdades simples. O planejador usa a FK para estimar o join por várias colunas. Sem FK, ele considera as colunas independentes e erra muito (1000 linhas em vez de 100 mil), e depois escolhe nested loop em vez de hash. Se o join não cobre a FK inteira (uma coluna com expressão ou no WHERE), a estimativa pela FK não é aplicada. A tabela alvo precisa de uma chave única nas mesmas colunas. O custo é sobrecarga adicional na escrita.
Join desnecessário em consulta longa. Se a tabela não filtra nada e a relação é garantida por FK e NOT NULL, remova-a: geralmente é o maior ganho. Não espere que o planejador remova um inner join com outra tabela sozinho, mesmo com FK: ele remove LEFT JOIN com chave única cujas colunas a consulta não lê, e a partir do PG18, junção da tabela consigo mesma por chave única. Sem garantia de FK e NOT NULL, não mexa nesse join: o resultado mudará.
Estimativas incorretas e não é possível reescrever a consulta: crie estatísticas estendidas ou defina ALTER TABLE … ALTER COLUMN … SET STATISTICS n (padrão 100).
Memória insuficiente (arquivos temporários, Batches > 1). Aumente work_mem ou hash_mem_multiplier para a sessão ou papel (SET, ALTER ROLE … SET), mas não globalmente: work_mem é alocado para cada nó em cada sessão. O limite para hash é work_mem × hash_mem_multiplier. Por padrão, é 4MB × 2.0 = 8MB; antes do PG15, o multiplicador era 1.0. Verifique com SHOW e calcule o limite final.

Hipótese "o planejador errou"

Verifique em uma transação: BEGIN; SET LOCAL enable_nestloop = off; (ou enable_seqscan), depois EXPLAIN (ANALYZE, BUFFERS), depois ROLLBACK. Compare por páginas — assim é visível se existe um plano melhor.
Não deixe enable_* nem na configuração, nem no código do serviço. Eles apenas desencorajam o planejador de usar um nó e afetam todas as consultas. A documentação os chama de recurso temporário grosseiro.
Depois, dois caminhos:
- Estimativas de linhas incorretas — corrija as estimativas (veja "O que corrigir").
- Estimativas corretas, mas o plano desejado sistematicamente não é escolhido em várias consultas — altere os custos. Se SSD ou o banco está quase inteiro em cache, reduza random_page_cost de 4.0 para 1.1–2, mas não abaixo de seq_page_cost (1.0). Em effective_cache_size, defina uma estimativa real: shared_buffers mais cache do SO (padrão 4GB). Altere em todo o banco apenas após verificação em várias consultas.

Fora do escopo

pg_query_state — requer kernel modificado.
plprofiler e plpgsql_check — para lógica em PL/pgSQL; aqui a lógica está na aplicação.
Técnicas que exigem builds comerciais do PostgreSQL.

scary-production/claude-plugins

O que é claude-plugins?

Perguntar na sua IA favorita

Documentação