Criando Índice condicional no PostgreSQL

Assim como os demais SGBDs populares do mercado, o PostgreSQL possui o recurso de índices, que auxiliam o otimizador de consultas na hora de localizar os registros com os filtros de uma query.

Conforme as tabelas do banco de dados vão crescendo, é necessário ajudar o otimizador criando índices que o ajudarão a identificar rapidamente os registros desejados, porém, nem sempre a simples criação de um índice comum atende aos critérios desejados.

Por exemplo: Supondo que tenhamos uma tabela de usuários e, ao remover um usuário do sistema, ao invés de excluir o registro do banco de dados, simplesmente marcamos seu status como 0 (zero). Sendo assim, todas as pesquisas de usuários no sistema incluirão o filtro where status=1 para garantir que usuários excluídos não sejam manipulados no sistema.

Nesse caso, se criarmos um índice com a coluna status, estaremos armazenando informação de registros que jamais serão utilizados nas consultas mais básicas como consulta de login, pesquisa por nome, rg, cpf, etc.

Então, criamos um índice com um critério específico:

CREATE INDEX idx_usuario_login_senha ON usuario USING btree (login,senha) <em>WHERE STATUS = 1</em>;

Nesse caso, o índice armazenará somente os registros onde o status seja igual a 1. Economizando espaço em disco para os registros “mortos” e otimizando ainda mais o custo da consulta, como no exemplo abaixo:

SELECT * FROM usuario WHERE login = 'marcelo' AND senha = '123456'<em> AND STATUS = 1</em>;

A query acima também seria otimizada, caso criássemos um índice composto com os campos login, senha e status. Só que perderíamos performance a cada consulta já que sempre buscamos nessa tabela os registros cujo status seja = 1, ou seja, o usuário que esteja ativo no sistema.