English 中文 العربية 日本語 Português

PostgreSQL: Ajuste de Desempenho do PostgreSQL na Prática

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

2. A História

Charlie assumiu um sistema de e-commerce cuja página inicial carregava em 8 segundos e cujo relatório mensal excedia o tempo limite. Ele analisou cada consulta com EXPLAIN ANALYZE e encontrou três problemas: (1) a tabela orders não tinha índice, causando Seq Scan; (2) work_mem era de apenas 4MB, então ordenações complexas iam para o disco; (3) o autovacuum não conseguia acompanhar a taxa de escrita e a tabela estava muito inchada. Após corrigir cada um — adicionando um índice para que as consultas usassem Index Scan, aumentando work_mem para 64MB para que as ordenações ficassem em memória e dobrando a frequência do autovacuum para controlar o inchaço — o desempenho geral melhorou 10x e a página inicial carregava em 0,8 segundos.

3. Conceito: EXPLAIN ANALYZE em Profundidade

(1) Operadores Principais do Plano de Execução

Operador Significado Bom para
Seq Scan Varredura sequencial completa da tabela Tabelas pequenas, sem índice utilizável, muitas linhas retornadas
Index Scan Varredura de índice B-Tree Consulta de alta seletividade (retorna < 5% das linhas)
Bitmap Heap Scan Varredura heap por bitmap Seletividade média (5%-15%); coleta TIDs e depois busca as linhas
Bitmap Index Scan Varredura de índice por bitmap Emparelhado com Bitmap Heap Scan
Nest Loop Junção por laço aninhado Tabela externa pequena + tabela interna indexada
Hash Join Junção por hash Junção de igualdade, tabela interna cabe na memória
Merge Join Junção por mesclagem Ambas as tabelas ordenadas, junção de igualdade

(2) Campos Principais da Saída do EXPLAIN

SQL 
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1001;
TEXT 📖 Somente leitura 
Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=72) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (user_id = 1001)
  Buffers: shared hit=4
Planning Time: 0.085 ms
Execution Time: 0.032 ms
Campo Significado
cost=X..Y Custo de inicialização .. custo total (estimado)
rows=N Número estimado de linhas retornadas
actual time Tempo real gasto (ms)
rows (actual) Número real de linhas retornadas
loops Número de execuções
Buffers: shared hit Número de acertos no buffer compartilhado
Planning Time Tempo de planejamento
Execution Time Tempo de execução

(3) Fluxo de Leitura do Plano de Execução

100% 
flowchart TD
    A["Saída do EXPLAIN ANALYZE"] --> B{"Tipo do nó superior?"}
    B -->|"Seq Scan"| C{"Linhas vs estimado?"}
    C -->|"Estimativa errada"| D["EXECUTE ANALYZE<br/>Atualize estatísticas"]
    C -->|"Estimativa OK"| E{"Seletividade do filtro?"}
    E -->|"Baixa (< 5%)"| F["Adicione índice na coluna de filtro"]
    E -->|"Alta (> 15%)"| G["Seq Scan está OK"]
    B -->|"Index Scan"| H["✅ Bom para baixa seletividade"]
    B -->|"Hash Join"| I{"Derramamento da tabela hash?"}
    I -->|"Sim (work_mem baixo)"| J["Aumente work_mem"]
    I -->|"Não"| K["✅ Bom"]
    B -->|"Nest Loop"| L{"Linhas externas × custo interno?"}
    L -->|"Muito alto"| M["Considere Hash Join<br/>ou adicione índice interno"]
    L -->|"Razoável"| N["✅ Bom"]

4. Operação: Comparação de Tipos de Varredura

▶ Exemplo: Seq Scan — Varredura Completa da Tabela

SQL 
-- Sem índice em status, planejador escolhe Seq Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM orders WHERE status = 'pending';
TEXT 📖 Somente leitura 
Aggregate (actual time=45.123..45.124 rows=1 loops=1)
  -> Seq Scan on orders (actual time=0.012..42.890 rows=50000 loops=1)
       Filter: (status = 'pending'::text)
       Rows Removed by Filter: 950000

▶ Exemplo: Index Scan — Busca Precisa

SQL 
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Alta seletividade: planejador usa Index Scan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id = 1001;
TEXT 📖 Somente leitura 
Index Scan using idx_orders_user_id on orders (actual time=0.015..0.018 rows=3 loops=1)
  Index Cond: (user_id = 1001)

