Otimizando queries com índices compostos

1. Fundamentos dos Índices Compostos

Um índice composto é uma estrutura de dados que armazena múltiplas colunas de uma tabela em uma única entrada de índice, organizada em uma árvore B-Tree. Diferentemente de um índice simples, que contém apenas uma coluna, o índice composto permite que o banco de dados localize registros com base em combinações de valores de forma muito mais eficiente.

A principal vantagem do índice composto está na cardinalidade combinada das colunas. Enquanto um índice simples em uma coluna de baixa seletividade (como status com apenas 3 valores distintos) ainda exigirá que o banco examine muitas linhas, um índice composto com colunas de alta seletividade (como status + data_criacao + cliente_id) pode reduzir drasticamente o número de linhas examinadas.

A ordem das colunas no índice é o fator mais crítico. A regra fundamental é: coloque as colunas mais seletivas primeiro. Isso maximiza a capacidade do índice de filtrar registros logo nos primeiros níveis da árvore B-Tree.

2. O Princípio do Prefixo Mais à Esquerda (Leftmost Prefix)

O princípio do prefixo mais à esquerda determina que um índice composto só pode ser utilizado quando a query referencia as colunas mais à esquerda do índice, em ordem. Considere um índice composto definido como:

CREATE INDEX idx_pedidos_status_data 
ON pedidos (status, data_criacao, cliente_id);

Esta query aproveita o índice completamente:

SELECT * FROM pedidos 
WHERE status = 'PENDENTE' 
  AND data_criacao >= '2024-01-01' 
  AND cliente_id = 123;

Esta query aproveita apenas as duas primeiras colunas:

SELECT * FROM pedidos 
WHERE status = 'PENDENTE' 
  AND data_criacao >= '2024-01-01';

Esta query NÃO utiliza o índice:

SELECT * FROM pedidos 
WHERE data_criacao >= '2024-01-01' 
  AND cliente_id = 123;

A razão é que a coluna data_criacao não é a primeira coluna do índice, e não há referência à coluna status para iniciar a busca.

3. Estratégias de Ordenação de Colunas

Para maximizar a eficiência, siga esta ordem recomendada:

  1. Colunas de igualdade primeiro (WHERE coluna = valor)
  2. Colunas de ordenação (ORDER BY)
  3. Colunas de range por último (BETWEEN, >, <)

Exemplo prático:

-- Query frequente
SELECT * FROM vendas 
WHERE loja_id = 10 
  AND data_venda BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY valor_total DESC;

-- Índice otimizado
CREATE INDEX idx_vendas_loja_data_valor 
ON vendas (loja_id, data_venda, valor_total DESC);

Neste caso, loja_id é filtro de igualdade, data_venda é range, e valor_total é usado para ordenação. O índice cobre todos os três aspectos.

4. Índices Compostos e Cobertura de Queries (Covering Index)

Um índice de cobertura contém todas as colunas necessárias para responder uma query, eliminando a necessidade de acessar a tabela principal. Isso é chamado de Index-Only Scan.

Exemplo:

-- Tabela
CREATE TABLE produtos (
    id INT PRIMARY KEY,
    nome VARCHAR(100),
    categoria VARCHAR(50),
    preco DECIMAL(10,2),
    estoque INT
);

-- Índice composto de cobertura
CREATE INDEX idx_produtos_categoria_preco_estoque 
ON produtos (categoria, preco, estoque);

-- Query que usa apenas o índice
SELECT categoria, preco, estoque 
FROM produtos 
WHERE categoria = 'ELETRÔNICOS' 
  AND preco BETWEEN 100 AND 500;

O banco de dados pode responder esta query inteiramente a partir do índice, sem acessar a tabela produtos. O ganho de performance é significativo, mas o custo é o aumento do tamanho do índice e maior lentidão em operações de escrita.

5. Análise Prática com EXPLAIN

Vamos comparar o plano de execução antes e depois de criar um índice composto:

-- Antes do índice composto
EXPLAIN ANALYZE
SELECT * FROM transacoes 
WHERE conta_id = 42 
  AND data BETWEEN '2024-01-01' AND '2024-03-31'
  AND tipo = 'DEBITO';

