Noções Básicas de Design de Índices

Índices aceleram buscas que correspondem a um prefixo de chave da esquerda para a direita. Projete para formatos de consulta reais: colunas WHERE, JOIN e ORDER BY que aparecem juntas em planos do PostgreSQL 18.4.

Receita

Cartão de referência rápida - pronto para copiar e colar.

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 50;

Quando usar isso: Filtros ou junções frequentes mostram varreduras sequenciais (seq scans) em tabelas grandes com seletividade comprovável.

Exemplo de Trabalho

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total numeric(12,2) NOT NULL
);
 
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
 
INSERT INTO orders (customer_id, status, created_at, total)
SELECT
  (random() * 5000)::bigint,
  CASE WHEN random() < 0.1 THEN 'shipped' ELSE 'pending' END,
  now() - (random() * interval '180 days'),
  (random() * 200)::numeric(12,2)
FROM generate_series(1, 300000);
 
ANALYZE orders;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25;

O que isso demonstra:

Índice composto correspondendo a filtro mais ordenação
LIMIT satisfeito sem nó de ordenação separado quando o plano é ideal
Prova via EXPLAIN após ANALYZE

Análise Detalhada

Como Funciona

B-tree é o padrão: igualdade em colunas principais, depois intervalo na próxima coluna.
Colunas INCLUDE permitem varreduras apenas de índice (index-only scans) para cargas de dados selecionadas.
Índices parciais WHERE reduzem o tamanho para subconjuntos estáveis (status = 'open').
Cada índice taxa INSERT/UPDATE/DELETE com WAL e sujeira de buffer.

Checklist de Design

Cláusula da Consulta	Dica de Índice
`WHERE a = ? AND b > ?`	`(a, b)`
`JOIN ON child.parent_id`	Índice em `parent_id`
`ORDER BY created_at DESC`	Corresponder à direção de ordenação no índice
Sinalizador de baixa seletividade	Índice parcial com predicado

Notas SQL

CREATE INDEX orders_open_customer_idx
ON orders (customer_id, created_at)
WHERE status = 'open';

Armadilhas

Indexar cada coluna de filtro separadamente - O planejador pode combinar bitmap de muitos índices fracos. Correção: Um composto intencional por consulta frequente.
Ordem incorreta das colunas - (created_at, customer_id) falha em WHERE customer_id = ?. Correção: Coluna de igualdade mais seletiva à esquerda.
Índices largos incluindo blobs JSON - Páginas infladas, vacuum lento. Correção: INCLUDE apenas escalares necessários.
Criar índice sem CONCURRENTLY em produção - Bloqueia escritas. Correção: CREATE INDEX CONCURRENTLY em migrações.
Pular ANALYZE após o índice - O planejador ignora o novo índice. Correção: Analisar a tabela antes da validação.

Alternativas

Alternativa	Usar Quando	Não Usar Quando
BRIN em séries temporais	Timestamps somente anexáveis	Buscas pontuais
GIN em chaves JSONB	Consultas de contenção	Igualdade escalar simples
Tabela de cache desnormalizada	Extremo desequilíbrio de leitura	Necessidades fortes de normalização

FAQs

Quantos índices por tabela?

Não há um máximo fixo; observe a taxa de gravação e a pressão do autovacuum. Audite índices não utilizados trimestralmente.

UNIQUE vs INDEX simples?

UNIQUE impõe a restrição e fornece caminho de busca. Use UNIQUE quando a regra de negócio exigir.

DESC no índice importa?

Sim, para varreduras de índice reversas que evitam ordenações em ORDER BY ... DESC.

Índice de cobertura?

INCLUDE armazena colunas extras em páginas folha sem ordenação da chave de busca.

Uso de índice Hash?

Raro no PostgreSQL moderno; B-tree lida com a maioria dos casos de igualdade.

Índice em expressão?

CREATE INDEX ON lower(email) quando as consultas usam a mesma expressão.

FK sem índice?

Junções de filhos e exclusões em cascata sofrem. Indexe colunas FK de filhos.

Chaves primárias UUID?

UUIDs aleatórios fragmentam índices; considere IDs ordenados por tempo para tabelas com muitas inserções.

Monitoramento de uso?

pg_stat_user_indexes.idx_scan em uma janela móvel.

Próximo?

Ordem de índice multicoluna para regras de prefixo esquerdo.

Relacionado

Ordem de Índice Multicoluna - Regras da coluna principal
Índices Duplicados e Redundantes - Auditoria de desperdício
Seq Scan vs Index Scan - Quando o índice vence
Índices B-tree - Detalhes do caminho de acesso

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+.