▶ Exemplo: Bitmap Scan — Seletividade Média

SQL 
-- Seletividade moderada: planejador escolhe Bitmap
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id BETWEEN 1000 AND 1100;
TEXT 📖 Somente leitura 
Bitmap Heap Scan on orders (actual time=0.523..2.145 rows=523 loops=1)
  Recheck Cond: (user_id >= 1000 AND user_id <= 1100)
  -> Bitmap Index Scan on idx_orders_user_id (actual time=0.412..0.412 rows=523 loops=1)
       Index Cond: (user_id >= 1000 AND user_id <= 1100)
Tipo de varredura Seletividade Padrão de E/S Melhor quando
Seq Scan Tabela inteira ou > 15% Leitura sequencial Tabela pequena / resultado grande
Index Scan < 5% Leitura aleatória Busca precisa
Bitmap Scan 5%-15% Aleatória depois sequencial Intervalo + ordenação

▶ Exemplo: Nest Loop vs Hash Join

SQL 
-- Tabela externa pequena + interna indexada = Nest Loop
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 1001;

-- Tabela externa grande + interna não ordenada = Hash Join
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date > '2024-01-01';

Output:

TEXT 📖 Somente leitura 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Encontrar e Corrigir um Índice Ausente

SQL 
-- Consulta lenta: Seq Scan em tabela grande
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Seq Scan, cost=0.00..15432.00, actual time=120ms

-- Adiciona índice
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Reanalisa para estatísticas precisas
ANALYZE orders;

-- Mesma consulta agora usa Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Index Scan, cost=0.42..890.00, actual time=2ms

Output:

TEXT 📖 Somente leitura 
CREATE TABLE

5. Conceito: Consultas Lentas com pg_stat_statements

(1) Habilitar e Configurar

BASH 
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
SQL 
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

(2) Interpretação das Métricas Principais

Coluna Significado Direção de otimização
total_exec_time Tempo total de execução Otimize as consultas que mais consomem tempo
mean_exec_time Tempo médio de execução Consulta lenta única
calls Contagem de chamadas Priorize consultas de alta frequência
rows Total de linhas retornadas Verifique se muitas linhas estão sendo retornadas
shared_blks_hit Acertos de buffer Baixa taxa de acerto → aumente shared_buffers
shared_blks_read Leituras de disco Altas leituras de disco → adicione índice / aumente cache

▶ Exemplo: Encontrar as N Principais Consultas Lentas

