11.5. Índices em expressões

Uma coluna do índice não precisa ser apenas uma coluna da tabela subjacente, pode ser uma função ou uma expressão escalar computada a partir de uma ou mais colunas da tabela. Esta funcionalidade é útil para obter acesso rápido às tabelas com base em resultados de cálculos. [1]

Por exemplo, uma forma habitual de fazer comparações não diferenciando letras maiúsculas de minúsculas é utilizar a função lower:

SELECT * FROM teste1 WHERE lower(col1) = 'valor';

-- Para não diferenciar maiúsculas e minúsculas, acentuadas ou não (N. do T.)

SELECT * FROM teste1 WHERE lower(to_ascii(col1)) = 'valor';

Esta consulta pode utilizar um índice, caso algum tenha sido definido sobre o resultado da operação lower(col1):

CREATE INDEX idx_teste1_lower_col1 ON teste1 (lower(col1));

-- Para incluir as letras acentuadas (N. do T.)

CREATE INDEX idx_teste1_lower_ascii_col1 ON teste1 (lower(to_ascii(col1)));

Se o índice for declarado como UNIQUE, este impede a criação de linhas cujos valores de col1 diferem apenas em letras maiúsculas e minúsculas, assim como a criação de linhas cujos valores de col1 são realmente idênticos. Portanto, podem ser utilizados índices em expressões para impor restrições que não podem ser definidas como restrições simples de unicidade.

Como outro exemplo, quando são feitas habitualmente consultas do tipo

SELECT * FROM pessoas WHERE (primeiro_nome || ' ' || ultimo_nome) = 'Manoel Silva';

então vale a pena criar um índice como:

CREATE INDEX idx_pessoas_nome ON pessoas ((primeiro_nome || ' ' || ultimo_nome));

A sintaxe do comando CREATE INDEX normalmente requer que se escreva parênteses em torno da expressão do índice, conforme mostrado no segundo exemplo. Os parênteses podem ser omitidos quando a expressão for apenas uma chamada de função, como no primeiro exemplo.

É relativamente dispendioso manter expressões de índice, uma vez que a expressão derivada deve ser computada para cada linha inserida, ou sempre que for atualizada. Portanto, devem ser utilizadas somente quando as consultas que usam o índice são muito freqüentes.