Armadilhas Frequentes com Índices: Quando Eles Não Ajudam (ou Pioram)

Armadilhas em que índices não ajudam (e às vezes pioram)

Índices aceleram quando reduzem muito o conjunto de linhas lidas e evitam trabalho extra (como ordenações e buscas repetidas na tabela). Eles falham quando o plano precisa tocar em muitas linhas de qualquer forma, quando o índice não “casa” com o filtro/ordenação real, ou quando o custo de usar o índice (muitos lookups, sort caro, operações adicionais) supera o ganho.

A seguir estão armadilhas frequentes, com sinais no plano de execução e ações práticas para corrigir.

1) Indexar coluna de baixa seletividade sem considerar o padrão de consulta

O que acontece

Colunas com poucos valores distintos (ex.: status com 3 valores, ativo booleano) podem fazer o índice apontar para uma grande fração da tabela. O otimizador pode até usar o índice, mas o custo de buscar muitas linhas (e depois fazer lookup na tabela) vira gargalo.

Exemplo

-- Tabela grande: pedidos( id, status, data_criacao, cliente_id, total, ... )  -- status: 'ABERTO', 'PAGO', 'CANCELADO'  CREATE INDEX idx_pedidos_status ON pedidos(status);  SELECT * FROM pedidos WHERE status = 'PAGO';

Se 60% dos pedidos estão como 'PAGO', o índice não “filtra” o suficiente.

Como detectar no plano

• Index Scan/Seek seguido de muitos lookups (ex.: Key Lookup / Table Lookup) e alto número de linhas retornadas pelo operador de índice.
• Operadores com alto custo de I/O e muitas leituras apesar de usar índice.
• Estimativa de linhas próxima do total (ou muito alta) para o predicado.

Passo a passo prático

• Meça a fração retornada: rode a consulta com COUNT(*) para o filtro e compare com o total.
• Se a fração for alta, teste a alternativa: permitir scan (ou remover o índice) e comparar tempo/leituras.
• Se o filtro por status sempre vem junto com outro filtro mais seletivo (ex.: data_criacao, cliente_id), crie índice alinhado ao padrão real (ex.: composto).

Estudo de caso (expectativa vs realidade)

2) Excesso de índices redundantes (ou quase iguais)

O que acontece

Múltiplos índices com as mesmas colunas (ou prefixos equivalentes) aumentam custo de escrita e manutenção, podem confundir a escolha do plano e raramente trazem ganho adicional.

Continue em nosso aplicativo e ...

• Ouça o áudio com a tela desligada
• Ganhe Certificado após a conclusão
• + de 5000 cursos para você explorar!

ou continue lendo abaixo...

Baixar o aplicativo

Exemplo

CREATE INDEX idx_cliente_email ON clientes(email);  CREATE INDEX idx_cliente_email_nome ON clientes(email, nome);  CREATE INDEX idx_cliente_email_ativo ON clientes(email, ativo);

Se a maioria das consultas filtra por email e retorna poucas colunas, um único índice bem escolhido pode bastar.

Como detectar no plano e no catálogo

• Planos alternando entre índices parecidos sem ganho claro.
• Consultas de escrita (INSERT/UPDATE/DELETE) ficando mais lentas após “otimizações”.
• Catálogo mostrando índices com mesmas colunas na mesma ordem (ou um índice sendo prefixo de outro).

Passo a passo prático

• Liste índices por tabela e agrupe por colunas e ordem.
• Identifique quais são realmente usados (por métricas de uso do SGBD ou por amostragem de planos).
• Mantenha o menor conjunto que cobre os padrões principais; remova redundantes com cuidado e monitoramento.

Estudo de caso (expectativa vs realidade)

3) Índice que não corresponde às consultas reais

O que acontece

Você cria um índice “teoricamente bom”, mas as consultas filtram por outra coluna, usam funções, fazem join por outra chave, ou ordenam por campos diferentes. Resultado: o índice fica subutilizado e o plano recorre a scan, sort ou hash.

Exemplos comuns

• Consulta filtra por LOWER(email) mas o índice é em email.
• Consulta ordena por data_criacao DESC mas o índice é em status.
• Consulta filtra por intervalo em data mas o índice começa por outra coluna pouco útil para esse padrão.

-- Índice criado:  CREATE INDEX idx_users_email ON users(email);  -- Consulta real:  SELECT id FROM users WHERE LOWER(email) = 'ana@exemplo.com';

Como detectar no plano

• Predicado aparece como Filter (aplicado depois) em vez de Seek/Index Cond.
• Sort caro para ORDER BY que poderia ser evitado.
• Operador de índice não aparece; há Seq Scan/Table Scan mesmo com índice “existente”.

Passo a passo prático

• Capture as consultas mais lentas e mais frequentes.
• Compare WHERE/JOIN/ORDER BY reais com as colunas e ordem do índice.
• Evite funções no lado da coluna quando possível (normalize o dado, use coluna derivada/materializada, ou recurso equivalente do SGBD).
• Se ORDER BY é crítico, alinhe o índice à ordenação usada.

4) Uso de OR que impede uso eficiente do índice

O que acontece

Condições com OR podem levar o otimizador a escolher scan, ou a combinar múltiplos acessos a índice com custo alto. Em muitos casos, reescrever como UNION ALL (quando apropriado) permite que cada parte use um índice específico.