SQL 
-- Top 10 por tempo total
SELECT left(query, 80) AS query_preview,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       rows,
       round((100.0 * shared_blks_hit /
           nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Output:

TEXT 📖 Somente leitura 
  result  
----------
   42.50
(1 row)

▶ Exemplo: Encontrar Consultas com Uso Intenso de E/S

SQL 
-- Consultas com mais leituras de disco
SELECT left(query, 80) AS query_preview,
       shared_blks_read AS disk_reads,
       shared_blks_hit  AS cache_hits,
       calls,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;

Output:

TEXT 📖 Somente leitura 
  result  
----------
   42.50
(1 row)

6. Conceito: Pool de Conexões e Ajuste de Configuração

(1) Pool de Conexões PgBouncer

Modo Descrição Ideal para
Pooling de sessão Conexão vinculada ao cliente Precisa de variáveis de sessão / tabelas temporárias
Pooling de transação Conexão retornada após a transação A maioria das aplicações web
Pooling de instrução Conexão retornada após a instrução Consultas simples sem transações
BASH 
# pgbouncer.ini
[databases]
shop_db = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
Parâmetro Recomendado Descrição
max_client_conn 500-1000 Máximo de conexões de cliente
default_pool_size 20-50 Tamanho do pool por banco de dados/usuário
reserve_pool_size 5-10 Pool de buffer para picos de tráfego
pool_mode transaction Recomendado para aplicações web

(2) Parâmetros Principais do PostgreSQL

BASH 
# Ajuste do postgresql.conf para servidor com 16GB de RAM
shared_buffers = 4GB           # 25% da RAM
work_mem = 64MB                # memória por operação de ordenação
effective_cache_size = 12GB    # 75% da RAM
maintenance_work_mem = 1GB     # para VACUUM, CREATE INDEX
effective_io_concurrency = 200 # SSD; 2 para HDD
random_page_cost = 1.1         # SSD; 4.0 para HDD
Parâmetro Padrão Recomendado (16GB RAM) Descrição
shared_buffers 128MB 25% RAM Buffers compartilhados
work_mem 4MB 32-128MB Memória para ordenação/hash
effective_cache_size 4GB 75% RAM Estimativa de cache do planejador
maintenance_work_mem 64MB 512MB-1GB Memória para operações de manutenção
max_parallel_workers 8 Núcleos de CPU Processos workers paralelos

▶ Exemplo: Verificar se os Parâmetros Entraram em Vigor

SQL 
-- Verifica configurações atuais
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

-- Altera em tempo de execução (sem necessidade de reinicialização para alguns)
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

-- Parâmetros que exigem reinicialização
SELECT name, setting, boot_val, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

Output:

TEXT 📖 Somente leitura 
ALTER TABLE

7. Conceito: Princípio e Ajuste do Autovacuum

(1) Por Que o Autovacuum é Necessário

Mecanismo MVCC do PostgreSQL: UPDATE/DELETE produzem tuplas mortas, que precisam do VACUUM para recuperar espaço — caso contrário, as tabelas incham e as consultas ficam mais lentas.

SQL 
-- Verifica a proporção de tuplas mortas
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

(2) Parâmetros Principais do Autovacuum

Parâmetro Padrão Sugestão de ajuste
autovacuum on Deve estar habilitado
autovacuum_vacuum_threshold 50 Contagem base de tuplas mortas para disparar o vacuum
autovacuum_vacuum_scale_factor 0.2 Dispara vacuum com 20% das linhas como tuplas mortas
autovacuum_analyze_scale_factor 0.1 Dispara analyze com 10% de alteração
autovacuum_vacuum_cost_delay 2ms Atraso de limitação do vacuum
autovacuum_vacuum_cost_limit 200 Limite de E/S do vacuum por rodada

▶ Exemplo: Ajustar autovacuum para uma Tabela Grande

SQL 
-- Para tabelas de alta escrita, reduza o scale factor para vacuum mais frequente
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = '1ms',
    autovacuum_vacuum_cost_limit = 1000
);

-- Para orders com 10M de linhas, o vacuum dispara em:
-- 10M × 0.05 = 500K tuplas mortas (vs 2M do padrão)

Output:

TEXT 📖 Somente leitura 
-- Comando SQL executado com sucesso

▶ Exemplo: Monitorar o Progresso do Vacuum

SQL 
-- PG 12+: acompanha o progresso do vacuum
SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count
FROM pg_stat_progress_vacuum;

Output:

TEXT 📖 Somente leitura 
 count 
-------
     5
(1 row)

8. Operação: Consulta Paralela e Monitoramento

▶ Exemplo: Habilitar Consulta Paralela

SQL 
-- Habilita consulta paralela (PG 10+)
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET parallel_tuple_cost = 0.001;
SET min_parallel_table_scan_size = '8MB';

-- Verifica plano paralelo
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*), category
FROM products
GROUP BY category;
TEXT 📖 Somente leitura 
Finalize Aggregate (actual time=12.3..12.4 rows=5 loops=1)
  -> Gather (actual time=12.1..12.3 rows=15 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       -> Partial Aggregate (actual time=8.5..8.6 rows=5 loops=3)
            -> Parallel Seq Scan on products (actual time=0.2..6.1 rows=33333 loops=3)

▶ Exemplo: Monitoramento com pg_stat_user_tables

SQL 
SELECT relname AS table_name,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       round(100.0 * idx_scan / nullif(idx_scan + seq_scan, 0), 2) AS idx_scan_pct,
       n_live_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

Output:

TEXT 📖 Somente leitura 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)
Métrica de monitoramento Limite de alerta Ação
seq_tup_read >> idx_tup_fetch Alto seq_scan Adicione índice
dead_pct > 20% Inchaço severo Ajuste o autovacuum
cache_hit_pct < 95% Buffer insuficiente Aumente shared_buffers
last_autovacuum > 7 dias Vacuum não oportuno Reduza scale_factor

▶ Exemplo: Identificar e Corrigir Antipadrões de Desempenho

SQL 
-- Antipadrão 1: condição OR impedindo uso de índice
-- RUIM
SELECT * FROM orders WHERE user_id = 1001 OR status = 'pending';
-- BOM: use UNION ALL
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1001;

-- Antipadrão 2: função envolvendo coluna indexada
-- RUIM
SELECT * FROM orders WHERE lower(status) = 'pending';
-- BOM
SELECT * FROM orders WHERE status = lower('PENDING');

