Índices de árvore B do PostgreSQL

Os índices no PostgreSQL são usados para melhorar o desempenho das consultas ao banco de dados, permitindo uma recuperação mais rápida dos registros. O índice B-Tree é o tipo de índice padrão e mais comumente usado, ideal para operações de recuperação de dados que envolvem comparações.

Uso

Os índices B-Tree são usados quando você precisa pesquisar, recuperar e classificar dados com eficiência para operações que usam consultas de igualdade ou intervalo. Eles são criados automaticamente nas chaves primárias, mas podem ser criados manualmente em outras colunas para otimizar o desempenho da consulta.

CREATE INDEX index_name ON table_name (column_name);

Nessa sintaxe, o CREATE INDEX cria um novo índice com um nome especificado em uma coluna específica de uma tabela, aumentando a velocidade de pesquisa e recuperação. Os índices B-Tree também armazenam valores NULL, o que pode ser particularmente útil quando as consultas envolvem a verificação da presença ou ausência de tais valores.

Exemplos

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

CREATE INDEX idx_employee_name ON employees (name);

Este exemplo cria um índice B-Tree na coluna name da tabela employees para acelerar as consultas que filtram ou classificam por nomes de funcionários.

SELECT * FROM employees WHERE name = 'John Doe';

Essa consulta se beneficia do índice, melhorando a velocidade da pesquisa.

2. Índice com várias colunas

CREATE INDEX idx_order_date_customer ON orders (order_date, customer_id);

Essa sintaxe cria um índice B-Tree nas colunas order_date e customer_id, o que é benéfico para consultas que envolvem ambos os campos na tabela orders.

SELECT * FROM orders WHERE order_date = '2023-10-01' AND customer_id = 123;

Aqui, o índice otimiza a recuperação por order_date e customer_id.

3. Índice único

CREATE UNIQUE INDEX idx_unique_email ON users (email);

Um índice B-Tree exclusivo é criado na coluna email, garantindo que todos os endereços de e-mail da tabela users sejam distintos e, ao mesmo tempo, melhorando o desempenho da consulta.

SELECT * FROM users WHERE email = 'example@example.com';

O índice exclusivo acelera essa consulta, localizando rapidamente o endereço de e-mail específico.

Dicas e práticas recomendadas

1. Indexar seletivamente. Crie índices somente em colunas usadas com frequência em condições de pesquisa ou operações de união.
2. Monitorar o uso do índice. Analise regularmente o uso do índice com as ferramentas do PostgreSQL, como pg_stat_user_indexes e EXPLAIN, para garantir que eles sejam benéficos.
3. Seja cauteloso com as atualizações. Lembre-se de que os índices podem tornar as operações INSERT, UPDATE e DELETE mais lentas, portanto, equilibre a necessidade de velocidade com a sobrecarga de manutenção dos índices.
4. Considere o tamanho do índice. Índices grandes podem consumir muito espaço em disco, portanto, gerencie-os com sabedoria.
5. Use índices parciais. Crie índices parciais quando apenas um subconjunto de dados for consultado, melhorando o desempenho e reduzindo o tamanho do índice. Por exemplo:

   CREATE INDEX idx_active_users ON users (status) WHERE status = 'active';
           

Manutenção de índices

• Use o site REINDEX para reconstruir índices periodicamente, o que pode melhorar o desempenho e recuperar espaço de armazenamento à medida que os dados mudam com o tempo. Por exemplo:

  REINDEX INDEX idx_employee_name;