Hologres:Acelerar consultas JSONB

Como funciona

Ao ativar o armazenamento orientado a colunas em uma coluna JSONB, o Hologres converte essa coluna em várias subcolunas fortemente tipadas na camada de armazenamento, sendo uma para cada chave JSON. As consultas direcionadas a uma chave específica varrem apenas essa subcoluna, em vez de desserializar todo o valor JSONB. Isso proporciona o mesmo ganho de eficiência obtido com o armazenamento colunar em dados estruturados.

Pré-requisitos

Antes de começar, certifique-se de que:

• Sua instância do Hologres seja da versão V1.3 ou posterior. Para obter o melhor desempenho, atualize para a V1.3.37 ou superior. Para atualizar manualmente, consulte Atualizações de instância. Para solicitar uma atualização por meio da equipe de suporte, consulte Obter suporte online para o Hologres.

• A coluna de destino esteja em uma tabela orientada a colunas. O armazenamento orientado a colunas para JSONB não é compatível com tabelas orientadas a linhas.

• A tabela contenha pelo menos 1.000 linhas. O armazenamento orientado a colunas é acionado somente após atingir esse limiar.

Quando o armazenamento orientado a colunas apresenta baixo desempenho

Em alguns cenários, o armazenamento orientado a colunas não melhora o desempenho e pode até prejudicá-lo:

Somente os operadores -> e ->> acionam o acesso orientado a colunas. Consultas que utilizam outros operadores recorrem a varreduras completas do JSONB, o que pode ser mais lento do que no modo de armazenamento original.

Ativar o armazenamento orientado a colunas

-- Enable column-oriented storage for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);

VACUUM <table_name>;

-- Enable DECIMAL type inference for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = ON);

Verificar o modo de armazenamento

SELECT * FROM hologres.hg_column_options
WHERE schema_name = '<schema_name>' AND table_name = '<table_name>';

SELECT DISTINCT
    a.attnum as num,
    a.attname as name,
    format_type(a.atttypid, a.atttypmod) as type,
    a.attnotnull as notnull,
    com.description as comment,
    coalesce(i.indisprimary,false) as primary_key,
    def.adsrc as default,
    a.attoptions
FROM pg_attribute a
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON
    (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com ON
    (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON
    (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
    AND pg_table_is_visible(pgc.oid)
    AND NOT a.attisdropped
    AND pgc.relname = '<table_name>'
ORDER BY a.attnum;

Desativar o armazenamento orientado a colunas

-- Disable column-oriented storage for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_columnar_type = OFF);

-- Disable DECIMAL type inference for a JSONB column.
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET (enable_decimal = OFF);

Adicionar índices bitmap para consultas pontuais (V2.0+)

Depois que o armazenamento orientado a colunas converte as chaves JSONB em subcolunas tipadas, é possível adicionar índices bitmap nessas subcolunas para acelerar consultas pontuais (comparações de igualdade). Os índices bitmap são mais eficazes quando a chave aparece frequentemente nas linhas e a consulta busca um valor exato.

Com o armazenamento orientado a colunas ativado, o Hologres analisa os seguintes tipos: INT, INT[], BIGINT, BIGINT[], TEXT, TEXT[] e JSONB. Índices bitmap são criados para subcolunas dos tipos INT, INT[], BIGINT, BIGINT[], TEXT e TEXT[].

call set_table_property('<table_name>', 'bitmap_columns', '[<columnName>{:[on|off]}[,...]]');

Exemplo

O exemplo a seguir demonstra como ativar o armazenamento orientado a colunas, importar dados, realizar consultas e verificar o plano de execução.

1. Crie a tabela.

   DROP TABLE IF EXISTS user_tags;
   
   BEGIN;
   CREATE TABLE IF NOT EXISTS user_tags (
       ds   timestamptz,
       tags jsonb
   );
   COMMIT;

2. Ative o armazenamento orientado a colunas na coluna tags.

   ALTER TABLE user_tags ALTER COLUMN tags SET (enable_columnar_type = ON);

3. Verifique se o armazenamento orientado a colunas está ativado.

   SELECT * FROM hologres.hg_column_options WHERE table_name = 'user_tags';

   Saída esperada — o campo options para tags exibe {enable_columnar_type=on}:

    schema_name | table_name | column_id | column_name |       column_type        | notnull | comment | default |          options
   -------------+------------+-----------+-------------+--------------------------+---------+---------+---------+---------------------------
    public      | user_tags  |         1 | ds          | timestamp with time zone | f       |         |         |
    public      | user_tags  |         2 | tags        | jsonb                    | f       |         |         | {enable_columnar_type=on}
   (2 rows)

4. Importe os dados.

   INSERT INTO user_tags (ds, tags)
   SELECT
       '2022-01-01 00:00:00+08',
       ('{"id":' || i || ',"first_name":"Sig","gender":"Male"}')::jsonb
   FROM generate_series(1, 10001) i;

5. (Opcional) Descarregue os dados no disco para visualizar o efeito do armazenamento orientado a colunas imediatamente.

   VACUUM user_tags;

6. Consulte os dados por uma chave específica.

   SELECT (tags -> 'first_name')::text AS first_name
   FROM user_tags
   WHERE (tags -> 'id')::int = 10;

7. Confirme se o armazenamento orientado a colunas está ativo verificando o plano de execução.

   -- Enable detailed execution statistics.
   SET hg_experimental_show_execution_statistics_in_explain = ON;
   
   EXPLAIN ANALYZE
   SELECT (tags -> 'first_name')::text AS first_name
   FROM user_tags
   WHERE (tags -> 'id')::int = 10;

   A presença de columnar_access_used na saída confirma o uso do armazenamento orientado a colunas.

   

8. Adicione um índice bitmap em tags para acelerar a consulta pontual da etapa 6.

   call set_table_property('user_tags', 'bitmap_columns', 'tags');

9. Verifique se o índice bitmap está sendo utilizado.

   EXPLAIN ANALYZE
   SELECT (tags -> 'first_name')::text AS first_name
   FROM user_tags
   WHERE (tags -> 'id')::int = 10;

   O termo bitmap_used na saída confirma que o índice bitmap está ativo.

   

Práticas recomendadas

-- Faster: uses ->> directly
SELECT json_data->>'name' FROM tbl;

-- Slower: converts JSONB field to text via cast
SELECT (json_data->'name')::text FROM tbl;

SELECT key FROM tbl WHERE jsonb_to_textarray(json_data->'phones') && ARRAY['123456'];

CREATE TABLE tbl (key int, json_data json);
ALTER TABLE tbl ALTER COLUMN json_data SET (enable_columnar_type = on);

EXPLAIN ANALYZE SELECT json_data FROM tbl WHERE key = 123;

Column 'json_data' has enabled columnar jsonb, but the query scanned the entire Jsonb value

VACUUM <table_name>;

Perguntas frequentes