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. Os índices parciais são um recurso especializado, mas existem várias situações em que são úteis.
Uma das principais razões para usar um índice parcial é evitar a indexação de valores comuns. Como uma consulta que procura por um valor comum (um valor que ocorre em mais que alguns por cento das linhas da tabela) não vai usar o índice de qualquer maneira, não faz sentido manter essas linhas no índice. Isso reduz o tamanho do índice, acelerando as consultas que usam o índice. Também acelera muitas operações de atualização da tabela, porque o índice não precisa ser atualizado todas às vezes. O Exemplo 11.1 mostra uma possível aplicação dessa ideia.
Exemplo 11.1. Definição de um índice parcial para não incluir valores comuns
Suponha que se esteja armazenando logs de acesso ao servidor web em um banco de dados. A maioria dos acessos tem origem no intervalo de endereços de IP da rede da própria empresa, mas alguns vêm de outros lugares (digamos, funcionários em conexões discadas). Se as procuras por endereço de IP forem principalmente para acessos externos, provavelmente não será necessário indexar o intervalo de endereços de IP que corresponde à rede interna da própria empresa.
Suponha uma tabela como esta:
CREATE TABLE access_log (
url varchar,
client_ip inet,
...
);
Para criar um índice parcial adequado ao nosso exemplo, pode ser usado um comando como este:
CREATE INDEX access_log_client_ip_idx 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 usar este índice seria:
SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
Na consulta acima o endereço de IP da consulta é coberto pelo índice parcial. A consulta a seguir não pode usar o índice parcial, porque usa um endereço de IP não incluído no índice:
SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';
Note que este tipo de índice parcial requer que os valores comuns sejam pré-determinados, portanto estes índices parciais são melhor usados para distribuições de dados que não mudam. Os índices podem ser recriados ocasionalmente para se ajustar a novas distribuições dos dados, mas isto adiciona esforço de manutenção.
Outro uso possível para um índice parcial é não incluir no índice os valores para os quais a carga de trabalho de uma consulta típica não esteja interessada, como mostrado em Exemplo 11.2. Resulta nas mesmas vantagens mostradas acima, mas evita que os valores “sem interesse” sejam acessados por meio deste índice, mesmo que uma verificação de índice possa ser benéfica no caso. Obviamente, configurar índices parciais para este tipo de cenário exige muito cuidado e experimentação.
Exemplo 11.2. Definição de um índice parcial para não incluir valores sem interesse
Havendo uma tabela contendo pedidos faturados e não faturados, em que os pedidos não faturados ocupam uma pequena fração do total da tabela, e ainda assim são as linhas mais acessadas, o desempenho pode ser melhorado criando um índice apenas para os pedidos não faturados. O comando para criar o índice ficaria assim:
CREATE INDEX pedidos_nao_faturados_idx ON pedidos (num_pedido)
WHERE faturado IS NOT TRUE;
Uma possível consulta usando este índice seria:
SELECT * FROM pedidos WHERE faturado IS NOT TRUE AND num_pedido < 10000;
No entanto, o índice também pode ser usado em consultas que não
envolvem num_pedido, como, por exemplo:
SELECT * FROM pedidos WHERE faturado IS NOT TRUE AND quantidade > 5000.00;
Não é tão eficiente quanto um índice parcial na coluna
quantidade seria, porque o sistema
precisa percorrer todo o índice.
Ainda assim, havendo relativamente poucos pedidos não faturados,
usar este índice parcial apenas para encontrar os pedidos não
faturados pode ocasionar um ganho.
Note que a consulta abaixo não pode usar este índice:
SELECT * FROM pedidos WHERE num_pedido = 3501;
porque o pedido 3501 pode estar entre os pedidos faturados e os não faturados.
O Exemplo 11.2 também ilustra que não
é necessário haver correspondência entre a coluna indexada e a coluna
usada no predicado.
O PostgreSQL oferece suporte a índices
parciais com predicados arbitrários, desde que apenas colunas da
tabela que está sendo indexada estejam envolvidas.
No entanto, lembre-se que o predicado deve corresponder às
condições usadas nas consultas que devem se beneficiar do índice.
Para ser preciso, um índice parcial pode ser usado 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 expressões matematicamente
equivalentes escritas de formas diferentes. (Não só seria extremamente
difícil criar um provador de teoremas geral, como provavelmente seria
muito lento para ter qualquer utilidade real.)
O sistema pode reconhecer implicações de desigualdade simples, por
exemplo, “x < 1” implica em “x < 2”;
caso contrário, a condição do predicado deve corresponder exatamente
a parte da condição WHERE da consulta, ou o índice
não será reconhecido como usável.
A correspondência ocorre durante o planejamento da consulta,
não em tempo de execução.
Como consequência, as cláusulas de uma consulta parametrizada não
funcionam com índice parcial.
Por exemplo, uma consulta preparada com um parâmetro pode especificar
“x < ?”, o que não implica em “x < 2”
para todos os valores possíveis do parâmetro.
Um terceiro uso possível para índices parciais não exige que o índice seja usado em consultas. A ideia aqui é criar um índice de unicidade sobre um subconjunto da tabela, como em Exemplo 11.3. Esse índice impõe a unicidade entre as linhas que satisfazem o predicado do índice, sem restringir aquelas que não satisfazem.
Exemplo 11.3. Definição de um índice de unicidade parcial
Suponha que temos uma tabela descrevendo os resultados de um teste. Queremos garantir haver apenas uma entrada informando “bem-sucedido” para uma determinada combinação de assunto e resultado, mas podendo haver qualquer número de entradas informando “mal-sucedido”. Aqui está uma maneira de fazer isto:
CREATE TABLE testes (
assunto text,
resultado text,
sucesso boolean,
...
);
CREATE UNIQUE INDEX unique_sucesso_teste_idx ON testes (assunto, resultado)
WHERE sucesso;
Essa é uma abordagem particularmente eficiente quando há poucos
testes bem-sucedidos, e muitos mal-sucedidos.
Também é possível permitir haver apenas um único valor nulo em
uma coluna criando um índice parcial de unicidade usando a restrição
IS NULL.
Finalmente, um índice parcial também pode ser usado para substituir as opções do plano de consulta do sistema. Além disso, conjuntos de dados com distribuições peculiares podem fazer com que o sistema use um índice quando, na verdade, não deveria usar. Neste caso, o índice pode ser configurado de forma que não esteja disponível para a consulta incorreta. Normalmente, o PostgreSQL faz escolhas razoáveis sobre o uso do índice (por exemplo, os evita ao recuperar valores comuns, portanto o exemplo anterior na verdade salva apenas o tamanho do índice, não sendo necessário para evitar o uso do índice), e escolhas de plano grosseiramente incorretas são motivo para um relatório de bug.
Lembre-se que configurar um índice parcial indica que se sabe, pelo menos, tanto quanto o planejador de consultas sabe, em particular quando um índice pode produzir ganhos. Formar este conhecimento requer experiência e compreensão sobre como os índices funcionam no PostgreSQL. Geralmente, a vantagem de um índice parcial sobre um índice regular será mínima. Há casos em que são bastante contraproducentes, como no Exemplo 11.4.
Exemplo 11.4. Não se usa índices parciais como substituto para particionamento
Pode-se ficar tentado a criar um grande conjunto de índices parciais não sobrepostos, como, por exemplo:
CREATE INDEX minha_tabela_cat_1 ON minha_tabela (data) WHERE categoria = 1; CREATE INDEX minha_tabela_cat_2 ON minha_tabela (data) WHERE categoria = 2; CREATE INDEX minha_tabela_cat_3 ON minha_tabela (data) WHERE categoria = 3; ... CREATE INDEX minha_tabela_cat_NON minha_tabela (data) WHERE categoria =N;
Esta é uma má ideia! Quase certamente será melhor usar um único índice não parcial, declarado como
CREATE INDEX minha_tabela_cat_data ON minha_tabela (categoria, data);
(A coluna categoria foi colocada na frente pelos motivos descritos na Seção 11.3.) Embora uma procura neste índice maior possa ter que descer alguns níveis a mais na árvore do que a procura em um índice menor, quase certamente será mais econômico do que o esforço do planejador necessário para selecionar o índice parcial apropriado. O cerne do problema é que o sistema não entende a relação entre índices parciais, e vai testar laboriosamente cada um para ver se é aplicável à consulta corrente.
Se a tabela for grande o suficiente para que um único índice seja na verdade uma má ideia, deve-se procurar usar o particionamento (veja a Seção 5.12). Com este mecanismo, o sistema entende que as tabelas e os índices não se sobrepõem, sendo assim possível obter um desempenho muito melhor.
Mais informações sobre índices parciais podem ser encontradas em [ston89b], [olson93] e [seshadri95].