Dicas para otimizar queries SQL complexas

1. Entendendo o plano de execução da query

O primeiro passo para otimizar qualquer query SQL complexa é entender como o banco de dados está executando sua consulta. O comando EXPLAIN ANALYZE é a ferramenta mais poderosa para isso, pois mostra o plano de execução real com tempos e contagens de linhas.

EXPLAIN ANALYZE
SELECT c.nome, COUNT(p.id) as total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
WHERE c.data_cadastro > '2024-01-01'
GROUP BY c.nome;

-- Saída típica:
-- HashAggregate (cost=1500.32..1520.45 rows=2000 width=64)
--   -> Hash Left Join (cost=800.10..1400.20 rows=10000 width=36)
--        -> Seq Scan on clientes c (cost=0.00..500.00 rows=2000 width=32)
--             Filter: (data_cadastro > '2024-01-01')
--        -> Hash (cost=300.00..300.00 rows=10000 width=8)
--             -> Seq Scan on pedidos p (cost=0.00..250.00 rows=10000 width=8)

Ao interpretar o plano, foque em três elementos críticos:

  • Sequential Scan (Seq Scan): Varredura completa da tabela. Em tabelas grandes (>1 milhão de registros), isso é um sinal de alerta.
  • Index Scan: Uso de índice, geralmente eficiente para consultas seletivas.
  • Bitmap Scan: Combinação de múltiplos índices, útil para filtros complexos.

Operações custosas como Sort, Hash Join com muitas linhas, e Aggregate sobre grandes volumes são os principais gargalos a eliminar.

2. Indexação estratégica para queries complexas

Índices bem projetados podem transformar uma query que leva minutos em uma que responde em milissegundos. A chave está em criar índices compostos que atendam exatamente aos filtros e ordenações da sua query.

-- Query problemática:
SELECT * FROM vendas 
WHERE data_venda BETWEEN '2024-01-01' AND '2024-03-31'
  AND status = 'CONCLUIDO'
  AND valor > 1000
ORDER BY data_venda DESC;

-- Índice composto otimizado:
CREATE INDEX idx_vendas_data_status_valor 
ON vendas (data_venda DESC, status, valor);

-- Índice parcial para casos específicos:
CREATE INDEX idx_vendas_concluidas_2024 
ON vendas (data_venda DESC, valor)
WHERE status = 'CONCLUIDO' 
  AND data_venda >= '2024-01-01';

Índices cobertos são particularmente poderosos: quando todas as colunas selecionadas estão no índice, o banco evita acessar a tabela principal. Porém, cuidado com tabelas de alta escrita — cada índice adicional reduz a performance de inserts, updates e deletes.

-- Índice coberto para evitar acesso à tabela:
CREATE INDEX idx_vendas_cover 
ON vendas (data_venda, status, valor, cliente_id);

3. Reescrita de subqueries e CTEs

Subqueries correlacionadas são notoriamente lentas porque executam uma subconsulta para cada linha da consulta externa. A substituição por JOIN ou EXISTS geralmente traz ganhos significativos.

-- LENTO: Subquery correlacionada
SELECT p.*, 
  (SELECT MAX(data) FROM historico h WHERE h.pedido_id = p.id) as ultima_atualizacao
FROM pedidos p
WHERE p.status = 'PENDENTE';

-- RÁPIDO: Usando JOIN com agregação
SELECT p.*, h.ultima_atualizacao
FROM pedidos p
LEFT JOIN (
  SELECT pedido_id, MAX(data) as ultima_atualizacao
  FROM historico
  GROUP BY pedido_id
) h ON p.id = h.pedido_id
WHERE p.status = 'PENDENTE';

-- Alternativa com EXISTS para filtros:
SELECT p.* FROM pedidos p
WHERE EXISTS (
  SELECT 1 FROM historico h 
  WHERE h.pedido_id = p.id AND h.data > '2024-01-01'
);

Em PostgreSQL, CTEs (Common Table Expressions) podem ser materializadas ou não. Por padrão, CTEs são materializadas (executadas uma vez e armazenadas em memória), o que pode ser bom ou ruim dependendo do contexto.

-- CTE materializada (padrão no PostgreSQL < 12):
WITH cte_materializada AS MATERIALIZED (
  SELECT cliente_id, SUM(valor) as total
  FROM pedidos
  WHERE data > '2024-01-01'
  GROUP BY cliente_id
)
SELECT * FROM cte_materializada WHERE total > 1000;

-- CTE não materializada (pode ser inlined na query principal):
WITH cte_otimizada AS NOT MATERIALIZED (
  SELECT * FROM clientes WHERE ativo = true
)
SELECT * FROM cte_otimizada WHERE data_cadastro > '2024-06-01';

4. Otimização de joins em múltiplas tabelas

A ordem dos joins importa. Sempre coloque as tabelas menores ou mais filtradas primeiro. O otimizador geralmente faz isso automaticamente, mas em queries complexas com 5+ tabelas, você pode ajudar.

