Descrição

O comando CREATE INDEX constrói um índice na(s) coluna(s) especificada(s) da relação especificada, que pode ser uma tabela ou uma visão materializada. Os índices são usados principalmente para melhorar o desempenho do banco de dados (embora o uso inadequado possa resultar em um desempenho mais lento).

Os campos-chave para o índice são especificados como nomes de coluna ou, como alternativa, expressões escritas entre parênteses. Podem ser especificados vários campos, se o método de índice oferecer suporte a índices com várias colunas.

Um campo de índice pode ser uma expressão calculada a partir dos valores de uma ou mais colunas da linha da tabela. Este recurso pode ser usado para obter acesso rápido aos dados com base em alguma transformação dos dados básicos. Por exemplo, um índice computado em upper(col) permite que a cláusula WHERE upper(col) = 'JIM' use um índice.

O PostgreSQL fornece os métodos de índice B-tree, hash, GiST, SP-GiST, GIN, e BRIN. Os usuários também podem definir seus próprios métodos de índice, mas isto é bastante complicado.

Quando a cláusula WHERE está presente, é criado um índice parcial. Um índice parcial é um índice contendo entradas apenas para uma parte da tabela, geralmente a parte que é mais útil para indexação do que o restante da tabela. Por exemplo, havendo uma tabela contendo pedidos faturados e não faturados, em que os pedidos não faturados ocupam uma pequena fração do total da tabela, e ainda assim esta é uma parte usada com frequência, pode-se melhorar o desempenho criando um índice apenas para esta parte. Outra aplicação possível é usar WHERE com UNIQUE, para impor a unicidade sobre um subconjunto da tabela. Veja Índices parciais para obter mais informações.

A expressão usada na cláusula WHERE pode se referir apenas às colunas da tabela subjacente, mas pode usar todas as colunas, não apenas as que estão sendo indexadas. No momento, também não são permitidas subconsultas e expressões de agregação na cláusula WHERE. As mesmas restrições se aplicam aos campos do índice que são expressões.

Todas as funções e operadores usados na definição do índice devem ser “imutáveis”, ou seja, seus resultados devem depender apenas de seus argumentos, e nunca de qualquer influência externa (como o conteúdo de outra tabela, ou a hora corrente). Esta restrição garante que o comportamento do índice seja bem definido. Para usar uma função definida pelo usuário em uma expressão de índice ou cláusula WHERE, esta função deve ter sido marcada como imutável ao ser criada.

Parâmetros

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

Notas

Veja Índices para obter informações sobre quando os índices podem ser usados, quando não são usados, e em quais situações específicas podem ser úteis.

No momento, apenas os métodos de índice B-tree, GiST, GIN e BRIN dão suporte a índices com chave de várias colunas. A existência de várias colunas-chave é independente de poderem ser adicionadas as colunas do INCLUDE ao índice. Os índices podem ter até 32 colunas, incluindo colunas do INCLUDE. (Este limite pode ser alterado ao construir o PostgreSQL.) No momento, apenas Árvore-B oferece suporte a índices de unicidade.

Pode ser especificada uma classe de operador com parâmetros opcionais para cada coluna de um índice. A classe de operador identifica os operadores a serem usados pelo índice dessa coluna. Por exemplo, um índice de Árvore-B em números inteiros de quatro bytes usaria a classe int4_ops; esta classe de operador inclui funções de comparação para inteiros de quatro bytes. Na prática, a classe de operador padrão para o tipo de dados da coluna é geralmente suficiente. O ponto principal de ter classes de operador é que, para alguns tipos de dados, pode haver mais de uma ordenação significativa. Por exemplo, pode-se querer ordenar o tipo de dados de número complexo por seu valor absoluto, ou pela sua parte real. Isto pode ser feito definindo duas classes de operador para o tipo de dados, e selecionando a classe adequada ao criar o índice. Mais informações sobre classes de operador pode ser encontradas em Classes e famílias de operador e Interligando extensões a índices.

Quando o comando CREATE INDEX é executado em uma tabela particionada, o comportamento padrão é percorrer a todas as partições para garantir que todas tenham índices correspondentes. Primeiro, cada partição é verificada para determinar se já existe um índice equivalente e, em caso afirmativo, este índice será anexado como índice de partição ao índice que está sendo criado, que se tornará seu índice pai. Se não existir nenhum índice correspondente, será criado um índice e anexado automaticamente; o nome do novo índice de cada partição será determinado como se nenhum nome de índice tivesse sido especificado no comando. Se for especificada a opção ONLY, nenhuma recursividade será feita, e o índice será marcado como inválido. (O comando ALTER INDEX ... ATTACH PARTITION marca o índice como válido, uma vez que todas as partições tenham adquirido índices correspondentes.) Note, no entanto, que qualquer partição criada no futuro usando o comando CREATE TABLE ... PARTITION OF terá automaticamente um índice correspondente, independentemente de ONLY ser especificado.

Para métodos de índice com suporte a varreduras ordenadas (no momento, apenas B-tree), podem ser especificadas as cláusulas opcionais ASC, DESC, NULLS FIRST, e/ou NULLS LAST para modificar a ordem de classificação do índice. Uma vez que um índice ordenado pode ser varrido para frente ou para trás, normalmente não é útil criar um índice DESC de coluna única — esta ordem de classificação já está disponível com um índice regular. O valor dessas opções é que índices de várias colunas podem ser criados para corresponder à ordem de classificação solicitada por uma consulta de ordem mista, como SELECT ... ORDER BY x ASC, y DESC. As opções NULLS são úteis se for necessário oferecer suporte ao comportamento “nulos são menores”, em vez do padrão “nulos são maiores”, em consultas que dependem de índices para evitar etapas de classificação.