Exemplo

-- Consulta original  SELECT * FROM pedidos  WHERE cliente_id = 10 OR vendedor_id = 10;

Mesmo com índices em cliente_id e vendedor_id, o plano pode optar por scan se a combinação for cara.

Reescrita típica (quando não há duplicatas ou você pode tratá-las)

SELECT * FROM pedidos WHERE cliente_id = 10  UNION ALL  SELECT * FROM pedidos WHERE vendedor_id = 10 AND cliente_id <> 10;

Como detectar no plano

• Scan com filtro contendo OR.
• Ou plano com “Bitmap OR / Index Merge” (dependendo do SGBD) com custo alto e muitas linhas intermediárias.

Passo a passo prático

• Verifique se os ramos do OR têm seletividades muito diferentes.
• Teste reescrita com UNION ALL e compare leituras/tempo.
• Se OR é inevitável, avalie índice composto ou estratégia de modelagem (ex.: coluna “responsável_id” unificada) quando fizer sentido de negócio.

5) LIKE com curinga inicial (não aproveita bem B-tree)

O que acontece

Padrões como LIKE '%texto' ou LIKE '%texto%' geralmente impedem busca eficiente por prefixo. O índice pode ser ignorado, levando a scan e custo alto.

Exemplo

SELECT * FROM produtos WHERE nome LIKE '%pro%';

Como detectar no plano

• Seq Scan/Table Scan com filtro em LIKE.
• Alta CPU por avaliação de expressão em muitas linhas.

Passo a passo prático

• Se o requisito permitir, troque para busca por prefixo: LIKE 'pro%'.
• Se precisa de “contém”, considere recurso apropriado do SGBD (ex.: índice full-text/trigram) em vez de B-tree tradicional.
• Valide com plano: o predicado deve virar condição de acesso (seek/cond) e não apenas filtro.

6) Retornar muitas colunas: perde cobertura e aumenta lookups

O que acontece

Mesmo quando o filtro usa índice, selecionar muitas colunas (especialmente SELECT *) força o banco a buscar dados na tabela (lookups) para cada linha encontrada no índice. Se o conjunto retornado é grande, isso vira um padrão de acesso aleatório caro.

Exemplo

-- Filtro seletivo, mas retorno pesado  SELECT * FROM pedidos WHERE cliente_id = 10 AND data_criacao >= '2025-01-01';

Como detectar no plano

• Index Seek/Scan seguido de Key Lookup / Table Lookup com alto número de execuções.
• Tempo concentrado no operador de lookup.

Passo a passo prático

• Reduza colunas retornadas: selecione apenas o necessário.
• Se a consulta é crítica e repetida, avalie um índice que suporte o padrão (incluindo colunas necessárias quando o SGBD permitir) para reduzir lookups.
• Compare: mesmo índice, mas com projeção menor, costuma reduzir muito I/O.

Estudo de caso (expectativa vs realidade)

7) Consultas que retornam grande parte da tabela: scan pode ser melhor

O que acontece

Se a consulta retorna uma porcentagem grande da tabela, usar índice pode ser pior do que um scan sequencial (ou leitura por páginas contíguas). O índice adiciona saltos e lookups, enquanto o scan lê de forma mais linear.

Exemplo

-- Retorna 40% da tabela em um período movimentado  SELECT id, total FROM pedidos WHERE data_criacao >= '2025-01-01';

Como detectar no plano

• Otimizador escolhe scan mesmo havendo índice (isso pode estar correto).
• Quando força índice (por hint) e piora: aumento de leituras e tempo.
• Estimativa de linhas alta (próxima do total) para o predicado.

Passo a passo prático

• Meça a seletividade real do filtro (quantas linhas retorna).
• Compare plano com scan vs plano com índice (sem “forçar” em produção; teste em ambiente controlado).
• Se a consulta é inevitavelmente “larga”, foque em reduzir colunas, particionar por data (se aplicável), ou pré-agregar.

Mini-checklist de detecção no plano (sintomas típicos)

• Muito lookup: operador de lookup com muitas execuções; tempo concentrado nele.
• Sort caro: ORDER BY/Group By gerando sort grande; memória estoura e vai para disco (quando o SGBD mostra).
• Estimativas ruins: diferença grande entre linhas estimadas vs reais; escolha de join/scan inesperada.
• Filtro pós-leitura: predicado aparece como Filter em vez de condição de acesso no índice.
• Operadores intermediários enormes: bitmap/hash com muitos registros temporários.

Orientações para simplificar e priorizar mudanças

• Priorize pelo impacto: comece pelas consultas mais frequentes e/ou mais lentas, não por “melhorias genéricas”.
• Remova o óbvio: elimine índices claramente redundantes antes de criar novos.
• Alinhe com a query real: ajuste WHERE/JOIN/ORDER BY e projeção (colunas retornadas) antes de mexer em índice.
• Evite “forçar índice” como solução: se o plano escolhe scan, valide seletividade e custo; muitas vezes o scan é a escolha correta.
• Teste uma mudança por vez: compare plano, leituras e tempo; registre antes/depois para evitar regressões.
• Procure sinais no plano: lookup em massa, sort caro e estimativas ruins geralmente indicam que o problema não é “falta de índice”, e sim “índice errado” ou “consulta que não permite ganho”.