Noções Básicas de Design de Índices

Receita

Cartão de referência rápida - pronto para copiar e colar.

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 50;

Quando usar isso: Filtros ou junções frequentes mostram varreduras sequenciais (seq scans) em tabelas grandes com seletividade comprovável.

Exemplo de Trabalho

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total numeric(12,2) NOT NULL
);
 
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
 
INSERT INTO orders (customer_id, status, created_at, total)
SELECT
  (random() * 5000)::bigint,
  CASE WHEN random() < 0.1 THEN 'shipped' ELSE 'pending' END,
  now() - (random() * interval '180 days'),
  (random() * 200)::numeric(12,2)
FROM generate_series(1, 300000);
 
ANALYZE orders;
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25;

O que isso demonstra:

Índice composto correspondendo a filtro mais ordenação
LIMIT satisfeito sem nó de ordenação separado quando o plano é ideal
Prova via EXPLAIN após ANALYZE

Análise Detalhada

Como Funciona

B-tree é o padrão: igualdade em colunas principais, depois intervalo na próxima coluna.
Colunas INCLUDE permitem varreduras apenas de índice (index-only scans) para cargas de dados selecionadas.
Índices parciais WHERE reduzem o tamanho para subconjuntos estáveis (status = 'open').
Cada índice taxa INSERT/UPDATE/DELETE com WAL e sujeira de buffer.

Checklist de Design

Cláusula da Consulta	Dica de Índice
`WHERE a = ? AND b > ?`	`(a, b)`
`JOIN ON child.parent_id`	Índice em `parent_id`
`ORDER BY created_at DESC`	Corresponder à direção de ordenação no índice
Sinalizador de baixa seletividade	Índice parcial com predicado

Notas SQL

CREATE INDEX orders_open_customer_idx
ON orders (customer_id, created_at)
WHERE status = 'open';

Armadilhas

Indexar cada coluna de filtro separadamente - O planejador pode combinar bitmap de muitos índices fracos. Correção: Um composto intencional por consulta frequente.
Ordem incorreta das colunas - (created_at, customer_id) falha em WHERE customer_id = ?. Correção: Coluna de igualdade mais seletiva à esquerda.
Índices largos incluindo blobs JSON - Páginas infladas, vacuum lento. Correção: INCLUDE apenas escalares necessários.
Criar índice sem CONCURRENTLY em produção - Bloqueia escritas. Correção: CREATE INDEX CONCURRENTLY em migrações.
Pular ANALYZE após o índice - O planejador ignora o novo índice. Correção: Analisar a tabela antes da validação.

Alternativas

Alternativa	Usar Quando	Não Usar Quando
BRIN em séries temporais	Timestamps somente anexáveis	Buscas pontuais
GIN em chaves JSONB	Consultas de contenção	Igualdade escalar simples
Tabela de cache desnormalizada	Extremo desequilíbrio de leitura	Necessidades fortes de normalização

FAQs

Quantos índices por tabela?

Não há um máximo fixo; observe a taxa de gravação e a pressão do autovacuum. Audite índices não utilizados trimestralmente.

UNIQUE vs INDEX simples?

UNIQUE impõe a restrição e fornece caminho de busca. Use UNIQUE quando a regra de negócio exigir.

DESC no índice importa?

Sim, para varreduras de índice reversas que evitam ordenações em ORDER BY ... DESC.

Índice de cobertura?

INCLUDE armazena colunas extras em páginas folha sem ordenação da chave de busca.

Uso de índice Hash?

Raro no PostgreSQL moderno; B-tree lida com a maioria dos casos de igualdade.

Índice em expressão?

CREATE INDEX ON lower(email) quando as consultas usam a mesma expressão.

FK sem índice?

Junções de filhos e exclusões em cascata sofrem. Indexe colunas FK de filhos.

Chaves primárias UUID?

UUIDs aleatórios fragmentam índices; considere IDs ordenados por tempo para tabelas com muitas inserções.

Monitoramento de uso?

pg_stat_user_indexes.idx_scan em uma janela móvel.

Próximo?

Ordem de índice multicoluna para regras de prefixo esquerdo.

Relacionado

Ordem de Índice Multicoluna - Regras da coluna principal
Índices Duplicados e Redundantes - Auditoria de desperdício
Seq Scan vs Index Scan - Quando o índice vence
Índices B-tree - Detalhes do caminho de acesso

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+.

Noções Básicas de Design de Índices

Buscar na documentação

Noções Básicas de Design de Índices