11.7. Índices parciais

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

O principal motivo para criar índices parciais é evitar a indexação de valores freqüentes. Como um comando procurando por um valor freqüente (um que apareça em mais que uma pequena percentagem de linhas da tabela) não utiliza o índice de qualquer forma, não faz sentido 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 11-1 mostra uma aplicação possível desta idéia.

CREATE TABLE tbl_registro_acesso (
    url         varchar,
    ip_cliente  inet,
    ...
);

CREATE INDEX idx_registro_acesso_ip_cliente ON tbl_registro_acesso (ip_cliente)
    WHERE NOT (ip_cliente > inet '192.168.100.0' AND ip_cliente < inet '192.168.100.255');

SELECT * FROM tbl_registro_acesso WHERE url = '/index.html' AND ip_cliente = inet '212.78.10.32';

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

Outra possibilidade é excluir do índice os valores para os quais o perfil típico das consultas não tenha interesse, conforme mostrado no Exemplo 11-2. Isto resulta nas mesmas vantagens mostradas acima, mas impede o acesso aos valores "que não interessam" por meio deste índice, mesmo se a varredura do índice for vantajosa neste caso. Obviamente, definir índice parcial para este tipo de cenário requer muito cuidado e experimentação.

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

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

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

SELECT * FROM pedidos WHERE num_pedido = 3501;

O Exemplo 11-2 também ilustra que a coluna indexada e a coluna utilizada no predicado não precisam corresponder. O PostgreSQL suporta índices parciais com predicados arbitrários, desde que somente estejam envolvidas colunas da tabela indexada. Entretanto, deve-se ter em mente que o predicado deve corresponder às condições utilizadas nos comandos que supostamente vão se beneficiar do índice. Para ser preciso, o índice parcial somente pode ser utilizado em um comando se o sistema puder reconhecer que a condição WHERE do comando implica matematicamente no predicado do índice. O PostgreSQL não possui um provador de teoremas sofisticado que possa reconhecer expressões equivalentes matematicamente escritas de forma diferente (Não seria apenas extremamente difícil criar este provador de teoremas geral, como este 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 a uma parte da 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 nenhum comando. A idéia é criar um índice único sobre um subconjunto da tabela, como no Exemplo 11-3, impondo a unicidade das linhas que satisfazem o predicado do índice, sem restringir as que não fazem parte.

CREATE TABLE tbl_teste
    (pergunta  text,
     resposta  text,
     correto   bool
     ...
);

CREATE UNIQUE INDEX unq_resposta_correta ON tbl_teste (pergunta, correto)
    WHERE correto;

Finalizando, também pode ser utilizado um índice parcial para mudar a escolha do plano de comando feito pelo sistema. Pode ocorrer que conjuntos de dados com distribuições peculiares façam o sistema utilizar um índice quando na realidade não deveria. Neste caso, o índice pode ser definido de modo que não esteja disponível para o comando com problema. Normalmente, o PostgreSQL realiza escolhas razoáveis com relação à utilização dos índices (por exemplo, evita-os ao buscar valores com muitas ocorrências, desta maneira o primeiro exemplo realmente economiza apenas o tamanho do índice, mas não é necessário para evitar a utilização do índice), e a escolha de um plano grosseiramente incorreto é motivo para um relatório de erro.

Deve-se ter em mente que a criação de um índice parcial indica que você sabe pelo menos tanto quanto o planejador de comandos sabe. Em particular, você sabe quando um índice poderá ser vantajoso. A formação deste conhecimento requer experiência e compreensão sobre como os índices funcionam no PostgreSQL. Na maioria dos casos, a vantagem de um índice parcial sobre um índice regular não é muita.

Podem ser obtidas informações adicionais sobre índices parciais em The case for partial indexes , Partial indexing in POSTGRES: research project e Generalized Partial Indexes .