Índices parciais e índices de expressão

1. Introdução aos Índices Especializados

1.1. Limitações dos índices tradicionais (B-tree padrão)

Os índices B-tree tradicionais indexam todas as linhas de uma tabela com base em uma ou mais colunas. Embora sejam eficientes para consultas genéricas, eles apresentam limitações significativas: ocupam espaço em disco proporcional ao número total de linhas, exigem manutenção em todas as operações DML e não conseguem acelerar consultas que envolvem transformações nos dados (como funções aplicadas a colunas). Um índice em email não ajudará uma consulta com WHERE LOWER(email) = 'joao@exemplo.com', pois a função impede o uso direto do índice.

1.2. Definição e propósito dos índices parciais

Um índice parcial é um índice criado apenas sobre um subconjunto de linhas da tabela, definido por uma cláusula WHERE. Seu propósito principal é indexar apenas os dados que realmente importam para as consultas, ignorando linhas que nunca serão filtradas. Por exemplo, em uma tabela com milhões de pedidos, onde 90% já foram concluídos e raramente são consultados, um índice parcial sobre status = 'pendente' reduz drasticamente o tamanho do índice e acelera as buscas.

1.3. Definição e propósito dos índices de expressão

Índices de expressão (também chamados de índices funcionais) armazenam o resultado de uma expressão ou função aplicada a uma ou mais colunas, em vez dos valores brutos das colunas. Eles permitem que consultas com transformações nos dados utilizem indexação eficiente. Por exemplo, um índice em LOWER(email) permite buscas case-insensitive sem precisar modificar a estrutura da tabela ou usar funções em tempo de consulta que impediriam o uso de índices comuns.

1.4. Casos de uso comuns no mundo real

  • Índices parciais: filtrar registros ativos (WHERE ativo = true), pedidos não cancelados, usuários não deletados (WHERE deleted_at IS NULL), logs dos últimos 30 dias.
  • Índices de expressão: busca case-insensitive (LOWER(nome)), consultas por partes de datas (EXTRACT(YEAR FROM data_criacao)), extração de campos JSONB ((dados->>'cpf')), cálculos pré-computados ((preco * quantidade)).

2. Índices Parciais: Sintaxe e Funcionamento

2.1. Sintaxe CREATE INDEX ... WHERE e cláusula de filtro

A sintaxe básica para criar um índice parcial é:

CREATE INDEX idx_pedidos_pendentes
ON pedidos (status, data_criacao)
WHERE status = 'pendente';

A cláusula WHERE define quais linhas serão incluídas no índice. Apenas as linhas que satisfazem a condição são inseridas na estrutura do índice.

2.2. Como o query planner utiliza índices parciais

O query planner (planejador de consultas) avalia se a condição WHERE da consulta é um subconjunto da condição do índice parcial. Se a consulta tiver WHERE status = 'pendente' AND data_criacao > '2024-01-01', o planner pode usar o índice parcial acima. Se a consulta buscar por status = 'cancelado', o índice não será considerado, pois as linhas canceladas não estão no índice.

2.3. Exemplos práticos: filtrando registros ativos, pedidos pendentes, dados não deletados

Exemplo 1: Usuários ativos

CREATE INDEX idx_usuarios_ativos
ON usuarios (ultimo_login)
WHERE ativo = true;

-- Consulta que usará o índice:
SELECT * FROM usuarios
WHERE ativo = true AND ultimo_login < '2024-06-01';

Exemplo 2: Pedidos pendentes

CREATE INDEX idx_pedidos_pendentes_urgencia
ON pedidos (prazo_entrega)
WHERE status IN ('pendente', 'em_andamento');

-- Consulta beneficiada:
SELECT * FROM pedidos
WHERE status = 'pendente' AND prazo_entrega <= CURRENT_DATE;

Exemplo 3: Dados não deletados (soft delete)

CREATE INDEX idx_produtos_ativos
ON produtos (categoria_id, preco)
WHERE deleted_at IS NULL;

-- Consulta:
SELECT * FROM produtos
WHERE deleted_at IS NULL AND categoria_id = 5 AND preco BETWEEN 10 AND 50;

3. Índices de Expressão: Sintaxe e Funcionamento

3.1. Sintaxe CREATE INDEX ON ... (expressão) e funções imutáveis

A sintaxe para índices de expressão:

CREATE INDEX idx_email_lower
ON usuarios (LOWER(email));

A expressão pode ser qualquer combinação de colunas, operadores e funções, desde que sejam imutáveis (retornem o mesmo resultado para os mesmos argumentos, independentemente do contexto). Funções como LOWER(), UPPER(), EXTRACT(), COALESCE() são imutáveis. Funções como NOW(), RANDOM(), CURRENT_DATE não são permitidas.

3.2. Índices funcionais vs. índices em colunas derivadas

Índices funcionais são diferentes de índices em colunas derivadas (como colunas geradas). Enquanto colunas geradas armazenam fisicamente o valor derivado na tabela, índices funcionais apenas armazenam o resultado na estrutura do índice, sem ocupar espaço extra na tabela principal. Isso economiza armazenamento e evita redundância de dados.

3.3. Exemplos práticos: LOWER(email), (ano || mes), JSONB extração de campos

Exemplo 1: Busca case-insensitive

CREATE INDEX idx_clientes_nome_lower
ON clientes (LOWER(nome));

-- Consulta:
SELECT * FROM clientes
WHERE LOWER(nome) = 'maria silva';

Exemplo 2: Concatenação de ano e mês

CREATE INDEX idx_vendas_ano_mes
ON vendas ((EXTRACT(YEAR FROM data_venda) || '-' || EXTRACT(MONTH FROM data_venda)));

