Otimização de Queries no PostgreSQL: De Lento a Sub-Milissegundo
As técnicas de otimização do PostgreSQL que uso em bancos de produção. EXPLAIN ANALYZE, estratégias de índice, reescrita de queries e as mudanças exatas que cortaram tempos de segundos para microssegundos.
No mês passado recebi uma mensagem no Slack às 2 da manhã: "O dashboard está dando timeout." Fiz SSH na máquina de produção, abri o pg_stat_activity e encontrei uma única query escaneando 14 milhões de linhas para retornar 12 resultados. A correção foi um partial index que levou 30 segundos para criar. A query foi de 4.2 segundos para 0.3 milissegundos.
É assim que funciona com performance no PostgreSQL. Os problemas quase nunca são exóticos. São índices esquecidos, estatísticas ruins, queries que faziam sentido quando a tabela tinha 10.000 linhas mas agora tem 10 milhões. As correções geralmente são simples — quando você sabe onde procurar.
Este post é tudo que aprendi sobre otimização de queries no PostgreSQL rodando bancos de dados em produção. Sem teoria sem prática. Cada técnica aqui me poupou tempo real em sistemas reais.
A Mentalidade de Debugging: Não Adivinhe, Meça#
O maior erro que vejo desenvolvedores cometerem com queries lentas é adivinhar. "Talvez precisemos de um índice naquela coluna." "Talvez o JOIN é lento." "Talvez devíamos adicionar mais RAM."
Pare de adivinhar. O PostgreSQL tem um dos melhores analisadores de query de qualquer banco de dados. Use-o.
EXPLAIN — O Blueprint#
O EXPLAIN simples mostra o que o PostgreSQL planeja fazer, sem realmente executar a query:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
Index Cond: (customer_id = 42)
Isso mostra o plano — um index scan — e o custo estimado. Mas é uma previsão. O PostgreSQL pode estar completamente errado sobre a contagem de linhas. Já vi estimativas de "1 row" quando o resultado real era 50.000 linhas. Esse tipo de erro de estimativa cascateia em escolhas de plano terríveis.
Use o EXPLAIN simples quando quiser uma olhada rápida no plano sem realmente executar a query. Isso importa quando você está analisando um DELETE que modificaria dados, ou uma query que leva 30 minutos para rodar.
EXPLAIN ANALYZE — A Verdade#
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
(actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.085 ms
Execution Time: 0.041 ms
Agora você vê o tempo real de execução e a contagem real de linhas. É aqui que o debugging real acontece. Compare rows=1 (estimado) com rows=1 (real) — eles batem, então o planejador tomou uma boa decisão. Quando não batem, essa é sua primeira pista.
Aviso: EXPLAIN ANALYZE realmente executa a query. Se você está analisando um UPDATE ou DELETE, envolva em uma transação:
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;EXPLAIN (ANALYZE, BUFFERS) — O Quadro Completo#
Isso é o que eu realmente uso 90% do tempo:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
AND o.status = 'completed'; QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=425.00..1893.25 rows=2150 width=48)
(actual time=2.341..15.892 rows=2347 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=312 read=45
-> Bitmap Heap Scan on orders o (cost=12.50..1450.75 rows=2150 width=32)
(actual time=0.512..12.105 rows=2347 loops=1)
Recheck Cond: (created_at > '2026-01-01'::date)
Filter: (status = 'completed')
Rows Removed by Filter: 893
Heap Blocks: exact=189
Buffers: shared hit=195 read=45
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..11.96 rows=3043 width=0)
(actual time=0.287..0.287 rows=3240 loops=1)
Index Cond: (created_at > '2026-01-01'::date)
Buffers: shared hit=12
-> Hash (cost=287.50..287.50 rows=10000 width=24)
(actual time=1.753..1.753 rows=10000 loops=1)
Buffers: shared hit=117
-> Seq Scan on customers c (cost=0.00..287.50 rows=10000 width=24)
(actual time=0.008..0.892 rows=10000 loops=1)
Buffers: shared hit=117
Planning Time: 0.215 ms
Execution Time: 16.147 ms
A saída BUFFERS é crítica. shared hit=312 significa que 312 páginas vieram do buffer cache (RAM). read=45 significa que 45 páginas tiveram que ser lidas do disco. Se você vê muito read relativo a hit, seu shared_buffers pode estar pequeno demais, ou a tabela é grande demais para ficar em cache.
Eu também uso FORMAT JSON quando preciso colar o plano no explain.dalibo.com para visualização. A view em árvore torna planos complexos muito mais fáceis de ler:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;Opções do EXPLAIN Que Eu Realmente Uso#
Aqui está a query de diagnóstico completa que rodo primeiro para qualquer investigação de query lenta:
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;TIMING— timing por nó (ligado por padrão comANALYZE, mas eu sou explícito)VERBOSE— mostra listas de colunas de saída e nomes de tabela com schema
Lendo Planos de Query Como um Detetive#
Todo plano de query é uma árvore. O PostgreSQL lê de baixo para cima: os nós com maior indentação executam primeiro, alimentando resultados para cima. Entender os tipos de scan é a base de todo o resto.
Seq Scan — O Scan Completo da Tabela#
Seq Scan on orders (cost=0.00..25000.00 rows=1000000 width=128)
Sequential scan lê cada linha da tabela. Para uma tabela de um milhão de linhas, são todas as páginas em disco.
Mas aqui vai a nuance: Seq Scan nem sempre é ruim. Se você está selecionando 30% ou mais da tabela, um sequential scan é na verdade mais rápido que um index scan porque I/O sequencial é muito mais rápido que I/O aleatório. O PostgreSQL sabe disso. Se ele está escolhendo um Seq Scan quando você tem um índice, verifique quão seletiva sua cláusula WHERE realmente é.
Quando é um problema: quando você está selecionando uma fração pequena de linhas de uma tabela grande e não tem índice.
Index Scan — A Busca Direcionada#
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=5 width=128)
Index Cond: (customer_id = 42)
Vai ao índice B-tree, encontra as entradas correspondentes, depois busca as linhas reais do heap da tabela. Cada busca de linha é uma operação de I/O aleatório no heap. Isso é ótimo para queries altamente seletivas, mas cada busca no heap tem um custo.
Index Only Scan — O Santo Graal#
Index Only Scan using idx_orders_customer_total on orders (cost=0.43..4.15 rows=5 width=12)
Index Cond: (customer_id = 42)
Heap Fetches: 0
Esse é o tipo de scan mais rápido. O PostgreSQL obtém tudo que precisa apenas do índice, sem nunca tocar o heap da tabela. Você vê isso quando todas as colunas que você faz SELECT e WHERE estão no índice.
O detalhe: Heap Fetches: 0 significa que o visibility map está atualizado. Se sua tabela tem muitas dead tuples (não recebeu vacuum recentemente), o PostgreSQL ainda precisa verificar o heap para confirmar a visibilidade da linha. Essa é uma razão pela qual o autovacuum importa para performance, não só para espaço em disco.
Bitmap Scan — O Meio Termo#
Bitmap Heap Scan on orders (cost=45.00..1250.00 rows=5000 width=128)
Recheck Cond: (created_at > '2026-01-01')
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..43.75 rows=5000 width=0)
Index Cond: (created_at > '2026-01-01')
Bitmap scans são a resposta do PostgreSQL para o problema "linhas demais para um index scan, poucas demais para um seq scan". Ele constrói um bitmap de quais páginas contêm linhas correspondentes, ordena por localização física, depois busca em ordem. Isso converte I/O aleatório em I/O sequencial.
Você frequentemente verá bitmap scans quando dois ou mais índices são combinados:
Bitmap Heap Scan on orders
Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
-> BitmapAnd
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'completed')
-> Bitmap Index Scan on idx_orders_created_at
Index Cond: (created_at > '2026-01-01')
Esse BitmapAnd combina dois índices separados. É o PostgreSQL dizendo "não tenho um índice composto, mas posso combinar esses dois índices de coluna única." Funciona, mas um índice composto adequado seria mais rápido.
Identificando Estimativas Ruins#
A coisa número um que procuro em um plano de query são estimativas incompatíveis:
Nested Loop (cost=0.43..50.12 rows=1 width=128)
(actual time=0.025..4521.337 rows=48753 loops=1)
O planejador estimou 1 linha. O resultado real foi 48.753 linhas. Isso é um erro de estimativa de cinco ordens de magnitude. O planejador escolheu um Nested Loop porque achou que estava fazendo join contra 1 linha. Com 48.753 linhas, um Hash Join teria sido ordens de magnitude mais rápido.
Causas comuns de estimativas ruins:
- Estatísticas desatualizadas: Execute
ANALYZEna tabela - Colunas correlacionadas: O planejador assume que os valores das colunas são independentes. Se
status = 'shipped'ecreated_at > '2026-01-01'são correlacionados (pedidos mais recentes estão enviados), o planejador subestima a seletividade combinada - Funções customizadas no WHERE: O planejador usa uma estimativa de seletividade padrão (geralmente 0.5% para igualdade, 33% para intervalo) quando não consegue analisar uma função
- Queries parametrizadas com planos genéricos: Após 5 execuções, o PostgreSQL pode mudar para um plano genérico que não considera o valor real do parâmetro
Quando você vê estimativas ruins, a correção geralmente é uma de: executar ANALYZE, criar estatísticas estendidas, reescrever a query ou usar um CTE como barreira de otimização.
-- Criar estatísticas estendidas para colunas correlacionadas
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;Estratégia de Índices: O Índice Certo para o Trabalho Certo#
Índices não são gratuitos. Cada índice desacelera escritas, consome espaço em disco e precisa de manutenção. O objetivo não é "indexar tudo" — é "indexar exatamente o que você precisa."
B-tree — O Cavalo de Batalha Padrão#
B-tree é o padrão e lida com a grande maioria dos casos. Ele suporta operadores de igualdade e intervalo (=, <, >, <=, >=, BETWEEN, IN, IS NULL).
CREATE INDEX idx_orders_created_at ON orders (created_at);A ordem das colunas no índice B-tree importa enormemente para índices compostos. A coluna mais à esquerda é a ordenação primária, depois a próxima, e assim por diante. Um índice em (a, b, c) pode eficientemente responder:
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b > 5WHERE a = 1 ORDER BY b
Ele não pode eficientemente responder:
WHERE b = 2(pula a primeira coluna)WHERE c = 3(pula as duas primeiras colunas)WHERE a = 1 AND c = 3(buraco no meio — condição deausada, condição decrequer filtro)
Pense como uma lista telefônica ordenada por sobrenome, depois nome. Você pode rapidamente encontrar todas as entradas "Silva", ou "Silva, João" especificamente. Mas não pode rapidamente encontrar todas as entradas "João" sem escanear o livro inteiro.
A regra: coloque condições de igualdade primeiro, depois condições de intervalo, depois colunas de ordenação.
-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
-- Bom: igualdade primeiro, depois intervalo, depois ordenação
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
-- Ruim: intervalo antes de igualdade
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);Covering Indexes com INCLUDE#
PostgreSQL 11+ permite adicionar colunas não-chave a um índice com INCLUDE. Essas colunas são armazenadas nas páginas folha mas não fazem parte da estrutura B-tree. Elas habilitam index-only scans sem inflar a árvore:
-- Executamos frequentemente:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);Agora o PostgreSQL pode satisfazer a query inteira apenas pelo índice. Sem INCLUDE, ele precisaria buscar total e status do heap. A diferença em um cold cache pode ser dramática — já vi queries irem de 50ms para 0.2ms apenas adicionando colunas INCLUDE.
Partial Indexes — Indexe Apenas o Que Importa#
Essa é minha feature favorita do PostgreSQL e a que a maioria dos desenvolvedores não conhece.
-- Apenas 2% dos pedidos são 'pending', mas consultamos constantemente
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';Esse índice é minúsculo comparado a um índice completo em created_at. Ele contém apenas linhas onde status = 'pending'. Queries que correspondem à cláusula WHERE usam esse índice pequeno e rápido. Queries que não correspondem ignoram.
Exemplo real de produção: eu tinha uma tabela sessions com 50 milhões de linhas. Apenas cerca de 200.000 estavam ativas (não expiradas). Um índice completo em user_id tinha 1.2 GB. Um partial index WHERE expires_at > now() tinha 8 MB. A query foi de 12ms para 0.1ms porque o índice inteiro cabia em cache.
-- Outro padrão comum: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
-- Constraint unique apenas em registros ativos
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;Esse último é incrivelmente útil. Permite ter uma constraint de email único em usuários ativos enquanto permite que o mesmo email apareça em registros deletados.
GIN Indexes — Full-Text Search e JSONB#
GIN (Generalized Inverted Index) é a resposta quando você precisa buscar dentro de valores — arrays, documentos JSONB ou vetores de full-text search.
-- Queries de containment JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Agora isso é rápido:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));Índices GIN são grandes e lentos para construir, mas extremamente rápidos para consultar. Valem a pena para workloads pesados em leitura com queries de containment complexas.
Para JSONB, existe também o jsonb_path_ops que é menor e mais rápido para queries de containment @>, mas não suporta operadores de existência (?, ?|, ?&):
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);GiST Indexes — Tipos Geométricos e de Intervalo#
GiST (Generalized Search Tree) lida com tipos de dados sobrepostos: formas geométricas, ranges, full-text search (alternativa ao GIN, menor mas mais lento).
-- Lookups de faixas de IP
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
-- Ranges temporais (conflitos de agendamento)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
tstzrange(start_time, end_time)
);
-- Encontrar agendamentos sobrepostos:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');Hash Indexes — Nicho mas Úteis#
Hash indexes são úteis apenas para comparações de igualdade. Desde o PostgreSQL 10 eles são WAL-logged e crash-safe. São menores que B-tree para colunas largas e ligeiramente mais rápidos para igualdade pura:
-- Bom para lookups de igualdade em colunas de texto grandes
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
-- Isso é rápido:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
-- Isso NÃO PODE usar o hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';Na prática, eu raramente uso hash indexes. B-tree lida com igualdade perfeitamente, e a incapacidade de fazer queries de intervalo ou ordenação torna os hash indexes inflexíveis demais para a maioria dos casos de uso reais.
Quando Índices Prejudicam#
Todo índice tem um custo:
- Write amplification: Cada INSERT atualiza todo índice na tabela. Uma tabela com 8 índices significa 8 escritas adicionais por INSERT
- HOT updates bloqueados: Heap-Only Tuple (HOT) updates são uma otimização importante onde o PostgreSQL pode atualizar uma linha sem atualizar os índices, mas apenas se nenhuma coluna indexada mudou. Mais índices = mais chances de bloquear HOT updates
- Overhead de vacuum: Mais índices significa vacuum mais demorado
- Overhead do planejador: Mais índices significa mais opções para o planejador avaliar
Eu regularmente audito índices em tabelas de produção:
-- Encontrar índices não utilizados
SELECT
schemaname, tablename, indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u') -- excluir primary key e unique constraints
)
ORDER BY pg_relation_size(indexrelid) DESC;Já encontrei índices de vários gigabytes que foram criados durante uma migração pontual e nunca mais usados. Removê-los acelerou as escritas perceptivelmente.
-- Encontrar índices duplicados (mesmas colunas, mesma ordem)
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid < b.indexrelid
AND a.indkey = b.indkey;O Problema de Query N+1#
Todo desenvolvedor de ORM esbarra nisso eventualmente. É o problema de performance mais comum que eu debugo.
Como o N+1 Se Parece#
# Python / SQLAlchemy — a armadilha clássica
orders = session.query(Order).filter_by(status='pending').all()
for order in orders:
# Isso dispara uma query separada para CADA pedido
print(f"Customer: {order.customer.name}")Isso gera:
-- Query 1: Buscar todos os pedidos pendentes
SELECT * FROM orders WHERE status = 'pending';
-- Query 2: Buscar cliente do pedido 1
SELECT * FROM customers WHERE id = 101;
-- Query 3: Buscar cliente do pedido 2
SELECT * FROM customers WHERE id = 102;
-- ...Query 502: Buscar cliente do pedido 500
SELECT * FROM customers WHERE id = 600;501 queries em vez de 1 ou 2. Cada query é rápida individualmente, talvez 0.5ms. Mas 501 delas somam 250ms só de tempo de banco de dados, mais latência de round-trip de rede para cada uma.
Detectando N+1 nos Logs#
A forma mais rápida de pegar queries N+1 é habilitar logging de statements temporariamente:
-- Logar todas as queries que levam mais de 0ms (ou seja, todas)
SET log_min_duration_statement = 0;
-- Melhor para produção: logar apenas queries lentas
ALTER SYSTEM SET log_min_duration_statement = 50; -- threshold de 50ms
SELECT pg_reload_conf();Depois olhe os logs. N+1 é inconfundível — você verá centenas de queries idênticas com valores de parâmetro diferentes em rápida sucessão.
Uma abordagem mais direcionada para desenvolvimento:
-- Habilitar auto_explain para queries lentas
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100; -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;Isso loga a saída completa do EXPLAIN ANALYZE para qualquer query acima de 100ms, incluindo queries dentro de funções.
A Correção: Eager Loading ou JOINs#
Abordagem via ORM — diga ao ORM para carregar dados relacionados antecipadamente:
# SQLAlchemy — joinedload busca clientes na mesma query
from sqlalchemy.orm import joinedload
orders = (
session.query(Order)
.options(joinedload(Order.customer))
.filter_by(status='pending')
.all()
)Abordagem via SQL puro — simplesmente use um JOIN:
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';Uma query. Feito.
JOINs vs Múltiplas Queries#
Existe um debate válido aqui. Às vezes duas queries são melhores que um JOIN:
-- Abordagem 1: JOIN único (pode produzir dados duplicados se one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
-- Abordagem 2: Duas queries (menos transferência de dados se orders tem muitas colunas)
SELECT * FROM orders WHERE status = 'pending';
-- aplicação coleta order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);A abordagem com JOIN duplica dados do pedido para cada item. Se cada pedido tem 20 itens e a linha do pedido é larga, são 20x de transferência de dados. A abordagem com duas queries envia cada pedido exatamente uma vez.
Minha regra: use JOINs para relacionamentos one-to-one, considere queries separadas para one-to-many quando o lado "one" é largo. Mas sempre faça benchmark — o round-trip de rede de uma segunda query frequentemente custa mais que os dados duplicados.
Reescritas Comuns de Queries#
Algumas queries são lentas não por falta de índices mas por como são escritas. O planejador do PostgreSQL é bom, mas não é mágica.
Subquery vs JOIN vs CTE#
Essas três abordagens podem produzir planos muito diferentes:
-- Subquery no WHERE — frequentemente ok, às vezes terrível
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'US'
);
-- JOIN — geralmente a melhor escolha
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
-- CTE — materializado no PG 11 e abaixo, otimizado no PG 12+
WITH us_customers AS (
SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;No PostgreSQL 12+, CTEs geralmente são inlined (o planejador os trata como subqueries), então a performance é idêntica. Mas no PostgreSQL 11 e abaixo, CTEs são barreiras de otimização — o planejador os materializa e não consegue empurrar predicados através deles. Se você ainda está no PG 11, evite CTEs para queries críticas de performance.
EXISTS vs IN vs JOIN#
Isso aparece constantemente:
-- EXISTS — para na primeira correspondência, ótimo para verificar existência
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM returns r WHERE r.order_id = o.id
);
-- IN com subquery — constrói o resultado completo primeiro
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
-- JOIN — pode produzir duplicatas se returns tem múltiplas linhas por pedido
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;Para tabelas grandes, EXISTS frequentemente vence porque faz short-circuit. A versão com IN precisa construir a lista inteira de order_ids de retorno antes de filtrar. A versão com JOIN pode produzir duplicatas, requerendo DISTINCT que adiciona um passo de sort ou hash.
Meu padrão: use EXISTS quando estiver verificando a existência de linhas relacionadas. É o mais semanticamente claro e geralmente o mais rápido.
Mas existe um contra-exemplo. Se o resultado da subquery é pequeno e você precisa dele para múltiplas condições:
-- Se a subquery retorna poucas linhas, IN é perfeitamente ok
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);Evitando SELECT *#
Isso não é apenas preferência de estilo de código. Tem implicações reais de performance:
-- Ruim: busca todas as 25 colunas incluindo uma coluna TEXT com kilobytes de dados
SELECT * FROM products WHERE category = 'electronics';
-- Bom: busca apenas o que precisa
SELECT id, name, price FROM products WHERE category = 'electronics';Com SELECT *:
- Não consegue usar index-only scans (todas as colunas precisariam estar no índice)
- Transfere mais dados pela rede
- Usa mais memória para ordenação e hashing
- Se alguém adicionar uma coluna BYTEA de 10MB depois, suas queries existentes ficam silenciosamente mais lentas
Window Functions vs Subqueries#
Window functions são uma das features mais poderosas do PostgreSQL e quase sempre superam subqueries correlacionadas:
-- Lento: subquery correlacionada executa uma vez por linha
SELECT
o.*,
(SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
(SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
-- Rápido: window functions computam em um único passo
SELECT
o.*,
COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';Outro padrão comum — pegar a última linha por grupo:
-- Lento: subquery correlacionada
SELECT * FROM notifications n
WHERE n.created_at = (
SELECT MAX(created_at) FROM notifications
WHERE user_id = n.user_id
);
-- Rápido: DISTINCT ON (específico do PostgreSQL, extremamente útil)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
-- Também rápido: window function ROW_NUMBER
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM notifications
) sub
WHERE rn = 1;DISTINCT ON é minha escolha padrão para esse padrão. É conciso, legível, e o PostgreSQL o otimiza bem com o índice certo:
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);Paginação Feita Direito#
OFFSET é uma armadilha para datasets grandes:
-- Página 1: rápido
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Página 500: lento — PostgreSQL precisa escanear e descartar 9.980 linhas
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
-- Página 5000: muito lento — escaneia 99.980 linhas para retornar 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;A correção é keyset pagination (também chamada de paginação baseada em cursor):
-- Primeira página
SELECT * FROM products ORDER BY id LIMIT 20;
-- Próxima página: use o último id da página anterior
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;Isso é sempre rápido porque usa o índice para pular diretamente para a posição certa, independente de qual "página" você está. O trade-off é que você não pode pular para um número de página arbitrário, mas para infinite scroll ou UIs de "próxima página", keyset pagination é estritamente superior.
Para ordens de classificação complexas:
-- Keyset pagination com múltiplas colunas de ordenação
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;Isso usa comparação de row-value, que o PostgreSQL trata eficientemente com um índice composto em (price, id).
Estatísticas de Tabela e Vacuuming#
O planejador de queries do PostgreSQL toma decisões baseadas em estatísticas sobre seus dados. Estatísticas ruins levam a planos ruins. É simples assim.
ANALYZE: Atualize as Estatísticas#
-- Analisar uma única tabela
ANALYZE orders;
-- Analisar o banco inteiro
ANALYZE;
-- Ver estatísticas atuais de uma coluna
SELECT
tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';O valor correlation é particularmente interessante. Vai de -1 a 1 e mede quão bem a ordem física das linhas corresponde à ordem lógica da coluna. Uma correlação próxima de 1 ou -1 significa que os dados estão fisicamente ordenados naquela coluna, tornando range scans muito eficientes (I/O sequencial). Uma correlação próxima de 0 significa I/O aleatório para queries de intervalo.
-- Aumentar o target de estatísticas para colunas com distribuições enviesadas
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;O target de estatísticas padrão é 100, significando que o PostgreSQL amostra 300 * 100 = 30.000 linhas. Para colunas com muitos valores distintos ou distribuições enviesadas, aumentar para 500 ou 1000 dá ao planejador dados melhores ao custo de tempos de ANALYZE ligeiramente maiores.
Tuning do Autovacuum#
O autovacuum faz duas coisas: recupera espaço de dead tuples (linhas deletadas ou atualizadas) e atualiza estatísticas. Em tabelas com muito tráfego, as configurações padrão do autovacuum são frequentemente conservadoras demais.
-- Verificar status do autovacuum
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;Se você vê tabelas com milhões de dead tuples e o último vacuum foi há horas, seu autovacuum está ficando para trás.
Para tabelas de alto churn (como sessions, filas de jobs ou métricas), eu defino configurações de autovacuum por tabela:
-- Autovacuum mais agressivo para tabelas com muita escrita
ALTER TABLE sessions SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum com 1% de dead tuples (padrão 20%)
autovacuum_analyze_scale_factor = 0.005, -- analyze com 0.5% de mudanças (padrão 10%)
autovacuum_vacuum_cost_delay = 2 -- vacuum mais rápido (padrão 2ms em PG mais novo)
);Table Bloat#
Quando o PostgreSQL atualiza uma linha, ele não modifica a linha no lugar — cria uma nova versão e marca a antiga como morta. O vacuum recupera as linhas mortas, mas o espaço só é reutilizado por aquela tabela. O arquivo da tabela em disco não encolhe.
Com o tempo, uma tabela muito atualizada pode ter bloat significativo — a tabela é muito maior em disco do que os dados vivos requerem. Isso significa mais páginas para escanear, mais I/O, mais pressão no cache.
-- Estimar table bloat (versão simplificada)
SELECT
current_database(),
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(
pg_total_relation_size(schemaname || '.' || tablename) -
pg_relation_size(schemaname || '.' || tablename)
) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;Para tabelas severamente inchadas, a opção nuclear é VACUUM FULL — ele reescreve a tabela inteira. Mas pega um lock exclusivo, então você não pode fazer isso em uma tabela de produção ao vivo sem downtime. A abordagem melhor é pg_repack, que faz a mesma coisa sem lock:
pg_repack --table orders --no-kill-backend -d mydbConnection Pooling#
Aqui está algo que surpreende muitos desenvolvedores: conexões PostgreSQL são caras. Cada conexão gera um novo processo (não uma thread), consome cerca de 5-10 MB de memória e tem um overhead de fork não-trivial.
O max_connections padrão é 100. Se você tem um servidor de aplicação com 20 workers, cada um abrindo 5 conexões, já está no limite. Adicione um processador de jobs em background, uma ferramenta de monitoramento e uma migração rodando em algum lugar, e você está em apuros.
Por Que Você Precisa de um Connection Pooler#
Sem um pooler, se sua aplicação precisa lidar com 500 requisições concorrentes, você precisa de 500 conexões PostgreSQL. São 5 GB de memória só para overhead de conexão, e a performance do PostgreSQL degrada significativamente além de algumas centenas de conexões devido ao overhead de gerenciamento de processos.
Com o PgBouncer na frente do PostgreSQL, essas 500 conexões de aplicação mapeiam para talvez 20 conexões reais do PostgreSQL. O pooler enfileira requisições quando todas as conexões do banco estão ocupadas.
Configuração do PgBouncer#
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Modo de pool
pool_mode = transaction
; Dimensionamento do pool
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30Modo Transaction vs Session#
- Modo transaction (
pool_mode = transaction): Conexões são retornadas ao pool após cada transação. Isso é o que você quer 95% do tempo. Reutilização máxima de conexão. - Modo session (
pool_mode = session): Conexões são mantidas durante toda a sessão do cliente. Use quando precisar de prepared statements, comandosSET,LISTEN/NOTIFY, ou outras features de nível de sessão. - Modo statement (
pool_mode = statement): Conexões são retornadas após cada statement. Restritivo demais para a maioria das aplicações — você nem consegue usar transações explícitas.
A pegadinha com o modo transaction: você não pode usar prepared statements (são estado de nível de sessão), não pode usar SET para variáveis de sessão, e LISTEN/NOTIFY não funcionará como esperado. A maioria dos ORMs tem uma forma de desabilitar prepared statements.
Para Node.js com o driver pg:
const pool = new Pool({
host: '127.0.0.1',
port: 6432, // Porta do PgBouncer, não do PostgreSQL
database: 'mydb',
max: 20, // corresponde ao default_pool_size do PgBouncer
statement_timeout: 30000,
// Desabilitar prepared statements para modo transaction do PgBouncer
prepare: false,
});Fórmula de Dimensionamento do Pool#
Existe uma fórmula comum para o tamanho ideal do pool de conexões PostgreSQL:
optimal_connections = (core_count * 2) + effective_spindle_count
Para um servidor moderno com 4 cores e um SSD (1 effective spindle):
optimal = (4 * 2) + 1 = 9
Isso parece contra-intuitivamente baixo. Mas o PostgreSQL é CPU-bound na maioria do hardware moderno (SSDs são rápidos o suficiente para que I/O raramente seja gargalo). Mais conexões que isso leva a overhead de context switching que na verdade desacelera as coisas.
Na prática, eu geralmente defino default_pool_size para 2-3x esse número para lidar com picos de tráfego, com o entendimento de que no pico de carga, algumas queries vão esperar na fila do PgBouncer em vez de todas atingirem o PostgreSQL simultaneamente.
Checklist Prático: Os Passos Exatos para Toda Query Lenta#
Aqui está meu processo real quando recebo um relatório de "essa query está lenta". Sigo esses passos nesta ordem, toda vez.
Passo 1: Pegue a Query Real#
Não "o endpoint está lento" — o SQL real. Se você está usando um ORM, habilite logging de query:
-- Logar temporariamente todas as queries acima de 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();Ou verifique o pg_stat_statements para as top queries por tempo total:
-- Top 10 queries por tempo total de execução
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;pg_stat_statements é a extensão mais valiosa para performance do PostgreSQL. Se você não está rodando, instale agora:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;Passo 2: Execute EXPLAIN (ANALYZE, BUFFERS)#
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <a query lenta>;Procure por:
- Estimativas de linhas ruins — linhas reais muito diferentes das estimadas
- Seq Scans em tabelas grandes — possível índice faltando
- Nested Loops com muitas linhas — deveria ser um Hash Join ou Merge Join
- Muitas leituras de buffer — cold cache ou tabela grande demais
- Operações de sort vazando para disco — aumente
work_memou adicione índice para ordenação
Passo 3: Verifique Estatísticas da Tabela#
-- Quando as estatísticas foram atualizadas por último?
SELECT
relname,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';Se last_analyze é antigo ou n_dead_tup é alto relativo a n_live_tup, execute:
ANALYZE orders;Depois re-execute EXPLAIN ANALYZE. Se o plano mudar, estatísticas desatualizadas eram o problema.
Passo 4: Verifique Índices Existentes#
-- Quais índices existem nessa tabela?
SELECT
indexname,
indexdef,
idx_scan,
idx_tup_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;Talvez o índice existe mas não está sendo usado. Talvez a ordem das colunas está errada para sua query.
Passo 5: Crie ou Modifique Índices#
Baseado no plano de query, crie o índice apropriado. Teste com EXPLAIN ANALYZE antes e depois.
-- Crie o índice concorrentemente (não bloqueia a tabela)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
-- Verifique se o índice está sendo usado
EXPLAIN (ANALYZE, BUFFERS) <a query lenta>;Sempre use CONCURRENTLY em tabelas de produção. Um CREATE INDEX regular pega um lock completo na tabela que bloqueia todas as escritas.
Passo 6: Considere Reescritas de Query#
Se o índice existe e as estatísticas estão frescas mas a query ainda é lenta, olhe para a query em si:
- Uma subquery pode ser reescrita como JOIN?
OFFSETestá causando problemas? Troque para keyset pagination- Você está selecionando mais colunas que o necessário?
- Uma subquery correlacionada pode se tornar uma window function?
- Um CTE está impedindo o planejador de otimizar?
Passo 7: Verifique Configurações do Servidor#
Para padrões de query específicos, configurações do servidor importam:
-- work_mem: memória para sorts e hash joins (por operação, não por conexão)
-- Padrão é 4MB, que é baixo demais para queries complexas
SET work_mem = '64MB'; -- tente isso e re-execute EXPLAIN ANALYZE
-- effective_cache_size: diz ao planejador quanto cache de disco esperar
-- Defina como ~75% da RAM total
SHOW effective_cache_size;
-- random_page_cost: razão de I/O aleatório para I/O sequencial
-- Padrão é 4.0, defina como 1.1-1.5 para SSDs
SHOW random_page_cost;Se mudar work_mem corrige um problema de sort-to-disk, considere aumentar globalmente. Mas cuidado — é por operação, não por conexão. Uma query complexa com 10 operações de sort e work_mem = 256MB poderia usar 2.5 GB para uma única query.
Passo 8: Monitore Após a Correção#
Não corrija e esqueça. Verifique se a correção se mantém:
-- Resete pg_stat_statements para ver dados frescos após suas mudanças
SELECT pg_stat_statements_reset();
-- Verifique em uma hora/dia
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round(max_exec_time::numeric, 2) AS max_time_ms,
query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;Bônus: Quick Wins Que Aplico em Todo Banco Novo#
Essas são configurações e práticas que aplico em todo banco PostgreSQL que configuro, antes de qualquer problema de performance surgir:
-- 1. Instalar pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2. Habilitar logging de queries lentas (50ms é meu threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
-- 3. Configurações apropriadas para SSD
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
-- 4. Aumentar work_mem do padrão anêmico
ALTER SYSTEM SET work_mem = '32MB';
-- 5. Definir effective_cache_size como 75% da RAM
-- Para um servidor de 16GB:
ALTER SYSTEM SET effective_cache_size = '12GB';
-- 6. Definir shared_buffers como 25% da RAM
-- Para um servidor de 16GB:
ALTER SYSTEM SET shared_buffers = '4GB';
-- 7. Melhorar responsividade do autovacuum
ALTER SYSTEM SET autovacuum_naptime = '10s';
-- Aplicar mudanças
SELECT pg_reload_conf();
-- Nota: shared_buffers requer restartE uma query de monitoramento que rodo semanalmente:
-- Tabelas que precisam de atenção
SELECT
schemaname || '.' || relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;Considerações Finais#
Otimização de queries no PostgreSQL não é uma arte obscura. É um processo sistemático:
- Meça — não adivinhe. EXPLAIN (ANALYZE, BUFFERS) é seu melhor amigo.
- Entenda o plano — aprenda a ler tipos de scan, tipos de join e estimativas de linhas.
- Indexe estrategicamente — o índice certo nas colunas certas, na ordem certa. Partial indexes e covering indexes são superpoderes subutilizados.
- Escreva queries melhores — EXISTS em vez de IN para verificações de existência, keyset pagination em vez de OFFSET, window functions em vez de subqueries correlacionadas.
- Mantenha o banco — tuning do autovacuum, atualizações de estatísticas, connection pooling.
- Monitore continuamente — pg_stat_statements te diz onde seu banco gasta tempo. Verifique regularmente.
A diferença entre uma query que leva 4 segundos e uma que leva 0.3 milissegundos raramente é hardware. É quase sempre conhecimento — saber onde procurar e o que mudar. E agora você sabe onde procurar.