Planos de execução & otimização

Cada query SQL passa por um otimizador antes de ser executada. O parser converte o texto em árvore lógica; o planejador examina opções de execução — qual ordem de joins, qual índice, qual algoritmo — e estima o custo de cada plano. O com menor custo estimado vence. O resultado é o "plano de execução", e o comando que o expõe é EXPLAIN.

Saber ler um plano de execução é a habilidade que mais separa quem "usa Postgres" de quem "tunifica Postgres". É a única ferramenta que mostra o que o banco realmente faz com sua query — independente do que você imaginou que ele faria. Quase todo problema de performance investigável começa com EXPLAIN ANALYZE; quase toda solução é confirmada com outro EXPLAIN ANALYZE mostrando que mudou.

Este conceito é prático: como rodar, como interpretar, o que cada estratégia significa, e como diagnosticar e corrigir os problemas mais comuns. Ferramentas e padrões que aceleram a leitura de planos vêm no fim. Postgres é o foco; raciocínio se transfere para SQL Server (que tem SET STATISTICS PROFILE), MySQL (EXPLAIN ANALYZE desde 8.0.18) e Oracle.

EXPLAIN, EXPLAIN ANALYZE — a diferença

EXPLAIN query mostra o plano previsto: a árvore de operações que o otimizador escolheu, com estimativas de quantas linhas cada nó vai produzir e quanto custo (em unidades arbitrárias) cada um terá. Não executa a query.

EXPLAIN ANALYZE query efetivamente roda a query e junta tempos reais e contagens reais ao plano. É o que você quer em 99% dos casos. Atenção: EXPLAIN ANALYZE de um UPDATE ou DELETE executa de fato — envolva em BEGIN; ... ROLLBACK se quiser ver o plano sem aplicar.

Variantes úteis:

• EXPLAIN (ANALYZE, BUFFERS) ... — adiciona contagens de buffer hits/reads. Crítico para entender se a lentidão é I/O ou CPU.
• EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ... — informações extras sobre cada nó (saída de colunas, schemas).
• EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ... — saída estruturada para ferramentas (PEV, dalibo).
• EXPLAIN (ANALYZE, WAL) ... — Postgres 13+, mostra WAL gerado, útil para tuning de write paths.

Anatomia de um plano

Um plano é uma árvore. Postgres mostra de cima para baixo, com indentação representando profundidade. A execução acontece das folhas para a raiz: nós internos consomem linhas dos filhos.

EXPLAIN ANALYZE
SELECT p.id, p.valor_total, c.nome
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.criado_em >= '2026-01-01'
ORDER BY p.criado_em DESC
LIMIT 50;

Saída típica (Postgres):

Limit  (cost=8.47..8.59 rows=50 width=72) (actual time=0.15..0.18 rows=50 loops=1)
  ->  Sort  (cost=8.47..9.20 rows=290 width=72) (actual time=0.15..0.16 rows=50 loops=1)
        Sort Key: p.criado_em DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (cost=2.80..7.85 rows=290 width=72) (actual time=0.05..0.13 rows=312 loops=1)
              Hash Cond: (p.cliente_id = c.id)
              ->  Index Scan using idx_ped_criado_em on pedidos p  (cost=0.42..4.50 rows=290 width=24)
                    Index Cond: (criado_em >= '2026-01-01')
              ->  Hash  (cost=1.80..1.80 rows=80 width=56) (actual time=0.02..0.02 rows=80 loops=1)
                    ->  Seq Scan on clientes c  (cost=0.00..1.80 rows=80 width=56)
Planning Time: 0.21 ms
Execution Time: 0.24 ms

O que cada número significa:

• cost=start..total: custo estimado em unidades arbitrárias. start é o custo até a primeira linha; total é o custo até a última. Compara planos relativamente, não tem unidade absoluta de tempo.
• rows=N: estimativa de linhas que o nó produzirá. Vem das estatísticas de ANALYZE.
• actual time=X..Y: tempo real do primeiro e último output (em ms).
• actual rows=N: linhas realmente produzidas.
• loops=N: quantas vezes o nó foi executado. Importante em joins aninhados — multiplica o tempo por linha.

