Noções Básicas de Índices

9 exemplos para você começar com índices do PostgreSQL - 6 básicos e 3 intermediários.

Pré-requisitos

Tabela com milhares de linhas para uma saída EXPLAIN significativa.

# Desenvolvimento local com Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Exemplos Básicos

1. Criar Índice B-tree

Método de acesso padrão para igualdade e intervalo.

CREATE INDEX customers_email_idx ON app.customers (email);
EXPLAIN SELECT * FROM app.customers WHERE email = 'ada@example.com';

• O planejador pode escolher Index Scan ou Bitmap Index Scan
• Consultas de busca única geralmente usam Index Scan
• Verifique com EXPLAIN ANALYZE

2. Ordem de Índice Composto

A coluna principal deve corresponder ao predicado.

CREATE INDEX orders_account_created_idx ON app.orders (account_id, created_at DESC);

• Consultas (account_id) usam o índice
• (created_at) sozinho pode não usar
• Corresponda à ordem de classificação quando possível

3. Custo de Escrita do Índice

Cada índice é atualizado a cada alteração de linha.

INSERT INTO app.customers (email) VALUES ('new@example.com');
-- atualiza customers_email_idx mais o PK

• Mais índices tornam as escritas mais lentas
• Remova índices não utilizados encontrados em pg_stat_user_indexes
• Meça idx_scan antes de remover

4. Índice Único

Impõe a exclusividade e acelera a busca.

CREATE UNIQUE INDEX customers_email_unique ON app.customers (lower(email));

• Índices de expressão impõem exclusividade sem distinção entre maiúsculas e minúsculas
• Conflitos geram unique_violation
• Prefira CONSTRAINT USING INDEX quando nomeado

5. Listar Índices em uma Tabela

Inspeção do catálogo.

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'app' AND tablename = 'customers';

• indexdef mostra expressões e WHERE
• Compare com os arquivos de migração
• Observe índices duplicados sobrepostos

6. Quando o Seq Scan é Vantajoso

Tabelas pequenas ou uma fração grande das linhas.

EXPLAIN ANALYZE SELECT * FROM app.customers;

• O planejador escolhe Seq Scan quando é mais barato
• Forçar índices é raro
• O crescimento dos dados altera a escolha

Exemplos Intermediários

7. Construção de Índice Concorrente

Evite bloqueios de escrita longos em produção.

CREATE INDEX CONCURRENTLY customers_created_idx
  ON app.customers (created_at);

• Não pode ser executado dentro de um bloco de transação
• Uma construção falha pode deixar um índice INVÁLIDO
• REINDEX CONCURRENTLY para reconstruções

8. Colunas INCLUDE de Cobertura

Projeções amigáveis para index-only scans.

CREATE INDEX orders_cover_idx ON app.orders (account_id) INCLUDE (total);

• Colunas INCLUDE não estão na chave de busca
• Ajuda em index-only scans quando o visibility map está atualizado
• Veja o artigo sobre index-only scans

9. Monitorar o Uso do Índice

pg_stat_user_indexes.

SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan;

• idx_scan = 0 ao longo de semanas sugere um candidato à remoção
• Reinicie as estatísticas apenas deliberadamente
• Combine com a revisão da carga de trabalho de consulta

Versões da Pilha: Esta página foi escrita para PostgreSQL 18.4 (estável 18, manutenção 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x e PostGIS 3.5+.