Query planning hints: quando e como usar

1. Fundamentos do Query Planner

O otimizador de consultas baseado em custo (CBO) é o coração de qualquer SGBD moderno. Ele analisa estatísticas como número de linhas, distribuição de valores, cardinalidade de índices e seletividade de filtros para estimar o custo de diferentes planos de execução. Em teoria, o planner escolhe o plano mais eficiente. Na prática, isso nem sempre acontece.

Por que o planner falha?

  • Estatísticas desatualizadas após grandes volumes de DML
  • Correlações ocultas entre colunas que o otimizador não capta
  • Limites de memória que tornam hash joins inviáveis
  • Estimativas de cardinalidade imprecisas em consultas complexas

O que são query hints?

Query hints são instruções explícitas que o desenvolvedor ou DBA insere na consulta para forçar ou influenciar a escolha do plano de execução. Eles funcionam como "atalhos" para corrigir decisões subótimas do otimizador.

-- Exemplo de hint no PostgreSQL (sintaxe comentário especial)
EXPLAIN (ANALYZE, BUFFERS)
SELECT /*+ SeqScan(t) */ *
FROM orders t
WHERE t.status = 'PENDING';

2. Hints de Junção (Join Hints)

Os hints de junção controlam dois aspectos críticos: o tipo de junção e a ordem em que as tabelas são unidas.

Forçando tipos de junção:

-- Forçar HASH JOIN quando o planner escolhe Nested Loop
SELECT /*+ HashJoin(o i) */
       o.order_id, i.item_name
FROM orders o
JOIN items i ON o.order_id = i.order_id
WHERE o.created_at >= '2024-01-01';

-- Forçar NESTED LOOP para poucas linhas
SELECT /*+ NestLoop(c o) */
       c.name, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 12345;

Direcionando a ordem das junções:

-- LEADING força a ordem: primeiro orders, depois customers
SELECT /*+ Leading(o c) HashJoin(o c) */
       o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Quando usar cada tipo:

  • Nested Loop: ideal quando uma das tabelas é pequena (menos de 10.000 linhas) ou o filtro reduz drasticamente o resultado
  • Hash Join: melhor para tabelas grandes sem índice adequado ou quando ambas as tabelas têm milhões de linhas
  • Merge Join: útil quando os dados já estão ordenados (por índice) ou quando a saída precisa ser ordenada

3. Hints de Acesso e Varredura (Scan Hints)

Controlam como o SGBD acessa os dados: via índice ou varredura sequencial.

-- Forçar uso de índice específico
SELECT /*+ IndexScan(t idx_orders_created) */
       *
FROM orders t
WHERE t.created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- Forçar sequential scan quando o planner insiste em índice subótimo
SELECT /*+ SeqScan(t) */
       *
FROM orders t
WHERE t.status IN ('CANCELED', 'REFUNDED');

Casos de uso comuns:

  • INDEX: tabelas grandes com filtros seletivos (menos de 5% das linhas)
  • SEQSCAN: quando o índice cobre grande parte da tabela e a varredura sequencial seria mais rápida
  • Bitmap Scan: útil para combinações de filtros em colunas diferentes
-- Forçar Bitmap Scan para consultas com múltiplos filtros
SELECT /*+ BitmapScan(t) */
       *
FROM orders t
WHERE t.status = 'SHIPPED'
  AND t.total > 1000
  AND t.created_at > '2024-06-01';

4. Hints de Materialização e Subconsultas

CTEs (Common Table Expressions) e subconsultas podem ser materializadas (executadas uma vez e armazenadas em memória) ou executadas como subconsultas correlacionadas.

-- Forçar materialização de CTE
WITH /*+ Materialize */ recent_orders AS (
    SELECT * FROM orders
    WHERE created_at > CURRENT_DATE - INTERVAL '7 days'
)
SELECT * FROM recent_orders
WHERE total > 500;

-- Evitar materialização (CTE como subconsulta inline)
WITH /*+ NoMaterialize */ expensive_items AS (
    SELECT * FROM items WHERE price > 1000
)
SELECT o.order_id, e.name
FROM orders o
JOIN expensive_items e ON o.item_id = e.item_id;

Quando materializar é benéfico:

  • Reuso do mesmo resultado múltiplas vezes na consulta
  • CTEs com operações caras (agregações, ordenações)
  • Evitar repetição de scans em tabelas grandes

Quando evitar:

  • CTEs que retornam muitas linhas (overhead de memória)
  • Consultas onde a materialização impede otimizações de predicado

5. Hints de Paralelismo e Alocação de Recursos

Controlam quantos workers paralelos são usados e quanto de memória é alocada.

-- Forçar paralelismo máximo
SELECT /*+ Parallel(t 4) */
       status, COUNT(*)
FROM orders t
GROUP BY status;

-- Limitar paralelismo (evitar monopolizar recursos)
SELECT /*+ Parallel(t 1) */
       *
FROM orders t
WHERE t.total > 10000;

Hints de memória:

-- Aumentar work_mem para operação específica
SET work_mem = '64MB';
SELECT /*+ HashAgg */ status, COUNT(*)
FROM orders
GROUP BY status;

Impacto em ambientes concorrentes:

  • Hints de paralelismo agressivo podem causar contenção de CPU e I/O
  • Hints de memória excessiva podem forçar swap ou OOM killer
  • Sempre testar com carga real antes de aplicar em produção

6. Sintaxe e Portabilidade entre SGBDs

A sintaxe de hints varia drasticamente entre SGBDs:

PostgreSQL:

/*+ SeqScan(t) */
/*+ HashJoin(a b) */

Oracle:

SELECT /*+ FULL(t) */ * FROM orders t;
SELECT /*+ USE_HASH(a b) */ * FROM orders a JOIN items b ...;

SQL Server:

SELECT * FROM orders 
OPTION (HASH JOIN, MERGE JOIN);

MySQL:

SELECT /*+ NO_INDEX(t idx_status) */ * FROM orders t;

Boas práticas de portabilidade:

  • Documente cada hint com justificativa clara
  • Teste após atualizações de versão do SGBD
  • Evite hints em aplicações de terceiros ou frameworks ORM
  • Prefira a sintaxe de comentário (portável entre SGBDs como comentário ignorado)

7. Quando Usar e Quando Evitar Hints

Cenários justificáveis:

  • Consultas críticas com planos instáveis que variam entre execuções
  • Data warehouse com distribuição de dados conhecida e estável
  • Correção temporária enquanto se ajustam estatísticas ou índices
  • Consultas batch noturnas onde desempenho previsível é essencial

Riscos e alternativas:

Risco Alternativa
Plano congelado se torna ruim com mudança de dados Atualizar estatísticas regularmente
Manutenção difícil Reescrever a consulta de forma otimizada
Incompatibilidade entre versões Criar índices apropriados
Monopolização de recursos Ajustar configurações globais (work_mem, parallel_workers)

Antes de usar hints, sempre tente:

  1. ANALYZE para atualizar estatísticas
  2. Criar índices compostos que cubram os filtros
  3. Reescrever a consulta com JOINs explícitos
  4. Ajustar parâmetros globais como random_page_cost e effective_cache_size

Regra de ouro: hints são soluções de curto prazo. O objetivo final é ter um banco de dados configurado e com estatísticas atualizadas que permita ao otimizador tomar a melhor decisão sozinho.

Referências