Considerações sobre o desempenho do PostgreSQL para índices

Os índices no PostgreSQL são objetos de banco de dados que melhoram a velocidade das operações de recuperação de dados em uma tabela ao custo de espaço de armazenamento adicional e sobrecarga de manutenção. Eles são essenciais para otimizar o desempenho das consultas, principalmente em grandes conjuntos de dados.

Uso

Os índices são usados para melhorar o desempenho das operações de leitura, como as consultas SELECT, permitindo um acesso mais rápido aos dados. Eles são utilizados automaticamente pelo PostgreSQL ao executar consultas, desde que sejam relevantes para as condições da consulta.

CREATE INDEX index_name
ON table_name (column_name);

Nessa sintaxe, CREATE INDEX gera um índice chamado index_name no column_name especificado de table_name, otimizando as operações de pesquisa nessa coluna.

Exemplos

1. Criação de índices básicos

CREATE INDEX idx_employee_name
ON employees (last_name);

Este exemplo cria um índice na coluna last_name da tabela employees para acelerar as consultas que filtram ou classificam pelo sobrenome.

2. Índice único

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

Aqui, um índice exclusivo garante que todos os valores da coluna email da tabela users sejam distintos, evitando entradas duplicadas e otimizando a eficiência da pesquisa.

3. Índice com várias colunas

CREATE INDEX idx_order_customer_date
ON orders (customer_id, order_date);

Este exemplo demonstra um índice de várias colunas em customer_id e order_date, que pode melhorar o desempenho de consultas que filtram em ambas as colunas, como a recuperação de pedidos de um cliente específico em um intervalo de datas.

Tipos de índice

1. Índice parcial

CREATE INDEX idx_active_users
ON users (email)
WHERE active = true;

Os índices parciais podem ser usados para indexar um subconjunto de tabelas em uma tabela, o que pode ser benéfico para o desempenho quando as consultas frequentemente filtram em condições específicas.

2. Índice de expressão

CREATE INDEX idx_lower_email
ON users (LOWER(email));

Os índices de expressão permitem indexar o resultado de uma expressão, o que é útil para pesquisas sem distinção entre maiúsculas e minúsculas ou valores de colunas computados.

Dicas e práticas recomendadas

• Avalie as necessidades de consulta. Crie índices com base em padrões de consulta; indexar cada coluna é ineficiente.
• Monitorar o uso do índice. Use a visualização pg_stat_user_indexes do PostgreSQL para monitorar o uso do índice e identificar os índices não utilizados.
• Equilíbrio entre leituras e gravações. Considere a compensação entre a melhoria do desempenho de leitura e a sobrecarga nas operações de gravação, pois os índices precisam ser atualizados a cada inserção, atualização ou exclusão.
• Aproveite os índices exclusivos para obter integridade. Use índices exclusivos para impor restrições de integridade de dados sempre que possível.
• Manutenção regular. Analisar e aspirar tabelas regularmente para manter a eficiência do índice e evitar o inchaço.
• Reconstruir índices fragmentados. Use o comando REINDEX para reconstruir os índices quando eles se fragmentarem.
• Avaliar a eficácia do índice. Use o comando EXPLAIN para visualizar os planos de execução de consultas e avaliar a utilização do índice.
• Escolha o tipo de índice correto. Selecione os índices B-tree, GiST ou GIN com base em casos de uso específicos e requisitos de consulta.