O sistema coleta regularmente estatísticas em todas as colunas da tabela. Os índices sem expressão recém-criados podem usar imediatamente estas estatísticas para determinar a utilidade do índice. Para novos índices de expressão, é necessário executar o comando ANALYZE, ou aguardar para que Autovacuum analise a tabela e gere estatísticas para estes índices.

Enquanto CREATE INDEX está executando, o search_path foi temporariamente alterado para pg_catalog, pg_temp.

Para a maioria dos métodos de índice, a velocidade de criação de um índice depende da configuração do parâmetro maintenance_work_mem. Valores maiores reduzem o tempo necessário para a criação do índice, desde que não se torne maior do que a quantidade de memória realmente disponível, o que levaria a máquina a paginar.

O PostgreSQL pode construir índices enquanto aproveita várias CPUs para processar as linhas da tabela mais rapidamente. Este recurso é conhecido como construção de índice em paralelo. Para métodos de índice que suportam a construção de índices em paralelo (no momento, apenas B-tree, GIN e BRIN), o parâmetro maintenance_work_mem especifica a quantidade máxima de memória que pode ser usada por cada operação de construção do índice como um todo, independentemente de quantos processos trabalhadores foram iniciados. Geralmente, o modelo de custo determina automaticamente quantos processos trabalhadores devem ser solicitados, se houver.

Construções de índice em paralelo podem se beneficiar do aumento do parâmetro maintenance_work_mem, onde uma construção de índice serial equivalente terá pouco ou nenhum benefício. Note que o valor de maintenance_work_mem pode influenciar o número de processos trabalhadores solicitados, já que os trabalhadores em paralelo devem ter pelo menos um compartilhamento de 32MB do valor total de maintenance_work_mem. Também deve haver um compartilhamento 32 MB restante para o processo líder. Aumentar max_parallel_maintenance_workers pode permitir que mais trabalhadores sejam usados, reduzindo o tempo necessário para a criação do índice, desde que a construção do índice já não esteja presa à E/S. Obviamente, também deve haver capacidade de CPU suficiente que, de outra forma, ficaria ociosa.

Definir o valor de parallel_workers via ALTER TABLE controla diretamente quantos processos trabalhadores em paralelo serão solicitados por um comando CREATE INDEX na tabela. Isto ignora completamente o modelo de custo, evitando que maintenance_work_mem afete quantos trabalhadores paralelos são solicitados. Definir parallel_workers como zero por meio do comando ALTER TABLE, desativa as construções de índice em paralelo na tabela em todos os casos.

Embora o comando CREATE INDEX com a opção CONCURRENTLY dê suporte a construções em paralelo sem restrições especiais, apenas a primeira varredura de tabela é realmente executada em paralelo.

Deve ser usado o comando DROP INDEX para remover um índice.

Como qualquer transação de execução longa, o comando CREATE INDEX em uma tabela pode afetar quais tuplas podem ser removidas por um comando VACUUM simultâneo em qualquer outra tabela.

Versões anteriores do PostgreSQL também tinham o método de índice R-tree. Este método foi removido, porque não tinha vantagens significativas sobre o método GiST. Se for especificado USING rtree, o comando CREATE INDEX irá interpretá-lo como USING gist, para simplificar a conversão de bancos de dados antigos para GiST.

Cada processo servidor executando o comando CREATE INDEX irá relatar o seu progresso na visão pg_stat_progress_create_index. Veja Relatório de progresso do CREATE INDEX para obter detalhes.

Exemplos

Para criar um índice de unicidade Árvore-B na coluna title da tabela films:

CREATE UNIQUE INDEX title_idx ON films (title);

Para criar um índice de unicidade Árvore-B na coluna title, com as colunas incluídas director e rating, na tabela films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

Para criar um índice Árvore-B com deduplicação desativada:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

Para criar um índice na expressão lower(title), permitindo procuras eficientes que não diferenciam letras maiúsculas de minúsculas:

CREATE INDEX ON films ((lower(title)));

(Neste exemplo, optou-se por omitir o nome do índice, então o sistema irá escolher um nome, normalmente films_lower_idx.)

Para criar um índice com ordenação diferente do padrão:

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

Para criar um índice com ordem de classificação diferente do padrão para nulos:

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

Para criar um índice com fator de preenchimento diferente do padrão:

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

Para criar um índice GIN com atualizações rápidas desativadas:

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

Para criar um índice na coluna code da tabela films, e fazer com que o índice resida no espaço de tabelas indexspace:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

Para criar um índice GiST em atributo de ponto, para ser possível usar operadores de caixa com eficiência no resultado da função de conversão:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

Para criar um índice sem bloquear escrita na tabela:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Conformidade

O comando CREATE INDEX é uma extensão do PostgreSQL à linguagem SQL. Não existe provisionamento para índices no padrão SQL.

Consulta

As informações sobre cada índice no banco de dados podem ser consultadas através da visão do sistema pg_indexes.

Veja também