Como Manipular Índices no PostgreSQL

Hoje vou abordar um tema muito interessante, como manipular índices no PostgreSQL, neste post irei argumentar em qual situação você pode usá-los e como você pode fazer isso!

A criação dos índices está intrinsecamente ligada ao desempenho do banco de dados, isto é, no momento da consulta o índice aponta onde está um determinado registro, ou seja, ele é um direcionador, de modo que a consulta seja rápida, precisa e portanto eficiente. É importante que você tenha em mente que toda tabela deve ter ao menos um índice (a maioria tem a chave primária como índice) isso porque, caso sua tabela não tenha será necessário que o Postgres verifique registro por registro – seq scan – até encontrar o que você procura, esse processo demanda muito processamento e pode, inclusive, derrubar o servidor.

Assim você pode pensar: “oras, então criarei índices por toda parte no banco de dados”, e eu te digo: acalme-se! Os índices são excelentes ferramentas, mas quando usados em demasia não tem efeito algum. E é claro eles tem seus custos, como a ocupação de espaço em disco, inclusive você pode até mudá-lo de table space, veja abaixo nas instruções SQL para o manejo dos índices.

Sugiro que leia mais sobre o funcionamento dos índices, neste artigo, ele explica bem o funcionamento, de modo que você estabeleça uma politica criteriosa para a criação deles.

Como Manipular Índices no PostgreSQL

Agora vejamos a sintaxe SQL para o manejo dos índices:

Criando um índice:

CREATE INDEX idx_pessoas_nome ON pessoas USING hash (nome);

Criando um índice que “incorpora” dos campos ao mesmo tempo:

CREATE INDEX idx_usuario_login_senha ON usuario USING btree (login,senha);

Criando um índice, mas indexando apenas parte do campo:

CREATE INDEX idx_pessoas_endereco ON pessoas USING btree (substr(endereco, 1, 10));

Criando um índice em um determinado tablespace:

CREATE INDEX idx_compras ON compras (item) TABLESPACE outro_tablespace;

Criando um índice com a cláusula WHERE:

CREATE UNIQUE INDEX ind_nome_tiago ON pessoa USING GIN (nome) WHERE nome iLIKE 'Tiago%';

Renomeando um índice:

CREATE INDEX idx_pessoas_endereco RENAME TO idx_pessoas_endereco_completo

Excluindo (dropando) um índice:

DROP INDEX idx_pessoas_endereco_completo

Outros Atributos

UNIQUE: Declarado, o Postgres não irá indexar registros duplicados e retornará um erro. Veja o exemplo de uso:

CREATE INDEX UNIQUE idx_pessoa_cpf ON pessoas USING btree (cpf);

CONCURRENTLY: Caso seja declarado ele informa que não será realizado o bloqueio do índice durante procedimentos de DML (insert, update, etc). Veja o exemplo de uso:

CREATE INDEX CONCURRENTLY idx_pessoa_rg ON pessoas USING btree (rg);

Algorítimos de Indexação

B-tree: É o tipo padrão (assume quando não indicamos). São índices que podem tratar consultas de igualdade e de faixa, em dados que podem ser classificados. Indicado para consultas com os operadores: <, <=, =, >=, >. Também pode ser utilizado com LIKE, ILIKE, ~ e ~*.
R-tree: Tipo mais adequado a dados espaciais. Adequado para consultas com os operadores: <<, &<, &>, >>, @, ~=, &&.
HASH: Indicados para consultas com comparações de igualdade simples. É desencorajado seu uso. Em seu lugar recomenda-se o B-tree.
GiST: Utilizado para retornar resultados com perdas, isto é, retorna os dados mais rapidamente comparando por coincidências de bits, prefira usar b-tree que é mais confiável.
GIN: Três vezes mais rápida (e três vezes maior também) que a GiST, indexa a partir das palavras, mas cuidado, caso existam muito o desempenho cai, recomendável utilizar para indexar nomes.

Observações e dicas:

Somente os tipos B-tree e GiST suportam índices com várias colunas. Índices com mais de um campo somente será utilizado se as cláusulas com os campos indexados forem ligados por AND. Um índice com mais de 3 campos dificilmente será utilizado.

Referências