Performance de Queries no PostgreSQL: O Guia Definitivo de EXPLAIN ANALYZE pra Devs que Já Cansaram

Seu endpoint de API demora 3 segundos. O dashboard dá timeout carregando. O job em background que "deveria demorar um minuto" tá rodando faz 45. Você abre o pgAdmin, olha pra query e pensa: "Isso deveria ser rápido. É só um SELECT."

Todo dev backend bate nesse muro uma hora ou outra. E a maioria perde tempo chutando: jogando índice aleatório, reescrevendo query no escuro, ou pior, culpando o ORM e seguindo em frente.

Este guia te dá a abordagem sistemática. Vamos cobrir como ler a saída do EXPLAIN ANALYZE de verdade, quais índices ajudam (e quais pioram), como encontrar e matar queries N+1, erros de connection pooling que destroem throughput sem você perceber, e o workflow exato de debug que funciona em produção.

A query tá lenta. E agora?

Antes de mexer em qualquer coisa, você precisa de dados. Não palpite. Dados.

Primeiro passo é sempre o mesmo: roda EXPLAIN ANALYZE.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 50;

O flag ANALYZE executa a query de verdade (não rode com operações destrutivas sem wrappear numa transação). BUFFERS mostra a atividade de I/O, a informação mais importante pra diagnosticar lentidão.

O resultado sai assim:

Limit  (cost=1247.83..1247.96 rows=50 width=86) (actual time=342.118..342.127 rows=50 loops=1)
  Buffers: shared hit=128 read=15234
  ->  Sort  (cost=1247.83..1302.45 rows=21847 width=86) (actual time=342.115..342.120 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (cost=412.50..876.32 rows=21847 width=86) (actual time=45.821..298.456 rows=21847 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..398.75 rows=21847 width=24) (actual time=0.021..187.345 rows=21847 loops=1)
                    Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
                    Rows Removed by Filter: 478153
                    Buffers: shared hit=0 read=14891
              ->  Hash  (cost=212.50..212.50 rows=10000 width=62) (actual time=45.678..45.678 rows=10000 loops=1)
                    Buffers: shared hit=128 read=343
                    ->  Seq Scan on customers c  (cost=0.00..212.50 rows=10000 width=62) (actual time=0.012..22.345 rows=10000 loops=1)

Parece complexo. Bora destrinchar.

Lendo EXPLAIN ANALYZE: As 5 coisas que realmente importam

1. Actual Time (não Cost)

Ignora cost. É a estimativa do planner, não a realidade. O que importa é actual time:

actual time=0.021..187.345 rows=21847

Primeiro número é tempo de startup. Segundo (187.345) é tempo total em milissegundos. Esse Seq Scan consome 187ms, 55% do tempo total da query.

2. Rows Removed by Filter

Essa é a prova de que tá faltando índice:

Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
Rows Removed by Filter: 478153

PostgreSQL escaneou ~500.000 linhas pra encontrar ~22.000. 95.6% de desperdício. Se Rows Removed by Filter é 10x ou mais que as linhas retornadas, quase certeza que precisa de índice.

3. Buffers: shared hit vs read

Buffers: shared hit=0 read=14891

shared hit = páginas no cache de RAM do PostgreSQL. read = páginas lidas do disco. Se read é muito maior que hit, os dados não cabem na memória.

4. Seq Scan em tabelas grandes

Sequential scan numa tabela com 500K linhas é quase sempre problema. É o killer #1 de performance no PostgreSQL.

5. Estimated vs Actual Rows

Quando estimativa e realidade diferem mais de 10x, o planner escolheu errado. Geralmente é estatística desatualizada:

ANALYZE orders;

Estratégia de índice que funciona de verdade

A estratégia da maioria é: "Query lenta → Índice no WHERE → Torcer." Isso é Band-Aid em cano furado.

Regra 1: Índices compostos ganham de single-column

-- Mais ou menos: coluna única
CREATE INDEX idx_orders_status ON orders(status);

-- Bom: composto no WHERE
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Ordem das colunas importa. Igualdade primeiro (status = 'completed'), range depois (created_at >= '2026-01-01').

Regra 2: Covering index elimina lookup na tabela

CREATE INDEX idx_orders_covering ON orders(status, created_at DESC)
INCLUDE (id, total, customer_id);

Agora o PostgreSQL resolve a query usando só o índice. Sem acesso ao heap.

Resultado: 342ms → 0.23ms. 1.487x mais rápido. Heap Fetches: 0 é a chave.

Regra 3: Partial index pra dados enviesados

CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';

Índice 10x menor, cabe inteiro no cache.

Regra 4: Quando NÃO indexar

Índice não é de graça. Cada índice deixa INSERT, UPDATE e DELETE mais lentos.

Ache índices que ninguém tá usando:

SELECT
  schemaname, tablename, indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Já vi DB de produção com 40+ índices sem uso comendo 30GB de disco e deixando escrita 20% mais lenta.

O problema N+1: morte por mil queries

N+1 não é problema do PostgreSQL. É problema da camada de aplicação. E é o bug de performance mais comum em apps web.

Assim que ele se parece

// N+1 clássico: 1 query pra orders + N pra customers
const orders = await db.query('SELECT * FROM orders LIMIT 100');

for (const order of orders) {
  const customer = await db.query(
    'SELECT * FROM customers WHERE id = $1',
    [order.customer_id]
  );
  order.customer = customer;
}
// Total: 101 queries, 340ms de round-trips de rede

Solução 1: JOIN

SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 100;
-- 1 query, ~2ms

Solução 2: Batch loading

const orders = await db.query('SELECT * FROM orders LIMIT 100');
const customerIds = [...new Set(orders.map(o => o.customer_id))];
const customers = await db.query(
  'SELECT * FROM customers WHERE id = ANY($1)',
  [customerIds]
);
const customerMap = new Map(customers.map(c => [c.id, c]));
orders.forEach(o => o.customer = customerMap.get(o.customer_id));
// 2 queries, ~4ms

Solução 3: DataLoader pattern

import DataLoader from 'dataloader';

const customerLoader = new DataLoader(async (ids: number[]) => {
  const customers = await db.query(
    'SELECT * FROM customers WHERE id = ANY($1)',
    [ids]
  );
  const map = new Map(customers.map(c => [c.id, c]));
  return ids.map(id => map.get(id) ?? null);
});

const [cust1, cust2, cust3] = await Promise.all([
  customerLoader.load(1),
  customerLoader.load(2),
  customerLoader.load(3),
]);

Detectando N+1 em produção

const prisma = new PrismaClient({
  log: [{ emit: 'event', level: 'query' }],
});

let queryCount = 0;
prisma.$on('query', () => { queryCount++; });

if (queryCount > 10) {
  console.warn(`⚠️ N+1 detectado: ${queryCount} queries num único request`);
}

Connection Pooling: o killer silencioso de throughput

Você otimizou queries, colocou os índices certos, matou os N+1. Em dev tá perfeito. Fez deploy, chegou carga, e tudo ficou mais lento.

O culpado quase sempre é connection pooling.

Por que conexões são caras

Cada conexão PostgreSQL cria um processo (~10MB RAM). E o que a maioria não sabe: acima de ~50 conexões ativas, a performance cai por lock contention.

A solução: PgBouncer

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

pool_mode = transaction: a conexão real é alocada só durante a transação e depois volta pro pool.

O erro mais comum

import { Pool } from 'pg';

const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

10 instâncias × max: 50 = 500 conexões reais. PostgreSQL morre. Calcule max_connections / número_de_instâncias.

Debug avançado

pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Uma query de 2.81ms chamada 15.000 vezes representa 34.2% do tempo total do banco. É aí que você tem que otimizar primeiro.

pg_stat_user_tables: saúde das tabelas

SELECT
  relname AS tabela,
  n_live_tup AS linhas_vivas,
  n_dead_tup AS linhas_mortas,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC;

Se dead_pct tá acima de 20%, autovacuum não tá dando conta.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Monitoramento de locks: achando queries bloqueadas

SELECT
  blocked.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking_activity.query AS blocking_query,
  now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking ON blocking.locktype = blocked.locktype
  AND blocking.database IS NOT DISTINCT FROM blocked.database
  AND blocking.relation IS NOT DISTINCT FROM blocked.relation
  AND blocking.pid != blocked.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

Se queries ficam bloqueadas por mais de poucos segundos, ou tem transação longa segurando lock (solução: transações curtas) ou migração de schema rodando sem CONCURRENTLY (solução: use CREATE INDEX CONCURRENTLY).

Workflow completo de debug

┌────────────────────────────────────┐
│  1. EXPLAIN (ANALYZE, BUFFERS)     │
│     Rode a query suspeita          │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  2. Seq Scan em tabela > 10K?     │
│     → Adicione índice              │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  3. Rows Removed > 10x?           │
│     → Índice composto/cobertura    │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  4. Estimated vs Actual > 10x?    │
│     → ANALYZE na tabela            │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  5. Buffers read >> hit?           │
│     → Aumente shared_buffers       │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  6. pg_stat_statements             │
│     → Otimize por prioridade       │
└─────────────┬──────────────────────┘
              ▼
┌────────────────────────────────────┐
│  7. > 50 conexões ativas?          │
│     → PgBouncer ou reduza pools    │
└────────────────────────────────────┘

Erros comuns de configuração

Mudança de maior impacto: random_page_cost = 1.1 em SSD. O default 4.0 é da era de HD mecânico.

Exemplo real: de 12 segundos pra 15 milissegundos

SELECT
  date_trunc('day', o.created_at) AS day,
  COUNT(*) AS order_count,
  SUM(o.total) AS revenue,
  COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.created_at BETWEEN '2025-01-01' AND '2025-12-31'
  AND o.store_id = 42
GROUP BY date_trunc('day', o.created_at)
ORDER BY day;

Problemas: Seq Scan (5M linhas), sort em disco, I/O explosivo.

CREATE INDEX idx_orders_store_date ON orders(store_id, created_at)
INCLUDE (total, customer_id);

SET work_mem = '128MB';

12.847ms → 15ms. 856x mais rápido. Índice composto + cobertura + work_mem adequado.

Conclusão

Debug de performance no PostgreSQL não é magia. É processo sistemático:

1. Meça primeiro. EXPLAIN (ANALYZE, BUFFERS) em toda query lenta. Não chute.
2. Leia os sinais. Seq Scan, Rows Removed, Buffers hit vs read mostram exatamente o que tá errado.
3. Indexe com estratégia. Compostos com igualdade primeiro. Cobertura pra queries frequentes. Parciais pra dados enviesados.
4. Mate os N+1. JOIN, batch loading ou DataLoader.
5. Gerencie conexões. PgBouncer ou pooling da app. Conexões reais abaixo de 50.
6. Configure direito. shared_buffers em 25% da RAM, random_page_cost = 1.1 pra SSD.
7. Monitore sempre. pg_stat_statements pra queries, pg_stat_user_tables pra saúde das tabelas.

Comece com EXPLAIN ANALYZE. Siga o workflow. Meça tudo. Suas queries vão agradecer.