A regra de ouro do diagnóstico: compare rows estimado com actual rows. Diferença grande (10×, 100×) é sintoma de estatísticas erradas — o otimizador tomou decisão baseada em premissa falsa, e o plano provavelmente é subótimo.

As estratégias de scan

Sequential Scan (Seq Scan)

Lê a tabela inteira, página por página, aplica filtros enquanto lê. É a estratégia certa quando a query precisa de uma fração grande das linhas (geralmente > 5-15% conforme seletividade e largura de tupla). Sequential scan paralelizado (Postgres 9.6+) divide a tabela em blocos para múltiplos workers.

Index Scan

Desce a B-tree para encontrar entradas que satisfazem o filtro, e para cada entrada vai à tabela buscar a linha. Excelente para seletividade baixa (poucas linhas atendem). Para seletividade alta, o "ir à tabela" para muitas linhas custa mais que sequential.

Index Only Scan

Quando o índice contém todas as colunas que a query precisa, o banco evita ir à tabela — a tupla está completa no índice. Em Postgres, exige que o "Visibility Map" esteja atualizado para a página (autovacuum cuida disso). É a forma mais rápida de query. Padrão para fazer surgir: criar índice cobrindo (com INCLUDE) as colunas selecionadas, ou usar índice composto que já contenha todas.

Bitmap Index Scan / Bitmap Heap Scan

Para condições com muitas linhas que estão espalhadas, Postgres às vezes constrói um bitmap em memória: visita o índice, marca bits das páginas que têm linhas matching, depois visita a tabela em ordem física. Combina vantagem de índice (filtro eficiente) com leitura sequencial da heap. Aparece tipicamente em OR com índices distintos (BitmapOr) e em filtros de seletividade média.

CTE Scan / Subquery Scan

Wrap em torno de subquery ou CTE materializada. Em Postgres 11 e antes, CTEs eram sempre materializadas (barreira de otimização); a partir do 12, o otimizador decide (mantém WITH MATERIALIZED ou NOT MATERIALIZED para forçar). Vale conhecer o histórico — muito código antigo usa CTEs como otimização e depende do comportamento velho.

As três estratégias de join

Postgres conhece três algoritmos de join, e o otimizador escolhe conforme tamanhos e índices.

Nested Loop

Para cada linha da tabela externa, varre a tabela interna buscando matches. Com índice na chave de junção da tabela interna, é eficientíssimo para resultados pequenos (poucas linhas externas). Sem índice, vira O(N×M) — desastre. Aparece em queries com LIMIT alto e seletividade boa em uma das tabelas.

Hash Join

Constrói tabela hash com a tabela menor; varre a maior buscando matches no hash. Excelente para uniões de grandes conjuntos sem índice na coluna de junção. Custo: memória para a hash table (limitada por work_mem). Quando excede, derrame para disco (Batches: N no plano) — performance degrada.

Merge Join

Ambas as tabelas precisam estar ordenadas pela coluna de junção (ou ter índice que entregue ordenação). Avança ponteiros em paralelo, juntando matches. Eficiente quando ordem já existe; caro se exige sort de ambos os lados.

O otimizador escolhe baseado em estimativas. Se as estatísticas estão erradas, ele pode escolher Nested Loop quando deveria ser Hash, e o tempo voa para minutos. Diagnóstico: ver actual time do nó de join e checar contra rows esperado vs real.

Estatísticas — o cérebro do otimizador

Toda decisão do planejador depende de estatísticas mantidas em pg_statistic. ANALYZE tabela coleta essas estatísticas: histogramas de valores, fração de NULLs, cardinalidade distinta estimada, valores mais comuns. Autovacuum dispara ANALYZE automaticamente conforme threshold de mudanças, mas em mass loads ou após mudanças grandes, rodar manualmente é recomendado.

Quando estatísticas falham, o plano falha. Casos comuns:

