Jsonb e Gin Index: Otimizando consultas no PostgreSQL

O PostgreSQL é um dos sgbds que mais usei durante minha carreira. Ele é robusto, confiável e possui uma série de recursos avançados que o tornam uma escolha popular para muitos desenvolvedores. Um desses recursos é o 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 no PostgreSQL.

O que é JSONB?

O JSONB é uma versão binária do tipo de dados JSON no PostgreSQL. Ele armazena os dados em um formato otimizado para consultas, o que o torna mais rápido do que o tipo JSON tradicional. O JSONB suporta operações de indexação, o que significa que você pode criar índices para acelerar as consultas em campos específicos dentro do JSONB.

Criando um índice Gin para JSONB

O índice Gin (Generalized Inverted Index) é uma estrutura de dados que permite indexar valores dentro de um campo JSONB. Ele é especialmente útil para consultas que filtram por atributos específicos dentro do JSONB, como no exemplo abaixo:

-- 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',
        -- Subtipo com poucas variações para aumentar a amostragem
        'subtype', (ARRAY['Dragon', 'Goblin', 'Human', 'Elf'])[floor(random() * 4 + 1)],
        'stats', jsonb_build_object(
            'power', floor(random() * 10),
            'toughness', floor(random() * 10)
        ),
        -- Habilidade rara: apenas 'Phasing' será fácil de filtrar
        'abilities', CASE 
            WHEN random() < 0.001 THEN jsonb_build_array('Phasing')
            ELSE jsonb_build_array('Flying')
        END
    )
FROM generate_series(1, 1000000);

SELECT * FROM CARDS LIMIT 3;
// id |  name   | attributes
// ---+---------+----------------------------------------------------------------------------------
//  1 | Card 1  | {"type": "Creature", "stats": {"power": 8, "toughness": 1}, "subtype": "Goblin", "abilities": ["Flying"]}
//  2 | Card 2  | {"type": "Creature", "stats": {"power": 0, "toughness": 3}, "subtype": "Elf", "abilities": ["Flying"]}
//  3 | Card 3  | {"type": "Creature", "stats": {"power": 7, "toughness": 4}, "subtype": "Human", "abilities": ["Flying"]}

-- Atualiza as estatísticas para o Planejador de Consultas
ANALYZE cards;

Agora vamos analizar o desempenho das consultas sem o índice e depois com o índice Gin.

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

"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"

Veja que temos um scan sequencial na tabela, o que pode ser muito lento em tabelas grandes. Veja o Rows Removed by Filter, que indica que o PostgreSQL teve que ler todas as linhas da tabela para encontrar as correspondências.

Vamos criar o índice Gin e analisar a consulta novamente.

-- Criando o índice Gin
CREATE INDEX idx_cards_attributes ON cards USING gin (attributes);

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

"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"

Agora temos um Bitmap Heap Scan, que é muito mais eficiente do que o Seq Scan. Não temos mais o Rows Removed by Filter, pois o índice Gin já filtrou as linhas relevantes, resultando em uma execução muito mais rápida da consulta. A diferença no tempo de execução é significativa, mostrando a importância de usar índices adequados para otimizar consultas em campos JSONB.

Conclusão

O uso do tipo JSONB em conjunto com o índice Gin pode melhorar significativamente o desempenho das consultas em campos JSONB no PostgreSQL. Ao criar um índice Gin, as consultas que filtram por atributos específicos dentro do JSONB podem ser executadas muito mais rapidamente, especialmente em tabelas com um grande volume de dados. Se você estiver trabalhando com dados semi-estruturados, considere usar JSONB e índices Gin para otimizar suas consultas e melhorar a performance do seu banco de dados.