Indexando JSONB com GIN: jsonb_ops versus jsonb_path_ops

Uma coluna jsonb e comoda ate o momento em que voce precisa filtrar por ela em escala: sem indice cada consulta vira um scan sequencial que faz parse do JSON em tempo de execucao. Um indice GIN resolve isso, mas ele vem com duas classes de operadores que se comportam de formas diferentes, e a escolha afeta tanto a velocidade quanto o tamanho.

Nao escolha o indice "mais poderoso" so por garantia. O GIN acelera as leituras, mas voce paga em tamanho, insercoes mais lentas e manutencao periodica. Entao defina primeiro os predicados reais: voce busca pela forma do documento, pela existencia de uma chave ou por um unico campo escalar.

Por que usar GIN

Digamos que guardamos eventos com um payload em JSONB:

CREATE TABLE events (
    id         bigint PRIMARY KEY,
    user_id    bigint NOT NULL,
    payload    jsonb  NOT NULL,
    created_at timestamptz DEFAULT now()
);

Um B-tree nessa coluna nao ajuda: ele compara valores inteiros, mas precisamos olhar dentro do documento. O GIN (Generalized Inverted Index) guarda um indice invertido das chaves e valores dentro do JSON, entao "encontre documentos que contenham isto" passa a usar o indice.

CREATE INDEX idx_events_payload ON events USING GIN (payload);
SELECT id FROM events WHERE payload @> '{"status": "paid"}';

jsonb_ops versus jsonb_path_ops

O GIN sobre JSONB tem duas classes de operadores:

• jsonb_ops (a padrao) indexa cada chave e cada valor como uma entrada separada. Suporta mais operadores, mas o indice fica maior.
• jsonb_path_ops indexa hashes de caminhos completos "chave -> valor". E menor, normalmente mais rapido em verificacoes de contencao, mas suporta apenas um conjunto reduzido de operadores.

-- default class: jsonb_ops
CREATE INDEX idx_payload_ops ON events USING GIN (payload);

-- compact class optimized for containment
CREATE INDEX idx_payload_path ON events USING GIN (payload jsonb_path_ops);

Quais operadores cada classe suporta:

• @> (contem) e suportado por ambas as classes.
• @?, @@ (JSONPath) tambem sao suportados por ambas as classes.
• ?, ?|, ?& (existencia de chave) estao apenas em jsonb_ops.

Ou seja, a real linha divisoria e a existencia de chaves: so a classe padrao jsonb_ops resolve ?, ?| e ?& pelo indice. Se todas as suas consultas se baseiam em @> ou JSONPath, escolha jsonb_path_ops: ele cobre todas elas e e mais compacto, pois nao indexa chaves soltas pelas quais voce nunca busca.

Indice por expressao versus documento inteiro

Nem sempre voce precisa indexar o documento inteiro. Se voce filtra o tempo todo por um unico campo escalar, um B-tree sobre essa expressao sai mais barato:

CREATE INDEX idx_events_status
    ON events ((payload ->> 'status'));

SELECT id FROM events WHERE payload ->> 'status' = 'refunded';

Esse indice e minusculo e suporta intervalos e ordenacao. Juntar com suas outras tabelas relacionais e igualmente simples:

SELECT u.email, e.created_at
FROM events e
JOIN users u ON u.id = e.user_id
WHERE e.payload ->> 'status' = 'paid'
  AND u.country = 'DE';

A regra pratica: filtros de igualdade ou intervalo sobre um unico campo -> indice por expressao; filtros arbitrarios sobre varias chaves, em especial @> -> GIN sobre o documento inteiro.

Pegadinhas e como ler o plano

• ->> ignora o GIN. O predicado payload ->> 'status' = 'paid' NAO usa um indice GIN; o GIN precisa de payload @> '{"status":"paid"}'. Esse e de longe o erro mais comum.
• jsonb_path_ops nao conhece ?. A existencia de uma chave sem valor (payload ? 'coupon') so e indexada pela classe padrao.
• GIN e caro na escrita. Cada INSERT atualiza varias entradas do indice. Sob muita carga de insercao, fastupdate mais um VACUUM regular ajudam.
• Seletividade. O GIN compensa quando o predicado descarta a maioria das linhas. Se @> casa com quase tudo, o planejador vai, com bom senso, escolher um seq scan.

Sempre confira o plano:

EXPLAIN ANALYZE
SELECT id FROM events
WHERE payload @> '{"status": "paid", "channel": "web"}';

Procure por um Bitmap Index Scan no indice esperado; isso confirma que o GIN esta sendo realmente usado.

Como outros bancos diferem

• MySQL nao tem GIN. Para JSON voce usa colunas GENERATED com um B-tree comum por cima, ou indices multivalorados para arrays via MEMBER OF e JSON_CONTAINS.
• ClickHouse se apoia nos tipos JSON/Map e em indices esparsos no nivel de granulo, e nao em indices invertidos; os filtros costumam ser acelerados com colunas materializadas ou skip indexes.

No PostgreSQL a receita base e: filtros @> -> jsonb_path_ops, igualdade pontual -> indice por expressao, e confirme sempre com EXPLAIN.