Índices em PostgreSQL: B-tree, GIN e quando usar

1. Visão geral

PostgreSQL suporta vários tipos de índices, mas B-tree e GIN são os dois tipos que você encontra mais frequentemente no trabalho do dia a dia. Escolher o tipo de índice errado faz a query ficar lenta ou o índice consome espaço sem ajudar em nada.

Este artigo explica como B-tree e GIN funcionam em um nível suficiente para usar, como criar índices corretamente, como ler EXPLAIN para saber se o índice está sendo usado, e um checklist para escolher o tipo de índice apropriado. Depois de ler, você vai ter confiança para adicionar índices às tabelas Order, Product sem precisar adivinhar.

2. Índice B-tree: o padrão e mais comum

2.1. Como o B-tree funciona

B-tree (árvore balanceada) armazena valores em ordem classificada, permitindo busca, comparação (<, >, BETWEEN) e ordenação (ORDER BY) muito rápidas — complexidade O(log n). Este é o tipo de índice padrão quando você executa CREATE INDEX sem especificar o método.

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

A linha CREATE INDEX cria um B-tree na coluna customer_id. Quando a query WHERE customer_id = 123 é executada, PostgreSQL percorre a árvore em vez de fazer uma varredura completa da tabela (sequential scan).

2.2. Verificar se o índice está sendo usado

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan significa que o planner escolheu usar o índice. Se você vê Seq Scan mesmo tendo um índice, geralmente é porque a tabela é muito pequena (o planner acha que varrer a tabela é mais barato) ou as estatísticas estão desatualizadas — execute ANALYZE customer_order; para atualizar.

2.3. Índice composto e ordem das colunas

B-tree suporta múltiplas colunas, mas a ordem das colunas determina a eficiência.

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- Usa o índice completamente
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- Usa apenas a parte customer_id do índice
SELECT * FROM customer_order WHERE customer_id = 123;

-- NÃO pode usar este índice (falta customer_id)
SELECT * FROM customer_order WHERE status = 'PAID';

Princípio: um índice composto é como um livro de endereços classificado por (customer_id, status) — você pode buscar por customer_id primeiro, mas não pode buscar diretamente apenas por status.

Nota: Coloque a coluna com alta seletividade (alta selectivity, muitos valores diferentes) em primeiro lugar se as colunas são usadas independentemente na query. Se você sempre faz query com ambas as colunas ao mesmo tempo, coloque a coluna com filtro mais forte primeiro para a árvore estreitar o intervalo rapidamente.

3. Índice GIN: para dados “múltiplos valores em uma linha”

3.1. Quando B-tree não é suficiente

B-tree não é eficiente com tipos de dados onde uma linha contém múltiplos “elementos” que precisam ser pesquisados independentemente: array (array), JSON (jsonb), busca full-text (tsvector). GIN (Índice Invertido Generalizado) foi criado para este caso — ele indexa cada elemento individualmente, não o valor completo.

3.2. GIN para JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes) cria um índice sobre toda a coluna JSONB. O operador @> (“contains” / “contém”) é o que o GIN otimiza melhor — ele verifica se o JSON à esquerda contém a estrutura à direita, sem precisar fazer parse do documento inteiro novamente.

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN para busca full-text

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

A coluna search_vector é uma generated column que gera automaticamente tsvector a partir de name. Um índice GIN nesta coluna ajuda @@ (match) a rodar rápido com dados de texto grandes, em vez de LIKE '%laptop%' que precisaria varrer toda a tabela.

3.4. GIN para array

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

Assim como JSONB, GIN indexa cada elemento na matriz tag_list, então a consulta “artigos que contêm a tag X” é muito mais rápida do que B-tree com tabelas grandes.

4. Comparação e custo

