Como otimizar o desempenho da consulta PostgreSQL em 2026: índices, EXPLAIN e ajuste

A maioria dos problemas de desempenho do PostgreSQL se resume a um punhado de causas: um índice ausente, um índice que o planejador não pode usar, uma consulta que extrai muito mais linhas do que o necessário ou estatísticas que não refletem mais os dados. Este guia aborda como descobrir qual deles você possui e corrigi-lo, na ordem que encontrar os problemas mais rapidamente.

Etapa 1: Encontre as consultas lentas

Não adivinhe. Habilitarpg_stat_statements, que registra estatísticas de execução para cada formato de consulta.

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- then restart, and in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Highest total time — usually where the real wins are
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Classificar portotal tempo em vez de significar. Uma consulta que leva 20 ms, mas é executada 100.000 vezes por hora, custa muito mais do que uma consulta que leva 2 segundos duas vezes por dia e geralmente é mais fácil de corrigir.

Registre também consultas lentas para capturar o que acontece na produção, mas não nos testes.

-- postgresql.conf
log_min_duration_statement = 500   -- log anything over 500ms
log_lock_waits = on
log_temp_files = 0                 -- log every temp file (indicates spilling)

Etapa 2: Leia EXPLAIN ANALYZE corretamente

Sempre useEXPLAIN (ANALYZE, BUFFERS). SimplesEXPLAIN mostra a estimativa do planejador; ANALYZE na verdade, executa a consulta e relata a realidade, e a lacuna entre os dois geralmente é o bug.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

Leia a saída do nó mais interno para fora e procure quatro coisas.

Linhas estimadas versus linhas reais. rows=10 ... actual rows=48000 significa que o planejador estava terrivelmente errado e todas as decisões tomadas posteriormente são suspeitas. Geralmente estatísticas obsoletas.

Varreduras sequenciais em tabelas grandes. Multa em mesas pequenas e bandeira vermelha em mesas grandes com filtro seletivo.

Linhas removidas por filtro. Um número alto significa que o banco de dados leu muito mais linhas do que retornou — a clássica assinatura de índice ausente.

Mesclagem externa ou classificação baseada em disco. A classificação foi derramada no disco porquework_mem era pequeno demais para isso.

-- Refresh statistics when estimates are wrong
ANALYZE orders;

-- Collect more detail on a column with skewed distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Etapa 3: indexe da maneira certa

A ordem das colunas em um índice composto é extremamente importante. A regra: colunas de igualdade primeiro, depois colunas de intervalo e, em seguida, colunas pelas quais você classifica.

-- For: WHERE status = 'pending' AND created_at >= ... ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

Comstatus primeiro, o índice se restringe às linhas correspondentes e depois as lê já ordenadas porcreated_at, então a classificação desaparece completamente. Inverta a ordem e o índice se tornará muito menos útil.

Índices parciais são dramaticamente menores quando você consulta apenas um subconjunto.

-- If 98% of orders are completed and you only query pending ones
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

Índices de cobertura deixe a consulta ser respondida apenas a partir do índice, evitando totalmente a tabela.

CREATE INDEX CONCURRENTLY idx_orders_lookup
  ON orders (user_id, created_at DESC)
  INCLUDE (total, status);
-- EXPLAIN then shows "Index Only Scan"

Sempre crie índices em sistemas ativos comCONCURRENTLY, que não bloqueia gravações.

Etapa 4: Por que um índice está sendo ignorado

Uma frustração comum — o índice existe e o planejador não o utilizará. Causas usuais:

Uma função é aplicada à coluna. Isso torna o índice inutilizável.

-- ❌ Index on email cannot be used
WHERE lower(email) = 'user@example.com';

-- ✅ Index the expression instead
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (lower(email));

Incompatibilidade de tipo. Comparando umbigint coluna para uma string força uma conversão que anula o índice. Combine os tipos nos seus parâmetros de consulta.

Curinga principal em LIKE. LIKE '%term' não pode usar um índice de árvore B. Use um índice trigrama para isso.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);
-- now ILIKE '%widget%' can use an index

A consulta não é seletiva. Se um filtro corresponder a uma grande fração da tabela, uma varredura sequencial será genuinamente mais rápida e o planejador estará correto. Nem todo “índice não utilizado” é um bug.

Etapa 5: corrigir consultas N+1

A causa mais comum de carga do banco de dados no nível do aplicativo e geralmente invisível em logs de consulta lenta porque cada consulta individual é rápida.

-- 1 query for orders, then one per order for the user: 101 round trips
SELECT * FROM orders LIMIT 100;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;   -- ... and so on

-- ✅ One query
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 100;

Em um ORM, isso significa carregamento antecipado —includes no registro ativo,selectinload em SQLAlchemy,include em Prisma. Detecte o padrão contando consultas por solicitação em desenvolvimento; um salto repentino no tamanho da lista é a assinatura.

Etapa 6: Paginação escalonada

OFFSET fica mais lento quanto mais você avança na página, porque o banco de dados deve gerar e descartar cada linha ignorada.

-- ❌ Reads and throws away 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination — constant time at any depth
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

Incluindoid como um desempate, totaliza a ordem, o que evita que as linhas sejam ignoradas ou repetidas quando os carimbos de data e hora colidem.

Etapa 7: Configuração que vale a pena alterar

Os padrões são conservadores e assumem hardware muito modesto.

random_page_cost é o mais impactante e menos conhecido. O padrão 4.0 pressupõe que leituras aleatórias são quatro vezes mais caras que as sequenciais, o que acontecia com unidades mecânicas. Em SSDs, faz com que o planejador evite índices que deveria usar.

-- Test the effect on a single session before changing it globally
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Etapa 8: inchaço e vácuo

O PostgreSQL mantém versões antigas de linhas até que o vácuo as recupere. O tráfego intenso de atualizações ou exclusões causa inchaço, o que faz com que as verificações leiam mais páginas do que o necessário.

-- Which tables are bloated and when were they last vacuumed?
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Rebuild a bloated table without a long exclusive lock
VACUUM (ANALYZE, VERBOSE) orders;

-- For severe bloat, rebuild indexes concurrently
REINDEX INDEX CONCURRENTLY idx_orders_status_created;

Para tabelas com tráfego intenso de gravação, torne o autovacuum mais agressivo especificamente nessa tabela, e não globalmente.

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

Encontrando índices não utilizados e duplicados

Cada índice retarda as gravações e consome espaço. Audite-os periodicamente.

-- Indexes that have never been scanned
SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Verifique o tempo de atividade antes de agir – um índice não utilizado desde a reinicialização na semana passada pode ser essencial para um relatório mensal.

Conclusão

Otimizar o PostgreSQL é uma sequência, não uma suposição:encontre as consultas caras compg_stat_statements classificado por tempo total, leiaEXPLAIN (ANALYZE, BUFFERS) para lacunas e linhas estimadas versus reais removidas por filtro, adicione índices compostos com colunas de igualdade primeiro e colunas de intervalo depois, elimine padrões N+1 com carregamento antecipado, substitua OFFSET profundo pela paginação do conjunto de chaves e definarandom_page_cost apropriadamente para SSDs. Em seguida, mantenha as estatísticas atualizadas e o autovacuum ajustado, porque um banco de dados bem indexado com estatísticas obsoletas ainda produz planos ruins.