Criação de Índices Parciais (Partial Indexes)

Em tabelas com muitos registros, a utilização de índices normais pode causar um desempenho insatisfatório, principalmente quando se trata decolunas que representam abstrações de dados com pouca variação. É o caso de colunas representando tempo (DATE, TIME e TIMESTAMP), ou colunas numéricas representando tipos pré-definidos(Ex.: Regiões, Sexo, Faixa Salarial, etc.).

Tabelas de movimentação analítica com estes tipos de colunas podem conter milhares, ou até milhões de registros. Entretanto, em consultas SQL específicas por um determinado valor, um índice normal completo (Full Index) irá considerar na consulta todos os registros da tabela, organizados na ordem do índice.

O PostgreSQL possui um fantástico recurso para criação de índices que permite delimitar os registros que este irá considerar. Isto representa um enorme ganho de desempenho, especialmente em consultas SQL que utilizam filtros complexos no WHERE.

Vamos exemplificar este caso. Considere uma tabela de movimentação analítica de estoque de uma empresa de comércio comum. Vamos usar um modelo simples, apenas para demonstrar o caso. Utilize o código SQL abaixo para criar a tabela:

-- Criação da Tabela

CREATE TABLE estoque(

ID_Empresa INT2 NOT NULL,

ID_Produto INT4 NOT NULL,

ID_Local_Estoque INT2 NOT NULL,

TIPO_Entrada BOOLEAN NOT NULL DEFAULT false,

QTD_Quantidade NUMERIC(12,6) DEFAULT 0.000000,

VAL_Unitario NUMERIC(15,3) DEFAULT 0.000,

DT_Movimento DATE NOT NULL

);



-- Definição da chave primária

ALTER TABLE estoque ADD PRIMARY KEY(ID_Empresa,ID_Produto,ID_Local_Estoque);



-- Criação de Índice sobre o campo Data

CREATE INDEX idx_DATA ON estoque (DT_Movimento, ID_Empresa);

Imagine esta tabela com mais de 2.000.000 registros. O departamento de gerência de estoque emite relatórios mensais sobre a movimentação de estoque dos produtos para conferência. Um exemplo de relatório é de Entrada e Saída Consolidada, que considera os valores de entrada e saída por período. Um SQL típico para demonstrar as informações do mês de Dezembro de 2006 utilizaria o filtro no WHERE mencionando o campo DT_Movimento da seguinte forma: (…) WHERE DT_Movimento BETWEEN 2006-12-01 AND 2006-12-31.

Considere o volume de dados caso a empresa possua um movimento de mais de 50.000 registros por mês, mantendo esta marca desde 01012000. Ao utilizar o WHERE acima, uma varredura completa no índice idx_DATA seria feita, considerando a massa completa de dados no índice.

Dependendo de condições de uso dos registros estes podem estar na memória cache, então o resultado seria rapidamente apresentado. Entretanto, caso uma pesquisa aleatória não armazenada em cache for executada, o custo de IO do gerenciador de banco de dados seria problemático.

A solução neste caso – e uma medida muito satisfatória – é a criação dos índices parciais sobre o campo data, combinando-os com um índice normal completo. É possível criar os índices parciais para datas muito além das atuais, para prever a população de registros na tabela no futuro, de modo a garantir o desempenho. Lembre-se de que se não existirem registros com uma data prevista no índice, este não terá tamanho, portanto não será prejudicial em nenhum aspecto (espaço ou IO).

Para aperfeiçoar o acesso a dados nestas condições, os índices parciais consideram a cláusula SQL WHERE:

-- Criação de Índice sobre o campo Data - Janeiro de 2006

CREATE INDEX idx_DATA_0106 ON estoque (DT_Movimento, ID_Empresa) WHERE (DT_Movimento BETWEEN 2006-01-01 AND 2006-01-31);



-- Criação de Índice sobre o campo Data - Fevereiro de 2006

CREATE INDEX idx_DATA_0206 ON estoque (DT_Movimento, ID_Empresa) WHERE (DT_Movimento BETWEEN 2006-02-01 AND 2006-02-28);



(...)



-- Criação de Índice sobre o campo Data - Dezembro de 2006

CREATE INDEX idx_DATA_1206 ON estoque (DT_Movimento, ID_Empresa) WHERE (DT_Movimento BETWEEN 2006-12-01 AND 2006-12-31);



(...)

Desta forma, a todo SQL onde for utilizado a condição DT_Movimento BETWEEN 2006-12-01 AND 2006-12-31 ou sua equivalente DT_Movimento 2006-12-01 AND DT_Movimento 2006-12-31, o índice idx_DATA_0106 será apresentando para o otimizador interno como o mais eficaz, e portanto será usado.

Uma aplicação muito boa para os índices parciais é a utilização deste em tabelas que fazem parte de VIEWS (Visões) complexas. Todo o WHERE fixo da VIEW pode ser considerado em um índice parcial, o que resulta na diminuição considerável do tempo de resposta.

Novamente deixo ao dispor do leitor o meu endereço de e-mail (ver início do artigo) para contato, principalmente para sugestões de novos artigos relacionados ao POSTGRESQL.

Até breve!