Critério	B-tree	GIN
Dados apropriados	escalar: números, strings, datas	array, jsonb, tsvector
Operadores suportados	=, <, >, BETWEEN, ORDER BY	@>, ?, @@, &&
Velocidade de escrita (INSERT/UPDATE)	rápido	mais lento, especialmente UPDATE de JSONB grande
Tamanho do índice	menor	maior (armazena cada elemento)
Velocidade de construção do índice	rápido	mais lento em tabelas grandes

GIN é mais caro em termos de custo de escrita porque cada INSERT/UPDATE precisa atualizar múltiplas entradas (uma para cada elemento no JSON/array), em vez de uma única entrada como B-tree.

-- Verificar o tamanho real do índice
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. Testar para confirmar que o índice está sendo usado (integração com Spring Boot)

Ao trabalhar com Spring Data JPA, você geralmente quer garantir que o índice está funcionando corretamente antes de fazer deploy. Use Testcontainers para testar em um PostgreSQL real.

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

Este teste executa EXPLAIN (sem ANALYZE), combina as linhas de resultado em uma string e verifica se o plano menciona o nome do índice esperado. Esta é a forma de confirmar que o índice realmente participa do plano da query, e não apenas “existe no schema”.

Nota: EXPLAIN não executa a query de verdade, apenas consulta o planner. Se você quer saber o tempo de execução real, use EXPLAIN ANALYZE — mas lembre que ele executa a instrução, evite usar com DELETE/UPDATE a menos que você o envolva em uma transação com rollback.

6. Checklist para escolher o tipo de índice

• Query filtrando por coluna escalar (números, strings, datas) com =, <, >, ou precisando de ORDER BY → B-tree.
• Query filtrando múltiplas colunas ao mesmo tempo com “AND” → B-tree composto, coluna com alta seletividade ou coluna usada independentemente em primeiro lugar.
• Query em coluna JSONB usando @>, ?, ?&, ?| → GIN.
• Busca full-text (@@ com tsvector) → GIN.
• Query em array usando @>, &&, <@ → GIN.
• Tabela com muitas escritas, poucas leituras, dados JSONB mudando frequentemente → considere não indexar toda a coluna com GIN, apenas indexar sobre a expressão (expression index) necessária.

-- Expression index: apenas indexa uma chave específica no JSONB, mais leve do que GIN em toda a coluna
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

Este expression index é na verdade um B-tree no resultado de attributes ->> 'brand' (valor de texto), apropriado quando você sempre faz query exatamente pela mesma chave fixa, sem precisar da flexibilidade do GIN.

7. Erros comuns

• Criar índice em coluna com baixa cardinalidade (por exemplo, status com apenas 3 valores em uma tabela de milhões de linhas): o planner geralmente ignora o índice, escolhe Seq Scan porque é mais barato. Nem todo índice que existe é usado.
• Esquecer ANALYZE após inserir dados grandes: o planner fica baseado em estatísticas antigas, estima errado o número de linhas, escolhe um plano ruim.
• Usar GIN para coluna com poucas mudanças mas muitas pequenas escritas: o custo acumulado de atualizar o índice é maior do que o benefício da consulta — teste com EXPLAIN ANALYZE antes de decidir.
• Índice B-tree composto com ordem de coluna errada: colocar coluna pouco usada em primeiro lugar torna o índice praticamente inútil para queries reais.
• Criar muitos índices em uma tabela: cada INSERT/UPDATE precisa atualizar todos os índices relacionados, tornando o write path lento enquanto ninguém usa todos esses índices.

8. Conclusão

B-tree é a escolha padrão, apropriado para a maioria das condições de filtro e ordenação em dados escalares. GIN é dedicado a dados “múltiplos valores” como JSONB, array, full-text — onde B-tree não pode ser eficiente.

Antes de adicionar um índice, sempre verifique com EXPLAIN ANALYZE se o planner realmente o usa, e meça o custo de escrita se a tabela tem alta frequência de INSERT/UPDATE. Índice não é “para colocar só por segurança” — cada índice é um trade-off entre velocidade de leitura e velocidade de escrita.