Melhores práticas de SQL no PostgreSQL: escreva queries que escalam e leia EXPLAIN como um profissional

Problemas de performance de query no PostgreSQL raramente são exóticos. São quase sempre os mesmos punhados de padrões: SELECT * em uma tabela larga, uma chamada de função em uma coluna indexada, um OR que impede uso de índice, um cast implícito de tipo que silenciosamente desabilita um filtro. Este post coleta as regras que previnem 90% desses problemas, depois percorre uma saída real de EXPLAIN (ANALYZE, BUFFERS) linha por linha para você ver o que o planner está te dizendo.

Regra 1: nunca SELECT * em produção

SELECT * lê cada coluna, incluindo valores expandidos em TOAST, colunas JSONB grandes, e blobs bytea que você não precisa. Em uma tabela com 40 colunas onde você só usa 3, você está lendo 13× mais dados do que o necessário do disco para a memória. Isso não é preferência de estilo — afeta diretamente I/O, memória e transferência de rede. Nomeie as colunas que você precisa:

-- Ruim: lê 40 colunas, 200 bytes por linha, 2 MB para 10.000 linhas
SELECT * FROM orders WHERE customer_id = 42;

-- Bom: lê 3 colunas, 24 bytes por linha, 240 KB para 10.000 linhas
SELECT id, status, total FROM orders WHERE customer_id = 42;

Regra 2: não envolva colunas indexadas em funções

Um índice em created_at não pode ser usado se você filtra por DATE(created_at). A função é uma caixa-preta para o planner — ele não consegue casar o índice com a expressão transformada. Reescreva o filtro para usar um range:

-- Ruim: índice em created_at é inutilizável; full table scan
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';

-- Bom: índice em created_at é usado; range scan
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';

Se você genuinamente precisa filtrar por uma expressão transformada com frequência, crie um índice funcional: CREATE INDEX idx_orders_date ON orders (DATE(created_at)). Mas geralmente, reescrever o filtro é mais simples e flexível.

Regra 3: OR é frequentemente um matador de índice

O PostgreSQL pode usar um índice para a = 1 OR b = 2 via BitmapOr, mas apenas se ambas as colunas forem independentemente indexadas — e mesmo assim, é menos eficiente que duas queries separadas unidas. Mais comumente, OR entre colunas força um sequential scan:

-- Ruim: provável Seq Scan se apenas uma das colunas for indexada
SELECT * FROM orders WHERE customer_id = 42 OR status = 'pending';

-- Bom: dois index scans, unidos
SELECT * FROM orders WHERE customer_id = 42
UNION
SELECT * FROM orders WHERE status = 'pending';

Se o OR é na mesma coluna, use IN — o planner trata como um único index scan com múltiplos valores de probe:

-- Ruim
SELECT * FROM orders WHERE status = 'pending' OR status = 'shipped' OR status = 'processing';

-- Bom
SELECT * FROM orders WHERE status IN ('pending', 'shipped', 'processing');

Regra 4: atenção a casts implícitos de tipo

Se order_number é VARCHAR e você escreve WHERE order_number = 12345, o PostgreSQL converte o literal inteiro para text — geralmente fine. Mas se id é BIGINT e você escreve WHERE id = '42', a direção do cast depende do contexto e pode impedir uso de índice em alguns casos. Sempre matching o tipo da coluna, ou faça cast explícito:

-- Arriscado: cast implícito pode ou não usar o índice
SELECT * FROM orders WHERE id = '42';

-- Seguro: explícito, sem ambiguidade
SELECT * FROM orders WHERE id = 42::bigint;

Regra 5: LIMIT sem ORDER BY é aleatório

SELECT * FROM orders LIMIT 10 sem ORDER BY retorna quaisquer 10 linhas que o planner encontrar primeiro. Pode ser as primeiras 10 por ordem física, ou 10 linhas do chunk de um worker paralelo. Não é determinístico. Sempre especifique um ORDER BY quando usar LIMIT, e garanta que a coluna de ordenação tenha índice.

Regra 6: use EXISTS em vez de IN para subqueries em conjuntos grandes

WHERE id IN (SELECT order_id FROM returns) materializa a subquery em uma lista e faz probe. EXISTS faz short-circuit na primeira correspondência e é frequentemente mais rápido quando a subquery retorna muitas linhas:

-- Mais lento em subqueries grandes
SELECT * FROM orders WHERE id IN (SELECT order_id FROM returns);

-- Mais rápido: short-circuit por linha
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.id);

Lendo EXPLAIN: um walkthrough completo

Agora o deep dive. Aqui está uma saída real de EXPLAIN (ANALYZE, BUFFERS) para uma query contra uma tabela orders de 10 milhões de linhas:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.status, 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 = 'shipped'
ORDER BY o.created_at DESC
LIMIT 50;

Limit  (cost=0.56..1247.32 rows=50 width=44) (actual time=0.038..1.842 rows=50 loops=1)
  Buffers: shared hit=1247
  ->  Nested Loop  (cost=0.56..89234.11 rows=3582 width=44) (actual time=0.037..1.835 rows=50 loops=1)
        Buffers: shared hit=1247
        ->  Index Scan using idx_orders_created_at_desc on orders o  (cost=0.43..78234.50 rows=3582 width=24) (actual time=0.022..1.210 rows=50 loops=1)
              Index Cond: (created_at >= '2026-01-01'::date)
              Filter: (status = 'shipped'::text)
              Rows Removed by Filter: 142
              Buffers: shared hit=1147
        ->  Index Scan using customers_pkey on customers c  (cost=0.13..0.15 rows=1 width=28) (actual time=0.009..0.011 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
              Buffers: shared hit=100
Planning Time: 0.124 ms
Execution Time: 1.876 ms

O que cada linha te diz

• Limit — o nó do topo. Parou após 50 linhas (o LIMIT 50). O actual time=0.038..1.842 significa que a primeira linha veio em 0.038 ms e a 50ª em 1.842 ms. Execução total: menos de 2 ms. Bom.
• Nested Loop — para cada linha de order, faz probe na tabela customers pela primary key. Eficiente quando a tabela externa retorna poucas linhas (aqui, 50 após o limit) e a tabela interna é indexada.
• Index Scan on orders — usou idx_orders_created_at_desc para satisfazer tanto o filtro WHERE created_at >= '2026-01-01' quanto o ORDER BY created_at DESC. Sem sort separado — o índice já está ordenado na direção certa.
• Filter: (status = 'shipped') — o índice em created_at não inclui status, então o Postgres lê a linha e aplica o filtro depois. Rows Removed by Filter: 142 significa que teve que checar 192 linhas (50 mantidas + 142 descartadas) para encontrar 50 shipped. Se esse número fosse 50.000, você precisaria de um índice composto em (created_at, status) ou um índice parcial em WHERE status = 'shipped'.
• Buffers: shared hit=1247 — todas as 1.247 páginas foram encontradas em shared buffers (RAM). Zero read significa zero I/O de disco. Se você vir shared read=800, são 800 páginas lidas do disco — um sinal de que ou a tabela não cabe no cache ou a query está escaneando demais.
• loops=50 no scan interno — o índice de customers foi probeado 50 vezes (uma por linha de order). actual time=0.009..0.011 é por loop, então o tempo total interno é ~0.55 ms. Fine.

Bandeiras vermelhas no EXPLAIN

• Seq Scan em tabela grande — se a tabela tem mais que alguns milhares de linhas e você vê Seq Scan onde esperava um índice, cheque seu filtro. Ou a coluna não é indexada, o filtro não é sargable (envolvido em função), ou o planner estima que o scan é mais barato que o índice (às vezes erroneamente, por estatísticas desatualizadas).
• Sort Method: external merge Disk — o sort não coube em work_mem e derramou para disco. Aumente work_mem para a sessão ou adicione um índice que forneça a ordem de sort.
• Rows Removed by Filter: 100000+ — o índice está encontrando linhas mas a maioria está sendo descartada. Você precisa de um índice mais seletivo (composto ou parcial).
• Hash Join com hash table enorme — se o lado de build tem milhões de linhas, a hash table pode não caber em work_mem e vai derramar para disco em batches. Considere filtrar mais cedo ou adicionar um índice para converter o Hash Join em Nested Loop.
• Planning Time > 100 ms — o planner está gastando tempo demais escolhendo um plano. Geralmente significa partições demais, views muito complexas, ou estatísticas desatualizadas causando estimativas ruins de cardinalidade.

A query que não vai escalar

Aqui está uma query que parece fine e vai destruir performance conforme a tabela cresce:

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
OFFSET 100000 LIMIT 20;

O PostgreSQL precisa produzir e descartar as primeiras 100.000 linhas antes de retornar 20. Em uma tabela de 10 milhões de linhas, a página 5.000 leva segundos. O conserto é paginação keyset — lembre o último created_at que você exibiu e peça a próxima fatia diretamente:

SELECT id, status, total FROM orders
WHERE customer_id = 42 AND created_at < :ultimo_created_at_visto
ORDER BY created_at DESC
LIMIT 20;

Apoiado por CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at DESC), toda página custa o mesmo — o banco salta direto para a posição do cursor em vez de contar do topo.

Juntando tudo

As regras acima não são acadêmicas. São a diferença entre uma query que roda em 2 ms por anos e uma que roda em 2 ms em dev, 200 ms em staging, e 20 segundos em produção — porque a tabela cresceu, o índice parou de ser usado, e ninguém notou. O PG Monitoring rastreia o plano e a latência de cada query ao longo do tempo, então quando uma query que estava em 2 ms de repente vira 200 ms porque o planner trocou de Index Scan para Seq Scan, você recebe um incidente — não uma reclamação de usuário.