Jsonb e Gin Index: Otimizando consultas no PostgreSQL

Source: Dev.to

Introdução

O PostgreSQL é um dos SGBDs mais usados na prática. Ele é robusto, confiável e oferece recursos avançados, como suporte a tipos de dados JSON e JSONB, que permitem armazenar e consultar dados semi‑estruturados de forma eficiente. Neste post vamos explorar como usar o tipo JSONB em conjunto com o Gin Index para otimizar consultas.

JSONB

• Versão binária do tipo JSON, armazenada em formato otimizado para consultas.
• Mais rápido que o tipo JSON tradicional.
• Suporta operações de indexação, permitindo criar índices para acelerar buscas em campos específicos dentro do JSONB.

Gin Index

O Generalized Inverted Index (GIN) permite indexar valores dentro de um campo JSONB. É especialmente útil para consultas que filtram por atributos específicos.

Exemplo prático

1. Criação da tabela

CREATE TABLE cards (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB
);

2. Inserção de dados

INSERT INTO cards (name, attributes)
SELECT 
    'Card ' || generate_series,
    jsonb_build_object(
        'type', 'Creature',
        'subtype', (ARRAY['Dragon', 'Goblin', 'Human', 'Elf'])[floor(random() * 4 + 1)],
        'stats', jsonb_build_object(
            'power', floor(random() * 10),
            'toughness', floor(random() * 10)
        ),
        'abilities', CASE 
            WHEN random()  '{"type": "Creature", "subtype": "Dragon"}';

Resultado resumido:

Seq Scan on cards  (cost=0.00..36891.00 rows=179403 width=163) (actual time=0.037..944.492 rows=250022 loops=1)
  Filter: (attributes @> '{"type":"Creature","subtype":"Dragon"}'::jsonb)
  Rows Removed by Filter: 749978
Planning Time: 0.303 ms
Execution Time: 1441.434 ms

A consulta realiza um scan sequencial, lendo toda a tabela, o que pode ser muito lento em tabelas grandes.

4.2 Criando o índice GIN

CREATE INDEX idx_cards_attributes ON cards USING gin (attributes);

4.3 Consulta com índice

EXPLAIN ANALYZE
SELECT * FROM cards
WHERE attributes @> '{"type": "Creature", "subtype": "Dragon"}';

Resultado resumido:

Bitmap Heap Scan on cards  (cost=2044.35..28677.89 rows=179403 width=163) (actual time=96.083..777.979 rows=250022 loops=1)
  Recheck Cond: (attributes @> '{"type":"Creature","subtype":"Dragon"}'::jsonb)
  Heap Blocks: exact=24391
  ->  Bitmap Index Scan on idx_cards_attributes  (cost=0.00..1999.50 rows=179403 width=0) (actual time=92.507..92.509 rows=250022 loops=1)
        Index Cond: (attributes @> '{"type":"Creature","subtype":"Dragon"}'::jsonb)
Planning Time: 0.388 ms
Execution Time: 1197.012 ms

Com o índice GIN a consulta passa a usar Bitmap Heap Scan, que filtra as linhas relevantes antes de acessar a tabela, reduzindo significativamente o tempo de execução.

Conclusão

O uso combinado de JSONB e Gin Index pode melhorar drasticamente o desempenho de consultas em campos JSONB, especialmente em tabelas com grande volume de dados. Ao criar um índice GIN, consultas que filtram por atributos específicos dentro do JSONB são executadas muito mais rapidamente, proporcionando melhor performance para aplicações que lidam com dados semi‑estruturados.