-- Saída típica (sem índice):
-- Seq Scan on transacoes  (cost=0.00..1843.00 rows=150 width=120)
--   Filter: ((conta_id = 42) AND (data >= '2024-01-01') AND (tipo = 'DEBITO'))

-- Após criar o índice
CREATE INDEX idx_transacoes_conta_data_tipo 
ON transacoes (conta_id, data, tipo);

-- Novo EXPLAIN ANALYZE:
-- Index Scan using idx_transacoes_conta_data_tipo on transacoes  
--   (cost=0.29..8.30 rows=150 width=120)
--   Index Cond: ((conta_id = 42) AND (data >= '2024-01-01') AND (tipo = 'DEBITO'))

Observe a mudança de Seq Scan (varredura sequencial) para Index Scan. O custo cai drasticamente de 1843 para 8.30.

6. Casos de Uso Comuns e Anti-Padrões

Caso real de sucesso:

-- Sistema de e-commerce: buscar pedidos recentes de um cliente
CREATE INDEX idx_pedidos_cliente_data_status 
ON pedidos (cliente_id, data_pedido DESC, status);

-- Query otimizada
SELECT id, data_pedido, status, valor_total 
FROM pedidos 
WHERE cliente_id = 789 
  AND data_pedido >= CURRENT_DATE - INTERVAL '30 days'
  AND status IN ('PENDENTE', 'PROCESSANDO');

Anti-padrão: índice com coluna de baixa seletividade no início.

-- ERRO: coluna 'ativo' tem apenas 2 valores (0 ou 1)
CREATE INDEX idx_usuarios_ativo_nome 
ON usuarios (ativo, nome);

-- Query que sofre com isso
SELECT * FROM usuarios 
WHERE ativo = 1 AND nome = 'João Silva';
-- O índice examinará 50% da tabela antes de encontrar 'João Silva'

Quando evitar: Em tabelas com menos de 1000 registros, o custo de manter o índice geralmente supera o benefício.

7. Manutenção e Monitoramento de Índices Compostos

Índices compostos impactam operações de escrita. Cada INSERT, UPDATE ou DELETE precisa atualizar todas as entradas do índice. Em tabelas com alta frequência de escrita, índices demais podem degradar a performance.

Para identificar índices não utilizados:

-- PostgreSQL: consultar estatísticas de uso
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Índices com idx_scan próximo de zero são candidatos a remoção.

Para reconstruir índices fragmentados:

-- PostgreSQL
REINDEX INDEX idx_pedidos_cliente_data_status;

-- SQL Server
ALTER INDEX idx_pedidos_cliente_data_status ON pedidos REBUILD;

8. Considerações Avançadas

Índices compostos com expressões:

-- Índice funcional composto
CREATE INDEX idx_clientes_nome_lower_data 
ON clientes (LOWER(nome), data_cadastro);

-- Query que o utiliza
SELECT * FROM clientes 
WHERE LOWER(nome) = 'maria silva' 
  AND data_cadastro > '2024-01-01';

Índices compostos em joins:

-- Otimizando join entre tabelas grandes
CREATE INDEX idx_pedidos_cliente_foreign 
ON pedidos (cliente_id, data_pedido);

SELECT c.nome, p.id, p.data_pedido
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
WHERE c.uf = 'SP'
ORDER BY p.data_pedido DESC;

Combinação com índices parciais:

-- Índice composto parcial para cenário específico
CREATE INDEX idx_pedidos_atrasados_cliente 
ON pedidos (cliente_id, data_vencimento) 
WHERE status = 'PENDENTE' AND data_vencimento < CURRENT_DATE;

Este índice só armazena pedidos pendentes e vencidos, sendo muito menor e mais rápido que um índice completo.

Dominar índices compostos é essencial para qualquer profissional que trabalhe com bancos de dados relacionais. A chave está em entender o padrão de consultas da aplicação e projetar índices que cubram esses padrões de forma eficiente, sempre equilibrando ganhos de leitura com custos de escrita.

Referências