-- Consulta:
SELECT * FROM vendas
WHERE (EXTRACT(YEAR FROM data_venda) || '-' || EXTRACT(MONTH FROM data_venda)) = '2024-06';

Exemplo 3: Extração de campos JSONB

CREATE INDEX idx_usuarios_cpf
ON usuarios ((dados->>'cpf'));

-- Consulta:
SELECT * FROM usuarios
WHERE dados->>'cpf' = '123.456.789-00';

4. Vantagens e Ganhos de Performance

4.1. Redução do tamanho do índice (menos páginas em disco e em cache)

Índices parciais podem ser 10 a 100 vezes menores que índices completos, especialmente quando a condição filtra uma pequena fração dos dados. Isso significa menos páginas para carregar em cache, mais índices cabendo na memória e consultas mais rápidas.

4.2. Menor custo de manutenção em operações DML (INSERT, UPDATE, DELETE)

Como índices parciais contêm menos linhas, as operações de inserção, atualização e exclusão afetam menos páginas do índice. Para tabelas com alta taxa de escrita, isso reduz significativamente a contenção de locks e o overhead de manutenção.

4.3. Aceleração de consultas com filtros ou expressões específicas

Índices de expressão eliminam a necessidade de scans sequenciais quando a consulta usa funções. Uma consulta com WHERE LOWER(email) = 'x' sem índice funcional faria um scan completo da tabela. Com o índice, a busca se torna uma simples lookup em árvore B.

5. Cuidados, Limitações e Armadilhas

5.1. Restrições de imutabilidade em índices de expressão

Apenas funções marcadas como IMMUTABLE podem ser usadas em índices de expressão. Funções STABLE (como NOW()) ou VOLATILE (como RANDOM()) são rejeitadas. Isso garante que o valor indexado não mude sem que o índice seja atualizado.

5.2. Quando o índice parcial NÃO é usado pelo planner

O query planner só usa índices parciais se a condição WHERE da consulta for um subconjunto (ou equivalente) da condição do índice. Condições adicionais que não podem ser inferidas a partir do índice podem fazer o planner ignorá-lo. Além disso, se a consulta não incluir a condição do índice parcial, ele não será considerado.

5.3. Impacto em operações de escrita e concorrência

Índices parciais podem causar deadlocks ou contenção se muitas transações tentarem inserir/atualizar linhas que pertencem ao mesmo subconjunto indexado. Em sistemas com alta concorrência, é importante monitorar lock waits e considerar índices menos seletivos se necessário.

5.4. Diferenças entre PostgreSQL, MySQL e SQL Server

  • PostgreSQL: Suporte completo para índices parciais e de expressão, com ampla flexibilidade.
  • MySQL: Índices parciais não são suportados nativamente (apenas em índices全文 ou com workarounds). Índices de expressão foram introduzidos no MySQL 8.0.13.
  • SQL Server: Índices filtrados (parciais) são suportados desde o SQL Server 2008. Índices de expressão podem ser simulados com colunas computadas indexadas.

6. Estratégias de Criação e Manutenção

6.1. Como identificar candidatos ideais com pg_stat_user_indexes e logs lentos

No PostgreSQL, a view pg_stat_user_indexes mostra estatísticas de uso de índices. Índices com idx_scan = 0 são candidatos a remoção. Logs de consultas lentas (slow query log) revelam padrões como WHERE LOWER(email) = ... que se beneficiariam de índices de expressão.

6.2. Combinando índices parciais com índices compostos

É possível criar índices parciais compostos, combinando a filtragem do WHERE com múltiplas colunas no ON:

CREATE INDEX idx_pedidos_pendentes_data_cliente
ON pedidos (data_criacao, cliente_id)
WHERE status = 'pendente';

6.3. Quando substituir um índice tradicional por um índice especializado

Substitua um índice tradicional por um parcial quando a maioria das consultas filtra por um subconjunto estável de linhas. Substitua por um de expressão quando consultas frequentes usam funções que impedem o uso do índice tradicional.

6.4. Monitoramento e rebuild periódico

Índices parciais e de expressão também sofrem fragmentação. Monitore com pg_stat_user_tables e reconstrua quando necessário:

REINDEX INDEX idx_pedidos_pendentes;

7. Conclusão e Boas Práticas

7.1. Checklist para decidir entre índice parcial, de expressão ou tradicional

  1. A consulta filtra por um subconjunto específico de linhas? → Considere índice parcial.
  2. A consulta usa funções ou transformações nas colunas? → Considere índice de expressão.
  3. O índice tradicional é muito grande e raramente usado? → Considere substituir por parcial.
  4. A função usada é imutável? → Pode usar índice de expressão.
  5. A condição do índice parcial cobre todas as consultas relevantes? → Sim, então use.

7.2. Resumo dos trade-offs (performance vs. manutenção vs. complexidade)

Índices parciais e de expressão oferecem ganhos significativos de performance e economia de espaço, mas aumentam a complexidade do esquema e exigem monitoramento cuidadoso. Em sistemas com alta taxa de escrita, índices parciais muito seletivos podem causar contenção. Em sistemas com muitas consultas analíticas, os benefícios superam os custos.

7.3. Referência cruzada com temas vizinhos (EXPLAIN, índices compostos, normalização)

  • Use EXPLAIN ANALYZE para verificar se o índice está sendo usado.
  • Combine índices parciais com índices compostos para consultas multi-coluna.
  • Considere a normalização antes de criar índices de expressão — às vezes, uma coluna derivada na tabela é mais simples.

Referências