PostgreSQL CREATE INDEX

Os índices no PostgreSQL são objetos de banco de dados que melhoram a velocidade das operações de recuperação de dados. Eles servem como ponteiros para que você localize rapidamente as linhas em uma tabela, melhorando o desempenho das consultas.

Uso

Os índices são usados para otimizar o desempenho de consultas SELECT e cláusulas WHERE. Eles são especialmente úteis para tabelas grandes em que você precisa de acesso rápido aos dados.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Nessa sintaxe, `CREATE INDEX` define um novo índice chamado `index_name` no `table_name` e na(s) coluna(s) especificada(s).

Exemplos

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

CREATE INDEX idx_customer_name
ON customers (customer_name);

Esse exemplo cria um índice na coluna `customer_name` da tabela `customers`, o que acelera as pesquisas que envolvem essa coluna.

2. Índice composto

CREATE INDEX idx_order_customer
ON orders (customer_id, order_date);

Esse índice composto na tabela `orders` usa as colunas `customer_id` e `order_date`, melhorando o desempenho das consultas que filtram por essas colunas. Os índices compostos são úteis quando as consultas envolvem frequentemente várias colunas em suas cláusulas WHERE.

3. Índice único

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

Um índice exclusivo garante que todos os valores da coluna indexada `email` sejam distintos na tabela `users`, evitando entradas duplicadas e otimizando as pesquisas exclusivas.

4. Índice parcial

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

Um índice parcial é criado na coluna `last_login_date` somente para as linhas em que `active` é verdadeiro, otimizando as consultas que têm como alvo esse subconjunto de dados.

5. Índice de texto completo

CREATE INDEX idx_document_content
ON documents USING gin(to_tsvector('english', content));

Este exemplo cria um índice de texto completo na coluna `content` da tabela `documents`, melhorando o desempenho das consultas de pesquisa de texto completo.

Impacto da indexação

Os índices podem melhorar significativamente o desempenho das consultas de leitura, mas também podem afetar o desempenho das operações de gravação, como `INSERT`, `UPDATE` e `DELETE`. A compensação envolve equilibrar a recuperação de dados mais rápida com a modificação de dados potencialmente mais lenta.

Dicas e práticas recomendadas

• Indexar seletivamente. Indexe apenas as colunas que são usadas com frequência nas condições de consulta para evitar sobrecarga desnecessária.
• Monitorar e manter. Analise e mantenha regularmente os índices para garantir que eles estejam sendo utilizados de forma eficaz.
• Cuidado com o excesso de indexação. A indexação excessiva pode tornar mais lentas as modificações de dados como `INSERT`, `UPDATE` e `DELETE`.
• Use índices parciais. Considere índices parciais para colunas com um grande número de NULLs ou um subconjunto específico de dados.
• Aproveite os índices exclusivos. Use índices exclusivos para reforçar a integridade dos dados e melhorar o desempenho da consulta.
• Recursos avançados. Explore as opções de indexação, como índices de expressão para valores calculados ou índices de cobertura para incluir colunas adicionais no índice.
• Use REINDEX. Periodicamente, use o comando `REINDEX` para reconstruir índices corrompidos ou melhorar o desempenho após alterações significativas nos dados.