PostgreSQL - INDEXES

Os índices são tabelas de pesquisa especiais que o mecanismo de pesquisa do banco de dados pode usar para acelerar a recuperação de dados. Simplificando, um índice é um ponteiro para dados em uma tabela. Um índice em um banco de dados é muito semelhante a um índice no final de um livro.

Por exemplo, se você quiser fazer referência a todas as páginas de um livro que discute um determinado tópico, deve primeiro consultar o índice, que lista todos os tópicos em ordem alfabética e, em seguida, referir-se a um ou mais números de página específicos.

Um índice ajuda a acelerar consultas SELECT e cláusulas WHERE; no entanto, torna a entrada de dados mais lenta, com as instruções UPDATE e INSERT. Os índices podem ser criados ou eliminados sem efeito nos dados.

A criação de um índice envolve a instrução CREATE INDEX, que permite nomear o índice, especificar a tabela e qual coluna ou colunas indexar e indicar se o índice está em ordem crescente ou decrescente.

Os índices também podem ser exclusivos, semelhantes à restrição UNIQUE, no sentido de que o índice evita entradas duplicadas na coluna ou combinação de colunas nas quais há um índice.

O comando CREATE INDEX

A sintaxe básica de CREATE INDEX é o seguinte -

CREATE INDEX index_name ON table_name;

Tipos de índice

O PostgreSQL fornece vários tipos de índice: B-tree, Hash, GiST, SP-GiST e GIN. Cada tipo de índice usa um algoritmo diferente que é mais adequado para diferentes tipos de consultas. Por padrão, o comando CREATE INDEX cria índices de árvore B, que se ajustam às situações mais comuns.

Índices de coluna única

Um índice de coluna única é aquele criado com base em apenas uma coluna da tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
ON table_name (column_name);

Índices de várias colunas

Um índice de várias colunas é definido em mais de uma coluna de uma tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Seja para criar um índice de coluna única ou um índice de várias colunas, leve em consideração a (s) coluna (s) que você pode usar com muita frequência na cláusula WHERE de uma consulta como condições de filtro.

Se houver apenas uma coluna usada, um índice de coluna única deve ser a escolha. Se houver duas ou mais colunas que são frequentemente usadas na cláusula WHERE como filtros, o índice de várias colunas seria a melhor escolha.

Índices Únicos

Índices exclusivos são usados ​​não apenas para desempenho, mas também para integridade de dados. Um índice exclusivo não permite que nenhum valor duplicado seja inserido na tabela. A sintaxe básica é a seguinte -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices Parciais

Um índice parcial é um índice construído sobre um subconjunto de uma tabela; o subconjunto é definido por uma expressão condicional (chamada de predicado do índice parcial). O índice contém entradas apenas para as linhas da tabela que satisfazem o predicado. A sintaxe básica é a seguinte -

CREATE INDEX index_name
on table_name (conditional_expression);

Índices Implícitos

Índices implícitos são índices criados automaticamente pelo servidor de banco de dados quando um objeto é criado. Os índices são criados automaticamente para restrições de chave primária e restrições exclusivas.

Exemplo

O seguinte é um exemplo em que criaremos um índice na tabela COMPANY para a coluna de salários -

# CREATE INDEX salary_index ON COMPANY (salary);

Agora, vamos listar todos os índices disponíveis na tabela COMPANY usando \d company comando.

# \d company

Isso produzirá o seguinte resultado, onde company_pkey é um índice implícito, que foi criado quando a tabela foi criada.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Você pode listar todo o banco de dados de índices usando o \di comando -

O comando DROP INDEX

Um índice pode ser eliminado usando PostgreSQL DROPcomando. Deve-se ter cuidado ao descartar um índice, pois o desempenho pode ser reduzido ou melhorado.

A sintaxe básica é a seguinte -

DROP INDEX index_name;

Você pode usar a seguinte instrução para excluir o índice criado anteriormente -

# DROP INDEX salary_index;

Quando os índices devem ser evitados?

Embora os índices tenham como objetivo aprimorar o desempenho de um banco de dados, há momentos em que eles devem ser evitados. As seguintes diretrizes indicam quando o uso de um índice deve ser reconsiderado -

• Os índices não devem ser usados ​​em tabelas pequenas.

• Tabelas que possuem operações de inserção ou atualização de lote frequentes e grandes.

• Os índices não devem ser usados ​​em colunas que contêm um grande número de valores NULL.

• As colunas que são frequentemente manipuladas não devem ser indexadas.