Índices de expressão do PostgreSQL

Os índices de expressão no PostgreSQL permitem que você crie um índice com base em expressões ou cálculos envolvendo uma ou mais colunas. Eles são usados para otimizar consultas que envolvem cálculos ou transformações complexas diretamente na cláusula `WHERE`.

Uso

Os índices de expressão são particularmente úteis quando as consultas envolvem frequentemente valores ou funções computados. Eles melhoram o desempenho da consulta indexando o resultado de uma expressão em vez dos dados brutos da coluna.

CREATE INDEX index_name ON table_name (expression);

Nessa sintaxe, `expressão` representa o cálculo ou a função cujo resultado você deseja indexar, aumentando a eficiência da recuperação para consultas que envolvam essa expressão.

Exemplos

1. Índice de expressões básicas

CREATE INDEX idx_lower_email ON users (LOWER(email));

Aqui, um índice é criado na versão em minúsculas da coluna `email` na tabela `users`, otimizando as pesquisas sem distinção entre maiúsculas e minúsculas.

2. Indexação de um valor computado

CREATE INDEX idx_total_cost ON orders (quantity * price);

Esse exemplo cria um índice sobre o resultado da multiplicação de `quantity` por `price` na tabela `orders`, acelerando as consultas que filtram com base no custo total.

3. Índice com uma função

CREATE INDEX idx_date_part ON events (date_part('year', event_date));

Esse exemplo indexa o ano extraído de `event_date` usando a função `date_part`, melhorando o desempenho de consultas baseadas em ano.

Dicas e práticas recomendadas

• Use índices de expressão para expressões usadas com frequência. Indexar expressões que aparecem com frequência na cláusula `WHERE` para aumentar o desempenho.
• Considere os custos indiretos de manutenção. Os índices de expressão podem aumentar o tempo das operações `INSERT` e `UPDATE` devido à indexação adicional.
• Impacto no desempenho do teste. Sempre teste o impacto sobre o desempenho dos índices de expressão recém-criados, pois eles nem sempre oferecem um benefício.
• Mantenha as expressões simples. Expressões complexas podem não produzir melhorias significativas no desempenho e podem complicar a manutenção do índice.
• Entenda as limitações. Os índices de expressão podem não ser benéficos quando a seletividade do índice é baixa ou as expressões são muito complexas, o que pode levar a ganhos mínimos de desempenho.

Considerações adicionais

• Diferença entre índices regulares e parciais: Ao contrário dos índices regulares que usam dados brutos da coluna, os índices de expressão usam valores computados. Os índices parciais, por outro lado, são criados com uma condição para indexar apenas um subconjunto de linhas, o que é diferente do uso de expressões.
• Consistência e atualizações de dados: O PostgreSQL atualiza automaticamente as expressões indexadas quando os dados são alterados. No entanto, você deve estar ciente de que isso pode aumentar a sobrecarga das operações `INSERT` e `UPDATE`.
• Eliminação de um índice de expressão: Para remover um índice de expressão, você pode usar a seguinte sintaxe:

DROP INDEX index_name;

Isso pode ser útil se um índice não for mais necessário ou afetar negativamente o desempenho.