Indexação de arrays: criando índices GIN e BTREE

Imagina que tu tem uma tabela com milhões de registros, e uma das colunas guarda arrays. Por exemplo, temos a tabela products, e cada produto pode pertencer a várias categorias:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- Array de strings pra guardar as categorias do produto
);

Vamos supor que tu quer achar todos os produtos que pertencem à categoria electronics. Usar só o operador @> pra buscar pode acabar fazendo um scan completo da tabela:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

Scan completo (Seq Scan) — é lento. Principalmente se a tabela for gigante. Os índices entram em cena pra transformar isso numa busca bem mais rápida.

Tipos de índices pra arrays

O PostgreSQL suporta dois tipos principais de índices que tu pode usar com arrays:

1. GIN (Generalized Inverted Index) — perfeito pra buscar rápido elementos dentro do array ou checar interseções.
2. BTREE (Binary Tree) — serve pra outras operações, tipo comparar arrays exatamente.

Bora ver cada um deles com mais detalhes.

1. Índice GIN: velocidade da luz

GIN (Generalized Inverted Index) é um índice que manda muito bem com operadores tipo:

• @> (array contém elemento ou outro array),
• <@ (array está contido em outro array),
• && (arrays têm interseção).

Olha como criar um índice GIN pra nossa coluna categories:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Depois de criar o índice, as queries vão rodar bem mais rápido. Por exemplo, essa query:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

vai usar teu índice GIN.

Curiosidade: O índice GIN funciona tipo uma lista invertida — ele guarda quais elementos (tipo strings) estão em quais registros. É tipo aquele índice no final do livro que te mostra em que página tá cada assunto. Bem prático, né?

2. Índice BTREE: quando a ordem importa

BTREE (Binary Tree) é o índice padrão usado na maioria dos bancos de dados. Ele é bom pra operações que precisam comparar arrays exatamente, tipo:

• Checar se os arrays são iguais =,
• Comparar arrays pela ordem dos elementos (>, <).

Pra criar um índice BTREE pra array, faz assim:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Exemplo de query que pode usar o índice BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Mas fica ligado: índices BTREE não servem pra operadores tipo @> ou <@. Pra esses, usa GIN mesmo.

Exemplos de uso dos índices

Agora bora juntar teoria e prática e ver uns exemplos.

1. Buscando interseção de arrays

Vamos supor que tu quer achar todos os produtos ligados às categorias electronics e smartphones, usando o operador && (interseção de arrays):

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

Pra isso, o índice GIN que tu já criou é perfeito:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

Com esse índice, a query roda bem mais rápido porque usa a lista invertida.

2. Comparando arrays por igualdade

Se tu precisa achar produtos que pertencem somente às categorias electronics e gadgets (nessa ordem), aí é melhor usar o índice BTREE:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

Cria o índice assim:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

Performance dos índices

Índices ajudam a acelerar as queries, mas também têm seu lado B. Por exemplo:

• Criar o índice leva tempo e consome recursos. Se tua tabela for enorme, criar o índice pode demorar um bocado.
• Atualizar a tabela. Toda vez que tu insere novas linhas ou atualiza dados, os índices também são atualizados. Isso pode deixar INSERT e UPDATE mais lentos.

Mas, na real, na maioria dos casos o ganho de performance nas queries compensa esses custos.

Como escolher: GIN ou BTREE?

Aqui vai uma tabelinha pra te ajudar a escolher o índice certo pra cada situação: