AltaVista
Google

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.

Exemplo 11-1. Definir um índice parcial excluindo valores freqüentes

Suponha que os registros de acesso ao servidor Web são 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 sobre 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 como esta:

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

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

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');

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

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

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

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

Deve ser observado que este tipo de índice parcial requer que os valores comuns sejam determinados a priori. Se a distribuição dos valores for inerente (devido à natureza da aplicação) e estática (não muda com o tempo) não é difícil, mas se os valores freqüentes se devem meramente à carga de dados coincidentes, pode ser necessário bastante trabalho de manutenção.

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.

Exemplo 11-2. Definir um índice parcial excluindo valores que não interessam

Se existir uma tabela contendo tanto pedidos faturados quanto não faturados, onde os pedidos não faturados representam uma pequena parte da tabela, mas são os mais acessados, é possível melhorar o desempenho criando um índice somente para os pedidos não faturados. O comando para criar o índice deve ser 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 é

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 na coluna valor, porque o sistema precisa percorrer o índice por inteiro, mesmo assim, havendo poucos pedidos não faturados, a utilização do índice parcial para localizar apenas os pedidos não faturados pode ser vantajosa.

Deve ser observado 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 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.

Exemplo 11-3. Definir um índice único parcial

Suponha que exista uma tabela contendo perguntas e respostas. Deseja-se garantir que exista apenas uma resposta "correta" para uma dada pergunta, mas que possa haver qualquer número de respostas "incorretas". Abaixo está mostrada a forma de fazer:

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

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

Esta forma é particularmente eficiente quando existem poucas respostas corretas, e muitas incorretas.

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 .

Notas

[1]

predicado — especifica uma condição que pode ser avaliada para obter um resultado booleano. (ISO-ANSI Working Draft) Foundation (SQL/Foundation), August 2003, ISO/IEC JTC 1/SC 32, 25-jul-2003, ISO/IEC 9075-2:2003 (E) (N. do T.)

[2]

Os sistemas gerenciadores de banco de dados SQL Server 2000, Oracle 10g e DB2 8.1 não possuem suporte a índices parciais. Comparison of relational database management systems (N. do T.)

SourceForge.net Logo CSS válido!