• Após bulk load: 10M linhas inseridas, mas autovacuum ainda não rodou. Estatísticas dizem "tabela tem 50 linhas". Plano usa Seq Scan onde Index Scan ganharia. ANALYZE manual resolve.
• Skew em coluna correlacionada: 95% das linhas têm status = 'concluido'. Filtro por status = 'pendente' retorna pouquíssimas linhas — mas Postgres pode estimar errado se não tem most_common_values capturado bem. Solução: aumentar default_statistics_target ou usar CREATE STATISTICS para correlações multi-coluna.
• Multi-column dependencies: WHERE cidade = 'SP' AND estado = 'SP' — Postgres trata como independentes e estima seletividade(cidade) × seletividade(estado), o que é absurdamente baixo. CREATE STATISTICS (dependencies, ndistinct) ON cidade, estado FROM enderecos ensina a correlação. Essencial em modelagens com colunas correlacionadas.

Padrões de diagnóstico

Query lenta de ontem para hoje

Pergunta: o que mudou? Estatísticas (mass load? deleção?)? Tamanho de tabela cruzou threshold (passou a Seq Scan)? Plano mudou no banco mesmo? EXPLAIN hoje, comparar com tempo de execução histórico (pg_stat_statements acumula). Pode ser bloat — mesmo plano, mais I/O.

Query rápida em dev, lenta em prod

Tamanho da tabela. Dev tem 10k linhas, prod tem 100M. EXPLAIN em ambos — quase certo que o plano é diferente. Para diagnosticar problemas de prod localmente, restore um snapshot (mesmo que filtrado) ou clone com pg_dump --schema-only + dados sintéticos replicando cardinalidades. Apenas escolhas de plano em volumes reais são confiáveis.

Estatísticas estimadas vs reais divergem muito

rows=100 mas actual rows=100000. ANALYZE manual; aumente default_statistics_target (Postgres default é 100; subir para 1000 em colunas críticas é comum); considere CREATE STATISTICS para correlações.

Lentidão em LIMIT

Você tem SELECT ... ORDER BY criado_em DESC LIMIT 10. Plano usa Index Scan Backward sobre idx_criado_em e termina rápido. Adiciona WHERE status = 'pago', mantém LIMIT 10. Plano continua usando o índice de criado_em; a maioria das linhas top-N por data não tem status='pago'; o banco lê milhares antes de achar 10. Sintoma: query rápida vira lenta com filtro adicional. Solução: índice composto (status, criado_em DESC) ou parcial em criado_em WHERE status = 'pago'.

Reescritas que ajudam o otimizador

Muitas vezes a query "manda" um plano ruim por como foi escrita — reescrever ajuda mais que tunar parâmetros.

Substitua OFFSET por keyset pagination

OFFSET 100000 LIMIT 20 obriga o banco a ler 100k linhas só para descartar. WHERE (criado_em, id) < (?, ?) ORDER BY criado_em DESC, id DESC LIMIT 20 usa índice composto e roda em milissegundos independente da posição. Com cursor opaco passado entre páginas, é o padrão sênior para paginação de listas longas.

EXISTS vs IN para subqueries

Para "existe pelo menos um", EXISTS costuma gerar melhor plano (semi-join) que IN (SELECT ...) em casos onde a subquery retorna muitos. Postgres trata os dois semelhantemente em muitos casos modernos, mas em queries com duplicatas ou nulls, semantica difere — vale checar com EXPLAIN.

Window functions vs self-join

"Maior valor por cliente" via self-join é antipattern. ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY valor DESC) com filtro = 1 é mais rápido e claro.

JOINs em vez de subqueries correlacionadas

Subqueries correlacionadas no SELECT rodam uma vez por linha externa. Para muitas linhas, LEFT JOIN LATERAL ou agregações com GROUP BY podem ser ordens de magnitude mais rápidos.

Ferramentas para ler planos

Plano em texto cresce rápido — queries grandes ocupam dezenas de níveis. Visualizadores ajudam:

• explain.dalibo.com — cole JSON de plano, vê árvore com hot spots em vermelho. Gratuito, sem login.
• pev2 (postgres-explain-visualizer) — open source, roda local; mesmo input em JSON.
• pgMustard — comercial, sugere otimizações baseadas em padrões reconhecidos. Útil em times grandes.
• auto_explain — extensão que loga automaticamente planos de queries lentas em produção. Ative com auto_explain.log_min_duration = 1000 para capturar tudo > 1s.
• pg_stat_statements — extensão essencial. Acumula contadores por query normalizada. Top-10 mais custosas é o ponto de partida de toda investigação.

Lendo planos nas três linguagens

// EF Core 9 logging plus Npgsql interceptor para EXPLAIN
public class ExplainInterceptor : DbCommandInterceptor {
    public override async ValueTask<DbDataReader> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken ct = default) {

        if (command.CommandText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)) {
            using var explainCmd = command.Connection!.CreateCommand();
            explainCmd.CommandText = "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + command.CommandText;
            foreach (DbParameter p in command.Parameters)
                explainCmd.Parameters.Add(((ICloneable)p).Clone());
            using var reader = await explainCmd.ExecuteReaderAsync(ct);
            if (await reader.ReadAsync(ct))
                _logger.LogDebug("Plan: {Plan}", reader.GetString(0));
        }
        return await base.ReaderExecutingAsync(command, eventData, result, ct);
    }
}

from sqlalchemy import text
import json

def explain_query(session, stmt):
    """Roda EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) numa query SQLAlchemy."""
    compiled = stmt.compile(dialect=session.bind.dialect,
                            compile_kwargs={"literal_binds": True})
    sql = "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + str(compiled)
    result = session.execute(text(sql))
    return result.scalar()

# Uso em test:
plan = explain_query(session, select(Pedido).where(Pedido.criado_em >= '2026-01-01'))
print(json.dumps(plan, indent=2))

# Asserção de performance — testa contra regressão de plano:
assert plan[0]["Plan"]["Node Type"] != "Seq Scan", "Deveria usar índice"

func explainAnalyze(ctx context.Context, pool *pgxpool.Pool,
    sql string, args ...any) (string, error) {

    explainSQL := "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql
    var jsonOut string
    err := pool.QueryRow(ctx, explainSQL, args...).Scan(&jsonOut)
    return jsonOut, err
}

// Uso em test:
func TestPedidosUsesIndex(t *testing.T) {
    plan, err := explainAnalyze(ctx, pool,
        `SELECT * FROM pedidos WHERE cliente_id = $1`, 42)
    require.NoError(t, err)
    require.Contains(t, plan, `"Node Type": "Index Scan"`,
        "esperava index scan, plano: %s", plan)
}

Quando o otimizador é teimoso — query hints e workarounds

Postgres não tem hints como Oracle (/*+ INDEX(t idx) */). Há razões filosóficas — Tom Lane defende que hints poluem queries com decisões que envelhecem mal. Em vez disso, Postgres oferece "GUC parameters" que você pode setar por sessão para desligar tipos de plano: SET enable_seqscan = off, enable_nestloop = off, etc. Use apenas em diagnóstico — desabilitar Seq Scan em produção quebra queries onde Seq Scan é correto.

Quando o otimizador insiste em plano ruim:

1. Verifique estatísticas (ANALYZE).
2. Aumente default_statistics_target ou crie estatísticas extras.
3. Reescreva a query (CTE materializada, subquery, join lateral).
4. Ajuste índices.
5. Em último caso, considere pg_hint_plan (extensão) — mas trate como dívida.

Como praticar

1. Pegue as 10 queries mais custosas do seu sistema via pg_stat_statements. Para cada uma, rode EXPLAIN (ANALYZE, BUFFERS) e leia. Identifique o nó mais demorado. Tente uma melhoria — índice, reescrita, ou estatística adicional. Compare antes/depois.
2. Configure auto_explain em ambiente de teste com log_min_duration = 100ms. Rode sua suíte de integração. Investigue o que apareceu — provavelmente há queries lentas dormindo na base que ninguém viu.
3. Implemente paginação keyset num endpoint que hoje usa OFFSET. Compare planos e tempos com 1k, 10k, 100k de offset. O ganho de keyset cresce com a profundidade — visualize.