Otimização de Queries no PostgreSQL: De Lento a Sub-Milissegundo | Akousa

                                    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)

                                    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

                                    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

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 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')

sql

-- 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);

sql

-- 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;

sql

-- 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));

sql

-- 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');

sql

-- 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%';

sql

-- 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;

sql

-- 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;

python

# 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}")

sql

-- 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;

sql

-- 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();

sql

-- 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;

python

# 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()
)

sql

-- 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);

sql

-- 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;

sql

-- 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;

sql

-- 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';

sql

-- 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';

sql

-- 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;

sql

-- 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;

sql

-- 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';

sql

-- 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;

sql

-- 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)
);

sql

-- 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;

ini

[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 = 30

javascript

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,
});

sql

-- 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;

sql

-- 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;

sql

-- 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>;

sql

-- 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;

sql

-- 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;

sql

-- 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 restart

sql

-- 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;

A Mentalidade de Debugging: Não Adivinhe, Meça

EXPLAIN — O Blueprint

EXPLAIN ANALYZE — A Verdade

EXPLAIN (ANALYZE, BUFFERS) — O Quadro Completo

Opções do EXPLAIN Que Eu Realmente Uso

Lendo Planos de Query Como um Detetive

Seq Scan — O Scan Completo da Tabela

Index Scan — A Busca Direcionada

Index Only Scan — O Santo Graal

Bitmap Scan — O Meio Termo

Identificando Estimativas Ruins

Estratégia de Índices: O Índice Certo para o Trabalho Certo

B-tree — O Cavalo de Batalha Padrão

Covering Indexes com INCLUDE

Partial Indexes — Indexe Apenas o Que Importa

GIN Indexes — Full-Text Search e JSONB

GiST Indexes — Tipos Geométricos e de Intervalo

Hash Indexes — Nicho mas Úteis

Quando Índices Prejudicam

O Problema de Query N+1

Como o N+1 Se Parece

Detectando N+1 nos Logs

A Correção: Eager Loading ou JOINs

JOINs vs Múltiplas Queries

Reescritas Comuns de Queries

Subquery vs JOIN vs CTE

EXISTS vs IN vs JOIN

Evitando SELECT *

Window Functions vs Subqueries

Paginação Feita Direito

Estatísticas de Tabela e Vacuuming

ANALYZE: Atualize as Estatísticas

Tuning do Autovacuum

Table Bloat

Connection Pooling

Por Que Você Precisa de um Connection Pooler

Configuração do PgBouncer

Modo Transaction vs Session

Fórmula de Dimensionamento do Pool

Checklist Prático: Os Passos Exatos para Toda Query Lenta

Passo 1: Pegue a Query Real

Passo 2: Execute EXPLAIN (ANALYZE, BUFFERS)

Passo 3: Verifique Estatísticas da Tabela

Passo 4: Verifique Índices Existentes

Passo 5: Crie ou Modifique Índices

Passo 6: Considere Reescritas de Query

Passo 7: Verifique Configurações do Servidor

Passo 8: Monitore Após a Correção

Bônus: Quick Wins Que Aplico em Todo Banco Novo

Considerações Finais

Posts Relacionados

How to Compress Images Without Losing Quality: The Complete 2026 Guide

Autenticação Moderna em 2026: JWT, Sessions, OAuth e Passkeys