Como criar índices no PostgreSQL

Os índices são tabelas de pesquisa especializadas usadas pelos motores de caça ao banco de dados para acelerar os resultados da consulta. Um índice é uma referência às informações em uma tabela. Por exemplo, se os nomes em um livro de contato não estiverem alfabetizados, você teria que descer todas as fileiras e pesquisar todos os nomes antes de atingir o número de telefone específico que está procurando. Um índice acelera os comandos selecionados e onde frases, executando a entrada de dados nos comandos de atualização e inserção. Independentemente de os índices serem inseridos ou excluídos, não há impacto nas informações contidas na tabela. Os índices podem ser especiais da mesma maneira que a limitação única ajuda a evitar registros de réplicas no campo ou conjunto de campos para os quais o índice existe.

Sintaxe geral

A sintaxe geral a seguir é usada para criar índices.

>> Criar index index_name em table_name (column_name);

Para começar a trabalhar em índices, abra o pgadmin do PostgreSQL da barra de aplicativos. Você encontrará a opção 'servidores' exibida abaixo. Clique com o botão direito desta opção e conecte-a ao banco de dados.

Como você pode ver, o banco de dados 'teste' está listado na opção 'Bancos de dados'. Se você não tiver um, clique com o botão direito do mouse em bancos de dados ', navegue até a opção' Criar 'e nomear o banco de dados de acordo com suas preferências.

Expanda a opção 'Esquemas' e você encontrará a opção 'Tabelas' listadas lá. Se você não tiver um, clique com o botão direito do mouse, navegue para 'Criar' e clique na opção 'Tabela' para criar uma nova tabela. Desde que já criamos a tabela 'Emp', você pode vê -la na lista.

Experimente a consulta selecionada no editor de consultas para buscar os registros da tabela 'EMP', como mostrado abaixo.

>> Selecione * do público.EMP Ordem por "ID" ASC;

Os seguintes dados estarão na tabela 'Emp'.

Crie índices de coluna única

Expanda a tabela 'Emp' para encontrar várias categorias, e.g., Colunas, restrições, índices, etc. Clique com o botão direito do mouse 'índices', navegue até a opção 'Criar' e clique em 'índice' para criar um novo índice.

Construa um índice para a tabela 'Emp' fornecida ou exibição, usando a janela de diálogo de índice. Aqui, há duas guias: 'Geral' & 'Definição.'Na guia' Geral ', insira um título específico para o novo índice no campo' Nome '. Escolha o 'espaço de tabela' sob o qual o novo índice será armazenado usando a lista suspensa ao lado de 'tabela de tacos.'Como na área' Comentário ', faça comentários de índice aqui. Para iniciar esse processo, navegue até a guia 'Definição'.

Aqui, especifique o 'método de acesso' selecionando o tipo de índice. Depois disso, para criar seu índice como 'único', existem várias outras opções listadas lá. Na área de 'colunas', toque no sinal '+' e adicione os nomes de colunas a serem usados ​​para indexação. Como você pode ver, estamos aplicando indexação apenas na coluna 'telefone'. Para começar, selecione a seção SQL.

A guia SQL mostra o comando SQL que foi criado por suas entradas em todo o diálogo do índice. Clique no botão 'Salvar' para criar o índice.

Novamente, vá para a opção 'Tabelas' e navegue até a tabela 'Emp'. Atualize a opção 'índices' e você encontrará o índice 'index_on_phone' recém -criado listado nele.

Agora, executaremos o comando explicar selecionar para verificar os resultados dos índices com a cláusula WHERE. Isso resultará na saída a seguir, que diz: 'Scan SEQ no EMP.'Você pode se perguntar por que isso aconteceu enquanto você está usando índices.

Motivo: o planejador do Postgres pode decidir não ter um índice por vários motivos. O estrategista toma as melhores decisões na maioria das vezes, mesmo que as razões nem sempre sejam claras. É bom se uma pesquisa de índice for usada em algumas consultas, mas não em todas. As entradas retornadas de qualquer tabela podem variar, dependendo dos valores fixos retornados pela consulta. Como isso ocorre, uma varredura de sequência é quase sempre mais rápida que uma varredura de índice, indicando que talvez o planejador de consultas estivesse certo ao determinar que o custo de executar a consulta dessa maneira é reduzido.

Crie vários índices de coluna

Para criar índices de várias colunas, abra o shell da linha de comando e considere a tabela a seguir 'aluno' para começar a trabalhar em índices com várias colunas.

>> selecione * do aluno;

Escreva o seguinte, crie uma consulta de índice. Esta consulta criará um índice chamado 'new_index' nas colunas 'Snome' e 'Age' da tabela de 'aluno'.

>> Crie índice new_index no aluno (snome, idade);

Agora, listaremos as propriedades e atributos do recém -criado índice 'new_index' usando o comando '\ d'. Como você pode ver na imagem, este é um índice do tipo Btree que foi aplicado às colunas 'Snome' e 'Age'.

>> \ d new_index;

Crie índice exclusivo

Para construir um índice exclusivo, suponha a seguinte tabela 'EMP'.

>> Selecione * em EMP;

Execute a consulta de índice Create exclusiva no shell, seguida pelo nome do índice 'Empind' na coluna 'Nome' da tabela 'Emp'. Na saída, você pode ver que o índice exclusivo não pode ser aplicado a uma coluna com valores duplicados de 'nome'.

>> Crie um índice exclusivo empind no EMP (nome);

Certifique -se de aplicar o índice exclusivo apenas a colunas que não contêm duplicatas. Para a tabela 'Emp', você pode assumir que apenas a coluna 'ID' contém valores exclusivos. Então, aplicaremos um índice único a ele.

>> Crie um índice exclusivo empind no EMP (ID);

A seguir estão os atributos do índice exclusivo.

>> \ d empid;

Índice de queda

A declaração de queda é usada para remover um índice de uma tabela.

>> Índice de queda Empind;

Conclusão

Embora os índices sejam projetados para melhorar a eficiência dos bancos de dados, em alguns casos, não é possível usar um índice. Ao usar um índice, as seguintes regras devem ser consideradas:

• Os índices não devem ser expulsos para pequenas mesas.
• Tabelas com muitas atualizações/atualização em lote em larga escala ou operações de adição/inserção.
• Para colunas com uma porcentagem substancial de valores nulos, os índices não podem ser misturados-
• oferta.
• A indexação deve ser evitada com colunas manipuladas regularmente.