Menu

CREATE INDEX no PostgreSQL: tipos de índice e exemplos

O CREATE INDEX cria por padrão um índice B-tree para o PostgreSQL achar linhas sem ler a tabela inteira. Outros tipos (GIN, GiST, BRIN, hash) cobrem JSONB, arrays, busca de texto completo, intervalos e tabelas muito grandes.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

CREATE INDEX name ON table (column); cria um índice que permite ao PostgreSQL achar as linhas que batem sem ler a tabela inteira. O tipo padrão é B-tree, que atende =, <, >, BETWEEN, IN, IS NULL e ORDER BY. Escolha outro tipo com USING: gin, gist, spgist, brin ou hash.

A primeira linha do plano diz Index Scan using users_email_idx on users: o PostgreSQL procura o valor no índice e lê só a linha que bate. Remova a linha do CREATE INDEX e rode de novo para ver Seq Scan on users, que lê as 10.000 linhas.

Numa tabela com poucas dezenas de linhas, o planejador muitas vezes escolhe uma leitura sequencial mesmo com um índice: ler meia dúzia de páginas sai mais barato. EXPLAIN ANALYZE explica como ler esses planos.

Escolher um tipo de índice

TipoUse para
B-tree (padrão)igualdade, intervalos, ordenação, LIKE 'prefix%' (com a collation C ou text_pattern_ops)
GINvalores com muitas partes: jsonb (@>, ?), arrays (@>, &&), texto completo tsvector
GiSTgeometria, intervalos (&&, sobreposição), texto completo, vizinho mais próximo ORDER BY ... <->
SP-GiSTdados que se dividem em regiões sem sobreposição: pontos, endereços IP, prefixos de texto
BRINtabelas muito grandes cujos valores seguem a ordem física, como um created_at que só cresce
Hashsó igualdade; um B-tree faz o mesmo e mais, então raramente é escolhido

Um índice GIN numa coluna jsonb faz as consultas de contenção usarem o índice:

O plano mostra um Bitmap Index Scan no índice GIN, alimentando um Bitmap Heap Scan que lê as linhas que batem. A página de JSONB explica os operadores.

Indexar várias colunas

Um índice B-tree com várias colunas ordena pela primeira coluna e depois pela segunda dentro de cada valor da primeira. Ele atende consultas que filtram pela coluna inicial, ou pela inicial e pelas seguintes. Um filtro só na segunda coluna ganha pouco com ele, então coloque primeiro a coluna que você sempre filtra com =.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11+) guarda colunas extras no índice sem ordenar por elas: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Uma consulta que lê só essas colunas pode então ser respondida só pelo índice, um Index Only Scan.

Índices únicos e parciais: impor uma regra a algumas linhas

CREATE UNIQUE INDEX rejeita valores duplicados. Um índice parcial acrescenta um WHERE e indexa só as linhas que o atendem. Ele é menor e pode fazer uma regra de unicidade valer só para um subconjunto das linhas:

As duas linhas são aceitas porque só uma delas não está excluída. Uma terceira linha ativa com o mesmo email falha com duplicate key value violates unique constraint "accounts_email_live". Uma consulta usa um índice parcial só quando o WHERE dela implica a condição do índice, aqui WHERE NOT deleted.

Indexar uma expressão

Um índice pode guardar o resultado de uma expressão. A consulta precisa repetir a mesma expressão para usá-lo, e um índice em email não ajuda WHERE lower(email) = ...:

O plano usa users_email_lower_idx porque o WHERE repete lower(email) exatamente. A expressão precisa ser imutável, então now() ou uma função que depende de uma configuração são recusadas.

Listar, criar e excluir índices num banco em produção

pg_indexes lista todos os índices com a instrução que os recriaria:

A chave primária e a restrição UNIQUE criaram seus próprios índices, users_pkey e users_email_key. No psql, \d users mostra a mesma lista abaixo das colunas da tabela.

Numa tabela que recebe escritas, crie e exclua índices com CONCURRENTLY para não bloquear as escritas. Essas formas não podem rodar dentro de um bloco de transação. Uma criação concorrente que falha deixa um índice INVALID, que você precisa excluir e criar de novo:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

Perguntas frequentes

O PostgreSQL cria índices automaticamente?

Só para restrições PRIMARY KEY, UNIQUE e de exclusão. Uma coluna de chave estrangeira não ganha índice, então crie um você mesmo quando fizer joins por ela ou apagar linhas da tabela referenciada.

Por que o PostgreSQL não usa meu índice?

Motivos comuns: a tabela é pequena, então ler tudo é mais barato; a consulta retorna boa parte das linhas; o WHERE envolve a coluna numa função ou conversão que o índice não tem; ou as estatísticas estão defasadas (rode ANALYZE). O EXPLAIN mostra qual plano foi escolhido.

Quantos índices são demais?

Não existe um número fixo. Cada índice acelera algumas leituras e deixa mais lento todo INSERT e a maioria dos UPDATEs da tabela, além de ocupar disco. pg_stat_user_indexes.idx_scan mostra quantas vezes cada índice é usado, então você pode excluir os que continuam em zero.

O CREATE INDEX trava a tabela?

Um CREATE INDEX comum bloqueia INSERT, UPDATE e DELETE na tabela até terminar, mas as leituras continuam. CREATE INDEX CONCURRENTLY permite escritas durante a criação, demora mais e não pode rodar dentro de um bloco de transação.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar