Índices avançados: B-tree, BRIN, GIN, GiST, partial, covering

⏱ 14 min·⭐ 60 XP

Pré-requisitos (0/1)0%

⬜🗺️ Query planner: EXPLAIN ANALYZE ninja(Database Deep — Postgres Internals)

Recomendamos completar os pré-requisitos antes de seguir, mas nada te impede de continuar.

Tipos de índice

Tipo	Serve para	Não serve para
Árvore balanceada	Igualdade, faixa e ordenação	Busca por conteúdo dentro de texto ou JSON
Invertido generalizado	Vários valores por linha: texto, JSON, arranjo	Faixa e ordenação
Por bloco de intervalo	Coluna correlacionada com a ordem física — tempo, em tabela grande	Dado sem correlação física
Hash	Somente igualdade	Faixa, ordenação
Parcial	Indexar só a fatia consultada — pedidos abertos, por exemplo	Consulta que não repete o mesmo filtro
Por expressão	Filtro sobre função — texto em minúsculas, por exemplo	Se a consulta não usar a MESMA expressão

TipoÁrvore balanceada

Serve paraIgualdade, faixa e ordenação

Não serve paraBusca por conteúdo dentro de texto ou JSON

TipoInvertido generalizado

Serve paraVários valores por linha: texto, JSON, arranjo

Não serve paraFaixa e ordenação

TipoPor bloco de intervalo

Serve paraColuna correlacionada com a ordem física — tempo, em tabela grande

Não serve paraDado sem correlação física

TipoHash

Serve paraSomente igualdade

Não serve paraFaixa, ordenação

TipoParcial

Serve paraIndexar só a fatia consultada — pedidos abertos, por exemplo

Não serve paraConsulta que não repete o mesmo filtro

TipoPor expressão

Serve paraFiltro sobre função — texto em minúsculas, por exemplo

Não serve paraSe a consulta não usar a MESMA expressão

Tipo	Uso	Tamanho
B-tree	Equality, range, sort (default 90% casos)	Médio
BRIN	Tabelas grandes ordenadas (logs, timeseries)	Minúsculo
GIN	Array, jsonb, tsvector (full-text)	Grande
GiST	Geometric, range types, proximity	Médio
Hash	Equality only (raramente usado — B-tree cobre)	Pequeno
SP-GiST	Dados space-partitioning (IPs, phone numbers)	Variável

TipoB-tree

UsoEquality, range, sort (default 90% casos)

TamanhoMédio

TipoBRIN

UsoTabelas grandes ordenadas (logs, timeseries)

TamanhoMinúsculo

TipoGIN

UsoArray, jsonb, tsvector (full-text)

TamanhoGrande

TipoGiST

UsoGeometric, range types, proximity

TamanhoMédio

TipoHash

UsoEquality only (raramente usado — B-tree cobre)

TamanhoPequeno

TipoSP-GiST

UsoDados space-partitioning (IPs, phone numbers)

TamanhoVariável

Index tipos especiais

-- Partial index — só active users
CREATE INDEX idx_users_active
ON users (email) WHERE deleted_at IS NULL;

-- Expression index — lowercased email (case-insensitive search)
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));
-- Query precisa usar MESMA expression:
SELECT * FROM users WHERE LOWER(email) = 'a@b.co';

-- Covering index — evita heap fetch
CREATE INDEX idx_orders_user_covering
ON orders (user_id) INCLUDE (total, status);

-- Composite index — prefix rule: (a, b, c) serve (a), (a,b), (a,b,c) mas NÃO (b) ou (c)
CREATE INDEX idx_orders_uset ON orders (user_id, status, created_at);

-- JSONB GIN
CREATE INDEX idx_events_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"action": "login"}';

Quiz rápido

Quando um índice para consultas de contenção — como busca em texto ou em estruturas com muitos valores — é preferível ao índice de árvore balanceada?

Quando NÃO indexar

Tabela muito pequena (< 1k rows): Seq Scan > índice
Coluna com baixa cardinalidade (ex: boolean sem partial)
Coluna muito mutável (índice retarda writes)
Em prod sem dor — índice adicional custa storage + writes.

💡

Regra: meça com EXPLAIN ANALYZE ANTES e DEPOIS. Às vezes o índice criado não é usado pelo planner (estatísticas ruins, coluna low-cardinality). pg_stat_user_indexes mostra uso real.

Perguntas frequentes

❓ Quando usar índice de faixa em bloco em vez de árvore?

Em tabela muito grande com dado naturalmente ordenado pela coluna — série temporal por data de inserção. Ele guarda resumo por bloco, é minúsculo comparado à árvore e responde bem a filtro de faixa. Perde em busca pontual e em dado desordenado.

❓ Para que serve índice invertido generalizado?

Para coluna com múltiplos valores por linha: texto para busca textual, documento JSON, arranjo. Ele indexa cada elemento apontando para as linhas. O preço é escrita mais caro e índice maior — e é por isso que ele não é o padrão.

❓ Índice parcial vale a pena?

Vale muito quando a consulta filtra sempre pelo mesmo subconjunto — pedidos pendentes, registros ativos. O índice fica pequeno, cabe em memória e a escrita nas linhas fora do filtro não o toca. É a otimização com melhor relação de esforço e retorno em tabela grande com consulta seletiva.

Fixando

Quiz rápido

Para que serve um índice parcial?

Quiz rápido

Quando NÃO criar um índice?

Terminou de ler?

Marcar como concluído registra o XP, mantém sua sequência e coloca 3 cartas deste módulo na fila de revisão espaçada.

Próximos passos sugeridos

🧹

Vacuum, autovacuum e bloat: causa #1 de DB morrendo

Database Deep — Postgres Internals · 13 min · +55 XP

→

Temas deste módulo

Dados e engenharia de dados

Discussão

Carregando comentários…

Índices avançados: B-tree, BRIN, GIN, GiST, partial, covering

⏱ 14 min·⭐ 60 XP

Pré-requisitos (0/1)0%

⬜🗺️ Query planner: EXPLAIN ANALYZE ninja(Database Deep — Postgres Internals)

Recomendamos completar os pré-requisitos antes de seguir, mas nada te impede de continuar.

Tipos de índice

Tipo	Serve para	Não serve para
Árvore balanceada	Igualdade, faixa e ordenação	Busca por conteúdo dentro de texto ou JSON
Invertido generalizado	Vários valores por linha: texto, JSON, arranjo	Faixa e ordenação
Por bloco de intervalo	Coluna correlacionada com a ordem física — tempo, em tabela grande	Dado sem correlação física
Hash	Somente igualdade	Faixa, ordenação
Parcial	Indexar só a fatia consultada — pedidos abertos, por exemplo	Consulta que não repete o mesmo filtro
Por expressão	Filtro sobre função — texto em minúsculas, por exemplo	Se a consulta não usar a MESMA expressão

TipoÁrvore balanceada

Serve paraIgualdade, faixa e ordenação

Não serve paraBusca por conteúdo dentro de texto ou JSON

TipoInvertido generalizado

Serve paraVários valores por linha: texto, JSON, arranjo

Não serve paraFaixa e ordenação

TipoPor bloco de intervalo

Serve paraColuna correlacionada com a ordem física — tempo, em tabela grande

Não serve paraDado sem correlação física

TipoHash

Serve paraSomente igualdade

Não serve paraFaixa, ordenação

TipoParcial

Serve paraIndexar só a fatia consultada — pedidos abertos, por exemplo

Não serve paraConsulta que não repete o mesmo filtro

TipoPor expressão

Serve paraFiltro sobre função — texto em minúsculas, por exemplo

Não serve paraSe a consulta não usar a MESMA expressão

Tipo	Uso	Tamanho
B-tree	Equality, range, sort (default 90% casos)	Médio
BRIN	Tabelas grandes ordenadas (logs, timeseries)	Minúsculo
GIN	Array, jsonb, tsvector (full-text)	Grande
GiST	Geometric, range types, proximity	Médio
Hash	Equality only (raramente usado — B-tree cobre)	Pequeno
SP-GiST	Dados space-partitioning (IPs, phone numbers)	Variável

TipoB-tree

UsoEquality, range, sort (default 90% casos)

TamanhoMédio

TipoBRIN

UsoTabelas grandes ordenadas (logs, timeseries)

TamanhoMinúsculo

TipoGIN

UsoArray, jsonb, tsvector (full-text)

TamanhoGrande

TipoGiST

UsoGeometric, range types, proximity

TamanhoMédio

TipoHash

UsoEquality only (raramente usado — B-tree cobre)

TamanhoPequeno

TipoSP-GiST

UsoDados space-partitioning (IPs, phone numbers)

TamanhoVariável

Index tipos especiais

-- Partial index — só active users
CREATE INDEX idx_users_active
ON users (email) WHERE deleted_at IS NULL;

-- Expression index — lowercased email (case-insensitive search)
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));
-- Query precisa usar MESMA expression:
SELECT * FROM users WHERE LOWER(email) = 'a@b.co';

-- Covering index — evita heap fetch
CREATE INDEX idx_orders_user_covering
ON orders (user_id) INCLUDE (total, status);

-- Composite index — prefix rule: (a, b, c) serve (a), (a,b), (a,b,c) mas NÃO (b) ou (c)
CREATE INDEX idx_orders_uset ON orders (user_id, status, created_at);

-- JSONB GIN
CREATE INDEX idx_events_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"action": "login"}';

Quiz rápido

Quando um índice para consultas de contenção — como busca em texto ou em estruturas com muitos valores — é preferível ao índice de árvore balanceada?

Quando NÃO indexar

Tabela muito pequena (< 1k rows): Seq Scan > índice
Coluna com baixa cardinalidade (ex: boolean sem partial)
Coluna muito mutável (índice retarda writes)
Em prod sem dor — índice adicional custa storage + writes.

💡

Regra: meça com EXPLAIN ANALYZE ANTES e DEPOIS. Às vezes o índice criado não é usado pelo planner (estatísticas ruins, coluna low-cardinality). pg_stat_user_indexes mostra uso real.

Perguntas frequentes

❓ Quando usar índice de faixa em bloco em vez de árvore?

❓ Para que serve índice invertido generalizado?

❓ Índice parcial vale a pena?

Fixando

Quiz rápido

Para que serve um índice parcial?

Quiz rápido

Quando NÃO criar um índice?

Terminou de ler?

Marcar como concluído registra o XP, mantém sua sequência e coloca 3 cartas deste módulo na fila de revisão espaçada.

Próximos passos sugeridos

🧹

Vacuum, autovacuum e bloat: causa #1 de DB morrendo

Database Deep — Postgres Internals · 13 min · +55 XP

→

Temas deste módulo

Dados e engenharia de dados

Discussão

Carregando comentários…