-- Antipadrão 3: LIKE com curinga inicial
-- RUIM
SELECT * FROM products WHERE name LIKE '%phone%';
-- BOM: pg_trgm ou busca textual
SELECT * FROM products WHERE name % 'phone';

Output:

TEXT 📖 Somente leitura 
 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

9. Exemplo Abrangente

SQL 
-- Fluxo de trabalho completo de otimização de desempenho para o sistema de e-commerce de Charlie

-- Passo 1: Identificar consultas lentas
SELECT left(query, 60) AS q, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- Passo 2: Analisar a pior consulta
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name, p.name AS product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- Passo 3: Adicionar índices ausentes
CREATE INDEX idx_orders_date_status ON orders (order_date, status);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- Passo 4: Atualizar estatísticas
ANALYZE orders;
ANALYZE order_items;
ANALYZE products;

-- Passo 5: Ajustar autovacuum para tabelas de alta escrita
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

-- Passo 6: Aumentar work_mem para ordenações complexas
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();

-- Passo 7: Verificar melhoria
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- Passo 8: Monitorar a saúde das tabelas
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_items', 'products')
ORDER BY n_dead_tup DESC;

❓ Perguntas Frequentes

P: Qual a diferença entre EXPLAIN e EXPLAIN ANALYZE? R: EXPLAIN mostra apenas o plano estimado e não executa a consulta; EXPLAIN ANALYZE realmente a executa e retorna o tempo real e as contagens de linhas. Note que ANALYZE executa INSERT/UPDATE/DELETE, então ao analisar DML, envolva em uma transação e faça ROLLBACK depois.

P: O valor de custo é grande mas na verdade é rápido — por quê? R: cost é um valor unitário estimado pelo planejador (não milissegundos), influenciado pelas estatísticas. Se as estatísticas do ANALYZE estiverem desatualizadas, o custo pode ser impreciso. Execute ANALYZE regularmente para manter as estatísticas atualizadas.

P: Quanto shared_buffers deve ser configurado? R: Geralmente 25% da RAM física, não excedendo 40%. No Linux, acima de 40% pode entrar em conflito com o cache de páginas do SO. No Windows, mantenha entre 512MB-1GB.

P: Um work_mem grande pode causar OOM? R: work_mem é o limite de memória por operação de ordenação/hash, e uma consulta complexa pode ter várias dessas operações em execução ao mesmo tempo. Configurá-lo muito alto pode realmente causar OOM. Recomendado 32-128MB, ajustado monitorando o uso real.

P: E se o autovacuum não conseguir acompanhar a taxa de escrita? R: Reduza scale_factor (ex.: 0.02), aumente cost_limit (ex.: 2000), reduza cost_delay (ex.: 1ms) e configure isso por tabela grande. Em casos extremos, execute VACUUM manualmente em paralelo.

P: Quando a consulta paralela entra em vigor? R: Quando o tamanho da tabela excede min_parallel_table_scan_size (padrão 8MB), o custo da consulta excede o limite de inicialização paralela e max_parallel_workers tem capacidade livre. Tabelas pequenas ou consultas simples não serão paralelizadas.

P: Seq Scan é sempre pior que Index Scan? R: Não necessariamente. Quando a maioria das linhas de uma tabela precisa ser retornada, as leituras sequenciais do Seq Scan são mais eficientes que as leituras aleatórias do Index Scan. O planejador do PG escolhe automaticamente a opção de menor custo.

📖 Resumo

📝 Exercícios

  1. ⭐ Para uma tabela de teste com 1 milhão de linhas, observe a diferença de actual time entre Seq Scan e Index Scan com EXPLAIN ANALYZE e registre a diferença entre a estimativa de custo e o tempo real.

  2. ⭐⭐ Configure pg_stat_statements, colete 24 horas de estatísticas de consultas e escreva um relatório de consultas lentas: liste o Top 5 por cada um de total_exec_time, mean_exec_time e shared_blks_read e dê sugestões de otimização.

  3. ⭐⭐⭐ Simule o cenário de Charlie: crie 5 tabelas (orders/order_items/users/products/payments), insira dados de teste, use EXPLAIN ANALYZE para encontrar 3 problemas de desempenho (índice ausente, work_mem insuficiente, autovacuum atrasado), corrija cada um e depois use EXPLAIN ANALYZE para verificar o múltiplo de melhoria de desempenho para cada correção.

Web-Tutorial.com

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

100%