-- Query original (6 tabelas, 45 segundos):
SELECT p.*, c.nome, f.nome as vendedor, e.cidade
FROM pedidos p
LEFT JOIN clientes c ON p.cliente_id = c.id
LEFT JOIN funcionarios f ON p.vendedor_id = f.id
LEFT JOIN enderecos e ON c.endereco_id = e.id
LEFT JOIN categorias cat ON p.categoria_id = cat.id
LEFT JOIN estoque est ON p.produto_id = est.produto_id
WHERE p.data BETWEEN '2024-01-01' AND '2024-01-31'
  AND p.status = 'CONCLUIDO';

-- Otimizada (aplicando filtros antes e reduzindo joins):
WITH pedidos_filtrados AS (
  SELECT * FROM pedidos
  WHERE data BETWEEN '2024-01-01' AND '2024-01-31'
    AND status = 'CONCLUIDO'
)
SELECT pf.*, c.nome, f.nome as vendedor
FROM pedidos_filtrados pf
INNER JOIN clientes c ON pf.cliente_id = c.id
INNER JOIN funcionarios f ON pf.vendedor_id = f.id
WHERE c.ativo = true;

Troque LEFT JOIN por INNER JOIN sempre que possível. LEFT JOIN força o banco a processar todas as linhas da tabela esquerda, mesmo quando não há correspondência.

5. Gerenciamento de agregações e janelas

Para relatórios pesados, pré-agregue dados em tabelas temporárias ou views materializadas. Isso move o custo computacional para o momento da escrita, não da leitura.

-- View materializada para relatório diário:
CREATE MATERIALIZED VIEW relatorio_vendas_diario AS
SELECT 
  data::date as data_venda,
  produto_id,
  COUNT(*) as total_pedidos,
  SUM(valor) as valor_total,
  AVG(valor) as ticket_medio
FROM pedidos
WHERE status = 'CONCLUIDO'
GROUP BY data::date, produto_id;

-- Refresh periódico:
REFRESH MATERIALIZED VIEW relatorio_vendas_diario;

Funções de janela frequentemente substituem GROUP BY com melhor performance, especialmente quando você precisa de rankings ou totais acumulados.

-- GROUP BY pesado para top 3 por categoria:
SELECT categoria_id, produto_id, SUM(valor) as total
FROM vendas
GROUP BY categoria_id, produto_id
HAVING SUM(valor) > 10000;

-- Mais eficiente com ROW_NUMBER:
WITH ranked AS (
  SELECT 
    categoria_id, 
    produto_id, 
    SUM(valor) as total,
    ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY SUM(valor) DESC) as rn
  FROM vendas
  GROUP BY categoria_id, produto_id
)
SELECT * FROM ranked WHERE rn <= 3;

6. Particionamento e sharding de tabelas grandes

Tabelas com bilhões de registros se beneficiam imensamente do particionamento. O banco pode "podar" partições irrelevantes baseado nos filtros da query.

-- Particionamento por range (por mês):
CREATE TABLE pedidos (
  id SERIAL,
  data TIMESTAMP NOT NULL,
  valor DECIMAL(10,2),
  cliente_id INT
) PARTITION BY RANGE (data);

CREATE TABLE pedidos_2024_01 PARTITION OF pedidos
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE pedidos_2024_02 PARTITION OF pedidos
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Query que automaticamente consulta apenas a partição relevante:
SELECT * FROM pedidos 
WHERE data BETWEEN '2024-01-15' AND '2024-01-20';

O sharding distribui dados entre bancos físicos diferentes. Use quando uma única instância não suporta o volume de dados ou requisições.

7. Boas práticas de escrita e manutenção

Pequenos ajustes na escrita da query geram grandes ganhos. Evite funções em colunas no WHERE, pois isso impede o uso de índices.

-- ERRADO (impede uso de índice):
SELECT * FROM vendas 
WHERE YEAR(data) = 2024 AND MONTH(data) = 1;

-- CORRETO (usa índice normalmente):
SELECT * FROM vendas 
WHERE data >= '2024-01-01' AND data < '2024-02-01';

Para paginação, evite OFFSET que varre linhas descartadas. Use keyset pagination (cursor-based):

-- Paginação tradicional (lenta para páginas profundas):
SELECT * FROM pedidos 
ORDER BY id 
LIMIT 100 OFFSET 1000000;

-- Keyset pagination (rápida):
SELECT * FROM pedidos 
WHERE id > 1000000 
ORDER BY id 
LIMIT 100;

Por fim, mantenha o banco saudável: atualize estatísticas com ANALYZE regularmente e execute VACUUM em tabelas com muitas atualizações. Estatísticas desatualizadas levam o otimizador a escolher planos de execução ruins.

-- Atualizar estatísticas de uma tabela específica:
ANALYZE vendas;

-- Vacuum completo em tabela com alta taxa de updates:
VACUUM FULL vendas;

Referências