Truques para melhorar performance de queries no PostgreSQL

1. Compreendendo o Planejador de Consultas e o EXPLAIN

O primeiro passo para otimizar queries é entender como o PostgreSQL executa suas consultas. O comando EXPLAIN ANALYZE é sua ferramenta mais valiosa.

EXPLAIN ANALYZE
SELECT * FROM vendas WHERE data >= '2024-01-01' AND valor > 1000;

A saída típica mostra:
- Custo: valor estimado pelo planejador (primeiro número = custo inicial, segundo = custo total)
- Linhas: estimativa vs. realidade de linhas retornadas
- Tempo real: tempo efetivo de execução em ms

Para análise mais profunda, use o formato JSON com buffers:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM vendas WHERE data >= '2024-01-01';

Isso revela quantos buffers foram lidos do cache vs. disco. Um Sequential Scan em tabelas grandes (acima de 10 milhões de registros) geralmente indica falta de índice adequado.

Identificando scans ineficientes:
- Seq Scan: varredura sequencial — aceitável para tabelas pequenas, desastroso para grandes
- Index Scan: uso eficiente de índice B-Tree
- Bitmap Heap Scan: combina múltiplos índices — intermediário

2. Indexação Inteligente: Tipos e Estratégias

Índices B-Tree

Use para colunas com alta cardinalidade (muitos valores únicos). Evite em colunas booleanas ou com poucos valores.

CREATE INDEX idx_vendas_data ON vendas (data);
CREATE INDEX idx_clientes_email ON clientes (email);

Índices Parciais e Funcionais

Reduzem o tamanho do índice e aceleram consultas específicas:

-- Índice parcial: apenas vendas ativas
CREATE INDEX idx_vendas_ativas ON vendas (status) WHERE status = 'ativo';

-- Índice funcional: busca case-insensitive
CREATE INDEX idx_clientes_nome_lower ON clientes (lower(nome));
SELECT * FROM clientes WHERE lower(nome) = 'joão silva';

Índices GIN e GiST

Para tipos de dados complexos:

-- GIN para JSONB
CREATE INDEX idx_produtos_tags ON produtos USING GIN (tags);
SELECT * FROM produtos WHERE tags ? 'promocao';

-- GiST para texto completo
CREATE INDEX idx_documentos_texto ON documentos USING GIN (to_tsvector('portuguese', conteudo));
SELECT * FROM documentos WHERE to_tsvector('portuguese', conteudo) @@ to_tsquery('portuguese', 'performance & banco');

3. Otimização de Consultas com JOIN e Subconsultas

IN vs. EXISTS vs. JOIN

Em muitos casos, EXISTS supera IN quando a subconsulta retorna muitas linhas:

-- Lento com IN se subconsulta for grande
SELECT * FROM clientes WHERE id IN (SELECT cliente_id FROM pedidos WHERE valor > 1000);

-- Rápido com EXISTS (para quando a subconsulta é grande)
SELECT * FROM clientes c WHERE EXISTS (
    SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id AND p.valor > 1000
);

LATERAL JOIN

Evita subconsultas correlacionadas lentas, especialmente em agregações por grupo:

-- Subconsulta correlacionada lenta
SELECT c.nome, (SELECT MAX(p.data) FROM pedidos p WHERE p.cliente_id = c.id) AS ultima_compra
FROM clientes c;

-- LATERAL JOIN mais rápido
SELECT c.nome, u.ultima_compra
FROM clientes c
LEFT JOIN LATERAL (
    SELECT MAX(data) AS ultima_compra
    FROM pedidos
    WHERE cliente_id = c.id
) u ON true;

Window Functions vs. GROUP BY

Para rankings e totais parciais, funções de janela são mais eficientes:

-- GROUP BY perde detalhes
SELECT departamento, AVG(salario) FROM funcionarios GROUP BY departamento;

-- Window Function mantém detalhes e é mais performática para cálculos paralelos
SELECT nome, departamento, salario,
       AVG(salario) OVER (PARTITION BY departamento) AS media_departamento
FROM funcionarios;

4. Configurações de Parâmetros do PostgreSQL

Ajustes no postgresql.conf podem transformar a performance:

# work_mem: memória para ordenações e hash joins (por operação)
# Aumente com cuidado — multiplica por número de conexões simultâneas
work_mem = 64MB

# effective_cache_size: estimativa do cache do sistema (ajuda o planejador)
# Configure próximo à memória RAM disponível para cache
effective_cache_size = 4GB

# shared_buffers: buffer pool compartilhado (25% da RAM, máximo 8GB)
shared_buffers = 2GB

# wal_buffers: buffer para write-ahead log (aumente para escrita intensiva)
wal_buffers = 16MB

Como testar o impacto:

SET work_mem = '128MB';
EXPLAIN ANALYZE SELECT * FROM vendas ORDER BY valor DESC;

5. Técnicas de Particionamento e Manutenção

Particionamento por Intervalo

Para tabelas com dados temporais, o particionamento reduz drasticamente o escopo das consultas:

CREATE TABLE vendas (
    id SERIAL,
    data DATE NOT NULL,
    valor NUMERIC
) PARTITION BY RANGE (data);

CREATE TABLE vendas_2024_q1 PARTITION OF vendas
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE vendas_2024_q2 PARTITION OF vendas
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Consulta automaticamente escaneia apenas a partição relevante
SELECT * FROM vendas WHERE data BETWEEN '2024-02-01' AND '2024-02-28';

VACUUM e ANALYZE

Manutenção essencial para performance consistente:

-- Verificar necessidade de vacuum
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'vendas';

-- Vacuum manual em horário de baixa carga
VACUUM (VERBOSE, ANALYZE) vendas;

-- Ajustar autovacuum para tabelas críticas
ALTER TABLE vendas SET (autovacuum_vacuum_scale_factor = 0.01,
                        autovacuum_analyze_scale_factor = 0.005);

Reindexação e CLUSTER

Reduz fragmentação e melhora localidade dos dados:

-- Reindexar sem bloquear escritas (concorrente)
REINDEX INDEX CONCURRENTLY idx_vendas_data;

-- Reorganizar fisicamente a tabela pelo índice (bloqueia escritas)
CLUSTER vendas USING idx_vendas_data;

6. Uso Avançado de Funções e Índices Compostos

Índices Multicoluna

A ordem das colunas importa: coloque primeiro a mais seletiva:

-- Bom: data é mais seletiva que status
CREATE INDEX idx_vendas_data_status ON vendas (data, status);

-- Ruim: status tem baixa cardinalidade, o índice será pouco usado
CREATE INDEX idx_vendas_status_data ON vendas (status, data);

-- Consulta que usa o índice composto
SELECT * FROM vendas WHERE data >= '2024-01-01' AND status = 'ativo';

Índices de Expressão com Funções Agregadas

Para consultas de contagem frequentes:

-- Índice parcial para contar vendas ativas rapidamente
CREATE INDEX idx_vendas_ativas_count ON vendas (data) WHERE status = 'ativo';

-- count(*) agora escaneia apenas o índice (Index Only Scan)
SELECT count(*) FROM vendas WHERE status = 'ativo' AND data >= '2024-01-01';

Identificando Queries Lentas com pg_stat_statements

Ative a extensão e encontre os maiores gargalos:

-- Habilitar (requer superusuário)
CREATE EXTENSION pg_stat_statements;

-- Top 5 queries mais lentas por tempo total
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

-- Resetar estatísticas para nova medição
SELECT pg_stat_statements_reset();

Referências