Indexação no PostgreSQL: como criar um índice para uma consulta com múltiplos parâmetros? Por que o índice degradou após seis meses com atualizações ativas?

Para criar um índice no PostgreSQL que acelere consultas com múltiplos parâmetros (por exemplo, filtragem por várias colunas), usa-se um índice composto. Ele é criado assim:

CREATE INDEX idx_name ON table_name (column1, column2, column3);

É importante considerar a ordem das colunas no índice: ela deve corresponder à ordem de uso nas condições WHERE para máxima eficiência.

A causa da degradação do índice durante atualizações ativas é a fragmentação e o acúmulo de "tuplas mortas" (dead tuples). PostgreSQL usa MVCC, e durante as atualizações, versões antigas das linhas não são removidas imediatamente, permanecendo na tabela e no índice, o que reduz o desempenho. Para resolver esse problema, é necessário executar regularmente VACUUM ou VACUUM FULL, bem como ANALYZE para atualizar as estatísticas. O autovacuum geralmente cuida disso, mas em atualizações muito intensas, pode ser necessário ajustar os parâmetros ou intervir manualmente.