8.7. Índices parciais

Um índice parcial é um índice construído sobre um subconjunto da tabela; o subconjunto é definido por uma expressão condicional (chamada de predicado do índice parcial). O índice contém entradas apenas para as linhas da tabela que satisfazem o predicado.

A principal motivação para os índices parciais é evitar a indexação de valores comuns. Uma vez que uma pesquisa procurando por um valor comum (um que apareça em mais do que uma pequena percentagem das linhas da tabela) não vai utilizar o índice de qualquer forma, não faz sentido em manter estas linhas no índice. Isto reduz o tamanho do índice, acelerando as consultas que utilizam este índice. Também acelera muitas operações de atualização da tabela, porque o índice não precisa ser atualizado em todos os casos. O Exemplo 8-1 mostra uma aplicação possível desta idéia.

Exemplo 8-1. Definindo um índice parcial para excluir valores comuns

Supondo que as informações relativas ao acesso ao servidor Web estejam sendo armazenadas no banco de dados, e que a maioria dos acessos se origina na faixa de endereços de IP da própria organização, mas alguns são de fora (digamos, empregados com acesso discado). Se a procura por endereços de IP for principalmente relativa ao acesso externo, provavelmente não será necessário indexar a faixa de endereços de IP correspondente à subrede da própria organização.

Assumindo que exista uma tabela igual a esta:

CREATE TABLE access_log (
    url       varchar,
    client_ip inet,
    ...
);

Para criar um índice parcial adequado para o exemplo acima, deve ser utilizado um comando como este:

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

Uma consulta típica que pode utilizar este índice é:

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

Uma consulta que não pode utilizar este índice é:

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

Observe que este tipo de índice parcial requer que os valores comuns sejam conhecidos a priori. Se a distribuição dos valores for inerente (devido à natureza da aplicação) e estática (não muda com o tempo) isto não é difícil, mas se os valores comuns forem meramente devidos a carga coincidente dos dados isto pode requerer bastante trabalho para manutenção.

Outra possibilidade é excluir os valores do índice para os quais o perfil típico das consultas não esteja interessado; isto está mostrado no Exemplo 8-2. Isto resulta nas mesmas vantagens descritas acima, mas impede o acesso aos valores "que não interessam" por meio deste índice, mesmo que a procura pelo índice seja vantajosa neste caso. Obviamente, definir índice parcial para este tipo de cenário requer muito cuidado e verificação experimental.

Exemplo 8-2. Definindo um índice parcial para excluir valores que não interessam

Se existir uma tabela contendo tanto pedidos faturados quanto não faturados, onde os pedidos não faturados ocupam uma pequena parte da tabela, mas que são as linhas mais acessadas, é possível melhorar o desempenho criando um índice somente para os pedidos não faturados. O comando para criar o índice deve ficar parecido com este:

CREATE INDEX idx_pedidos_nao_faturados ON pedidos (num_pedido)
    WHERE faturado is not true;

Uma possível consulta utilizando este índice seria

SELECT * FROM pedidos WHERE faturado is not true AND num_pedido < 10000;

Entretanto, o índice também pode ser utilizado em consultas não envolvendo num_pedido como, por exemplo,

SELECT * FROM pedidos WHERE faturado is not true AND valor > 5000.00;

Embora não seja tão eficiente quanto seria um índice parcial englobando a coluna valor, porque o sistema precisa varrer o índice por inteiro, mesmo assim, havendo poucos pedidos não faturados, pode ser vantajoso utilizar este índice parcial para localizar apenas os pedidos não faturados.

Observe que a consulta abaixo não pode utilizar este índice:

SELECT * FROM pedidos WHERE num_pedido = 3501;

O pedido número 3501 pode estar entre os pedidos faturados e os não faturados.

O Exemplo 8-2 também ilustra que a coluna indexada e a coluna utilizada no predicado não precisam corresponder. O PostgreSQL suporta índice parcial com predicados arbitrários, desde que somente colunas da tabela sendo indexada estejam envolvidas. Entretanto, deve-se ter em mente que o predicado deve corresponder às condições utilizadas nas consultas que supostamente vão ser beneficiadas pelo índice. Sendo preciso, o índice parcial pode ser utilizado em uma consulta somente se o sistema puder reconhecer que a condição WHERE da consulta implica matematicamente no predicado do índice. O PostgreSQL não possui um provador de teoremas sofisticado que possa reconhecer predicados matematicamente equivalentes escritos de formas diferentes (Não apenas este provador geral de teoremas seria extremamente difícil de ser criado, como provavelmente também seria muito lento para poder ser usado na prática). O sistema pode reconhecer implicações de desigualdades simples como, por exemplo, "x < 1" implica "x < 2"; senão, a condição do predicado deve corresponder exatamente à condição WHERE da consulta, ou o índice não será reconhecido como utilizável.

Um terceiro uso possível para índices parciais não requer que o índice seja utilizado em nenhuma consulta. A idéia é criar um índice único sobre um subconjunto da tabela, como no Exemplo 8-3, garantindo a unicidade entre as linhas que satisfazem o predicado do índice, sem restringir as que não fazem parte.

Exemplo 8-3. Definindo um índice único parcial

Supondo que exista uma tabela descrevendo resultados de testes. Deseja-se garantir que exista apenas uma entrada "correta" para uma dada combinação de assunto e objetivo, mas que possa haver qualquer número de entradas "incorretas". Abaixo está mostrado um modo de fazer isto:

CREATE TABLE testes (assunto  text,
                     objetivo text,
                     correto  bool,
                    ...);
CREATE UNIQUE INDEX testes_correto_constraint ON testes (assunto, objetivo)
    WHERE correto;

Esta é uma forma particularmente eficiente a ser utilizada quando existem poucas situações corretas e muitas incorretas.

Finalizando, um índice parcial também pode ser utilizado para prevalecer sobre a escolha do plano feito para a consulta pelo sistema. Pode ocorrer que conjuntos de dados com uma distribuição peculiar façam o sistema utilizar um índice quando na realidade não deveria. Neste caso, o índice pode ser definido de tal modo que não esteja disponível para a consulta com problema. Normalmente, o PostgreSQL realiza escolhas razoáveis relativas à utilização dos índices (por exemplo, evita-os ao buscar valores com muita ocorrência, de tal forma que o primeiro exemplo realmente economiza apenas o tamanho do índice, não sendo requerido para evitar a utilização do índice), e a escolha de planos grosseiramente incorretos é motivo para um relatório de erro.

Tenha em mente que a criação de um índice parcial indica que você sabe pelo menos tanto quanto o planejador de consultas sabe, particularmente você sabe quando um índice pode ser vantajoso. A formação deste conhecimento requer experiência e compreensão sobre como os índice no PostgreSQL funcionam. Na maioria dos casos, a vantagem de um índice parcial sobre um índice regular não é muita.

Mais informações relativas aos índices parciais podem ser obtidas em The case for partial indexes, Partial indexing in POSTGRES: research project e Generalized Partial Indexes.