Indexação de arrays e operadores (`@>`, `<@`, `&&`) para busca rápida

Arrays no PostgreSQL permitem guardar vários valores numa única célula da tabela. Isso é super prático quando tu precisa agrupar dados relacionados, tipo uma lista de tags pra um artigo ou as categorias de um produto. Só que, quando a gente começa a buscar, filtrar ou cruzar arrays, a performance pode cair feio. É aí que a indexação de arrays salva a pátria. Os índices aceleram operações como:

• verificar se um array contém um elemento específico,
• buscar arrays que contenham certos elementos,
• verificar se arrays têm interseção.

Operadores pra trabalhar com arrays

Antes de cair de cabeça na criação de índices, bora entender os operadores principais pra mexer com arrays:

@> (contains) — checa se o array contém todos os elementos de outro array.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Aqui a gente procura cursos que tenham a tag "SQL".

<@ (is contained by) — checa se um array está contido em outro.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Aqui a gente procura cursos cujas tags incluem todos os elementos do array ARRAY['PostgreSQL', 'SQL'].

&& (overlap) — checa se existe interseção entre arrays.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Essa query acha cursos que têm pelo menos uma das tags "NoSQL" ou "Big Data".

Como a indexação ajuda?

Imagina que tu tem uma tabela courses com milhões de registros, e tu faz uma query usando um desses operadores aí de cima. Sem índice, o PostgreSQL vai ter que checar cada linha uma por uma — um processo que pode demorar uma eternidade (especialmente se tu tem a paciência de um dev esperando a compilação terminar).

Com índices, tu foge desse sofrimento. O PostgreSQL oferece dois tipos de índices que são massa pra arrays:

1. GIN (Generalized Inverted Index) — melhor escolha pra arrays.
2. BTREE — usado pra comparar arrays inteiros.

Exemplo: Criando índice pra arrays

Bora criar uma tabelinha com arrays pra testar tudo na prática.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Vamos adicionar uns registros:

INSERT INTO courses (name, tags)
VALUES
    ('Fundamentos de SQL', ARRAY['SQL', 'PostgreSQL', 'Bancos de dados']),
    ('Trabalhando com Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Desenvolvimento em Python', ARRAY['Python', 'Web', 'Dados']),
    ('Curso de PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Olha como fica a tabela:

Sem índice: busca lenta

Agora imagina que a gente quer achar todos os cursos que têm a tag SQL.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Essa query até roda, mas se tiver muita linha, vai ficar super lenta. O PostgreSQL vai fazer o famoso scan sequencial (Sequential Scan), ou seja, vai olhar linha por linha.

Exemplo de resultado:

Criando índice GIN

Pra acelerar a busca, bora criar um índice do tipo GIN:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Agora vamos rodar a mesma query:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Agora o PostgreSQL vai usar o índice GIN que a gente criou, e o tempo de execução cai muito.

Se antes era scan sequencial (Seq Scan), agora no plano de execução tu vai ver Bitmap Index Scan:

Como os operadores funcionam com índices?

Exemplo 1: Operador @>

Query:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

O índice GIN é perfeito pra esse operador. O Postgres acha rapidinho quais linhas têm o elemento e retorna o resultado.

Resultado:

Exemplo 2: Operador &&

Query:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Esse operador checa interseção de arrays: ele retorna linhas onde o array tags tem pelo menos um elemento do array passado.

O índice GIN faz a mágica de novo — a busca é rápida, mesmo com muitos dados.

Resultado:

Indexação e otimização

Quando for trabalhar com arrays, segue essas dicas:

1. Usa índices GIN pra buscar dentro de arrays. Eles são muito mais rápidos que scan sequencial.
2. Só cria índice nos campos que tu realmente usa nas queries. Índice ocupa espaço e deixa inserts mais lentos, então não sai indexando tudo.
3. Perfilha tuas queries com EXPLAIN e EXPLAIN ANALYZE pra ver se o índice tá sendo usado mesmo.

Exemplos: criando índices pra arrays

Bora ver como criar índices pra tipos específicos de operação com arrays e por que isso é útil na prática.

Índice pro operador @>

Imagina que a gente já tem essa tabela courses:

Pra acelerar queries com o operador @> (array contém elemento), cria um índice GIN:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Agora roda a query:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Resultado:

Índice pros operadores @>, <@ e &&

A tabela é a mesma do exemplo anterior.

Como os operadores @>, <@ e && funcionam bem com índices GIN, tu pode criar um índice só que acelera queries com qualquer um desses operadores:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Exemplos de queries e resultados:

• @> — checa se o array contém os elementos:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — checa se o array está contido em outro array:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — checa interseção de arrays:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Bora tentar algo mais difícil

Vamos fazer uma query que retorna cursos onde as tags têm pelo menos uma interseção com a lista ['Python', 'SQL', 'NoSQL']:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Saída:

Com índice GIN essa query roda na hora, mesmo se a tabela tiver milhões de linhas.

Erros comuns ao trabalhar com arrays

Índice não é usado: se no output do EXPLAIN tu vê Seq Scan, confere se o índice foi criado e se o operador que tu tá usando realmente suporta indexação.

Uso raro do array: se o campo de array quase nunca aparece nas queries ou é atualizado com frequência, o índice pode só ocupar espaço e não ajudar em nada.

Índices demais: índices ocupam espaço em disco e deixam inserts mais lentos, então só cria os que tu realmente vai usar nas queries.

Agora tu tem todas as ferramentas pra trabalhar de boa com arrays no PostgreSQL — acelera tuas queries usando os operadores @>, <@, && e índices GIN. Testa aí nos teus dados sem medo!