Índice do PostgreSQL

An index is a special table query that accelerates search engine data retrieval. Simply put, an index is a pointer to the data in the table. An index in a database is very similar to the index directory in a book.

Using the table of contents page (index) of a Chinese dictionary as an example, we can quickly find the needed character by sorting the table of contents (index) by pinyin, strokes, or radicals.

Indexes help speed up SELECT queries and WHERE clauses, but they can slow down data input when using UPDATE and INSERT statements. Indexes can be created or deleted, but they do not affect the data.

Utilize a CREATE INDEX statement to create an index, which allows for named indexes, specifying the table and one or more columns to be indexed, and indicating whether the index is in ascending or descending order.

Os índices também podem ser únicos, semelhantes à restrição UNIQUE, e impedem entradas repetidas em colunas ou combinações de colunas.

Comando CREATE INDEX

A sintaxe do CREATE INDEX (criar índice) é a seguinte:

CREATE INDEX index_name ON table_name;

Tipo de índice

Índice de coluna única

O índice de coluna única é um índice criado com base em uma coluna da tabela, e a sintaxe básica é a seguinte:

CREATE INDEX index_name
ON table_name (nome_coluna);

Índice composto

Os índices compostos são criados com base em várias colunas da tabela, e a sintaxe básica é a seguinte:

CREATE INDEX index_name
ON table_name (column1_name,　column2_name);

Independentemente de ser um índice de coluna única ou um índice composto, a coluna deve ser usada com muita frequência na condição de filtro da cláusula WHERE.

Se apenas uma coluna for usada, escolha o índice de coluna única; se houver várias colunas, use o índice composto.

Índice único

O uso de índices únicos não é apenas para melhorar o desempenho, mas também para garantir a integridade dos dados. Os índices únicos não permitem a inserção de valores repetidos na tabela. A sintaxe básica é a seguinte:

CREATE UNIQUE INDEX index_name
on table_name (nome_coluna);

Índice local

Os índices locais são construídos em subconjuntos da tabela; o subconjunto é definido por uma expressão condicional. Os índices contêm apenas as linhas que satisfazem a condição. A sintaxe básica é a seguinte:

CREATE INDEX index_name
on table_name (expressão_condicional);

Índice implícito

Os índices implícitos são criados automaticamente pelo servidor do banco de dados no momento da criação do objeto. Os índices são criados automaticamente para restrições de chave primária e única.

Exemplo online

O exemplo a seguir cria um índice na coluna SALARY da tabela COMPANY:

#　CREATE　INDEX　salary_index　ON　COMPANY　(salary);

Agora, usando 　\d company O comando lista todos os índices da tabela COMPANY:

#　\d　company

Os resultados obtidos são os seguintes, company_pkey é um índice implícito, criado no momento da criação da tabela:

w3codeboxdb=#　\d　company
　　　　　　　　　　　　　　　　　　Tabela "public.company"
　Coluna　|　Tipo　|　Colação　|　Nulo　|　Padrão　
---------+---------------+-----------+----------+---------
　id　|　inteiro　|　|　não null　|　
　nome | text | | | not null　
　idade | integer | | | not null　
　endereco | character(|50) | | | |　
　salary | real | | |　
Índices:
　　　　"company_pkey" PRIMARY KEY, btree (id)
　　　　"salary_index" btree (salary)

Você pode usar o comando \di para listar todos os índices do banco de dados:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Lista de relações
　Esquema | Nome | Tipo | Proprietário | Tabela　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
　public | salary_index | index | postgres | company
(3　rows)

DROP INDEX (remover índice)

Um índice pode ser removido usando o comando DROP do PostgreSQL.

DROP INDEX index_name;

Você pode usar a seguinte declaração para excluir o índice criado anteriormente:

# DROP INDEX salary_index;

Após a exclusão, você pode ver que o salary_index foi removido da lista de índices:

w3codeboxdb=# \di
　　　　　　　　　　　　　　　　　　　　Lista de relações
　Esquema | Nome | Tipo | Proprietário | Tabela　　　　
--------+-----------------+-------+----------+------------
　public | company_pkey | index | postgres | company
　public | department_pkey | index | postgres | department
(2　rows)

Em que situações deve-se evitar o uso de índices?

Embora o objetivo dos índices seja melhorar o desempenho do banco de dados, existem algumas situações em que é necessário evitar o uso de índices.

Ao usar índices, é necessário considerar os seguintes critérios:

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

• Os índices não devem ser usados em tabelas que têm frequentes atualizações ou inserções em grandes volumes.

• Os índices não devem ser usados em colunas que contêm muitos valores NULL.

• Os índices não devem ser usados em colunas de operações frequentes.